# WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

1. ## WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

I used this loop: ``Please Login or Register  to view this content.``
to create an array named 'Y' of dates minus the minimum of the dates i.e. the earliest date. I then tried to take the sum of the dates using WorksheetFunction.Sum(Y) and it returned 0.

Because I was confused, I used this loop to examine the contents of Y: ``Please Login or Register  to view this content.``
This showed me that the sum of the elements of 'Y' was greater than 0, as it contained numbers that were greater than 0 and no numbers less than 0.

I then used this loop to create an array of these values: ``Please Login or Register  to view this content.``
and then executed WorksheetFunction.Sum(TestArray) and got the sum I expected, which was around 50,000. Has anyone got any idea as to why this might occur?

Rowan  Register To Reply

2. ## Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

Hi,
How did you declare Y?  Register To Reply

3. ## Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

I declared Y in a separate module with:

Public Y() As Date and

then in the module of the procedure, I used ReDim Y(Count)  Register To Reply

4. ## Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

That's the issue then. It would need to be Double for Sum to work on it.  Register To Reply

5. ## Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

Thanks a lot OnErrorGoTo0. That's excellent. What range of values can a Double take?  Register To Reply

6. ## Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

-1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308
(lifted straight out of the Help file, so any inaccuracy is Microsoft's)  Register To Reply

7. ## Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

Thanks a lot.  Register To Reply