Different results for simple operations made with ranges or arrays

Question

I'm working with VBA for a very long time and I have quite experience with it. However, sometimes, it still blow my mind with his whimsicalities... In order to make a file with some accessible functionalities to all of my work colleagues, I chose to made every calculations on a script. To do so, I populate an array with some double data:

Dim DT01() As Double: ReDim DT01(1 To N03) As Double
Dim R As Long: R = 1: Dim N As Long: Dim P As Double
For x = 1 To N01
    N = SH01.Cells(x + 4, 15).Value: P = SH01.Cells(x + 4, 14).Value
    For y = 1 To N: DT01(R) = P: R = R + 1: Next y
Next x

The DT01 array is quite long (N03 = 495258) and the previous routine populates the array just as I intended. I even made the following:

For x = 1 To 495258
    SH01.cells(x, 1).FormulaR1C1 = DT01(x)
Next x

to check if I made a mistake on the script to fill the array. This piece of code writes alright the first column of the sheet SH01 with the right values of the array.

If I calculate the sum of the column, I got 292547224.4, which is the right value. However, if I use Application.Sum(DT01) on VBA, I got 1535172.8. When I saw this, I tried to calculate other things and the results are always different:

'On Excel:
    =AVERAGE(A1:A495258) = 598,6
'On VBA:
    Application.Average(DT01) = 42.1

'On Excel:
    =MAX(A1:A495258) = 3622.7
'On VBA:
    Application.Max(DT01) = 186.8    

'On Excel:
    =COUNT(A1:A495258) = 495258
'On VBA:
    Application.Count(DT01) = 36506

When I saw the last result, I knew immediately I had to ask someone about this... Does anybody knows what's happening here??

UPDATE:

I tried to calculate the sum of the array with a loop through all it's terms:

Dim SIGMA As Double: SIGMA = 0
For x = 1 To UBound(DT01)
    SIGMA = SIGMA + DT01(x)
Next x

and I got the right result (292547224.4), so why I still got 1535172.8 with Application.Sum(DT01)?

Answer 1

This isn't an ideal answer so someone may flesh out with decent documentation. I find that SUM on an array in VBA only works to length 36506 (starting at 1).

You can however, write the array of values to a range and then pass that range reference to Application.Sum and get the correct values.

Option Explicit
Public Sub test()
    Dim rng As Range, c As Range, i As Long
    Set rng = Range("A1:A495258") 'A1 has value 1, A2 has 2 etc.

    Debug.Print "Application.Sum(rng) " & Application.Sum(rng)   '<==  122640490911
    Debug.Print " Application.Sum(Application.Transpose(rng.Value)) " & Application.Sum(Application.Transpose(rng.Value))   '<== 666362271

    Dim arr3()
    ReDim arr3(1 To 495258)
    For Each c In rng
        i = i + 1
        arr3(i) = c.Value
    Next

    Debug.Print " Application.Sum(arr3) with  arr3(1 To 495258) " & Application.Sum(arr3)
    Debug.Print "Application.WorksheetFunction.Sum(arr3) " & Application.WorksheetFunction.Sum(arr3)
End Sub

Or write your own custom 1d sum function:

Option Explicit

Public Sub test()
    Dim arr3(), c As Range, rng As Range, i As Long
    Set rng = Range("A1:A495258")
    ReDim arr3(1 To 495258)
    For Each c In rng
        i = i + 1
        arr3(i) = c.Value
    Next
    Debug.Print SumArray(arr3)
End Sub

Public Function SumArray(ByVal arr As Variant) As Variant ' double
    Dim i As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        SumArray = SumArray + arr(i)
    Next
End Function