# 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

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

Hi,
How did you declare Y?

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)

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.

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?

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)

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

Thanks a lot.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1