SUM array elements

B

Bill

Is there a function like SUM for use with arrays?

Like: "ASUM"
Dim MyArray(8) as long
Dim Total as Long
Total = ASUM(MyArray)

Which would be the equivalent of an 8 iteration
loop through MyArray adding up the values
found in each element of MyArray.

Bill
 
J

John Nurick

Hi Bill,

There's no built-in function for summing array elements. You need to
iterate through the elements. Here are the beginnings of a
general-purpose function, though I suspect it would be simpler just to
write the few lines of code needed each time than to complete and debug
this:

Public Function ASum(A As Variant) As Variant

Dim lngBuf As Long
Dim dblBuf As Double
Dim LongOverFlow As Boolean
Dim DataType As Long
Dim j As Long

If VarType(A) And vbArray <> vbArray Then
'A is not an array
ASum = Null
Exit Function
End If

'Get data type for elements
'Needs refining to handle Decimal, Currency, etc.
'and variant arrays containing a mix of data types
DataType = VarType(A) And Not vbArray

'Iterate through elements
For j = LBound(A) To UBound(A)
dblBuf = dblBuf + CDbl(A(j))
On Error Resume Next
lngBuf = lngBuf + CLng(A(j))
If Err.Number = 6 Then
LongOverFlow = True
Err.Raise Err.Number, Err.Source, Err.Description, _
Err.HelpFile, Err.HelpContext
End If
On Error GoTo 0
Next

'Return sum from appropriate buffer
Select Case DataType
Case vbInteger, vbLong, vbByte
If LongOverFlow Then
ASum = dblBuf
Else
ASum = lngBuf
End If
Case Else
ASum = dblBuf
End Select

End Function
 
B

Bill

Hi John,
Three lines of code in a "For" loop loading an accumulator
was the cleanest solution here. I just thought there might be
a VBA built-in function that didn't surface when I did a
search.
Bill
 
Top