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
