API reference · Finance

Sum

Adds numeric values from a vector or the first column of a 2D array.

FunctionFinance→DoubleSource L16

Signature#

Function Sum(ByVal vArr As Variant) As Double

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Input 1D array or 2D array. For 2D input, only its first column by lower bound is included.

Returns#

Double. The sum of accepted numeric values, or 0 when no values qualify or the input shape is rejected.

Example#

SEE THE TRANSFORMATIONSum the numeric values

Before

values1D · (8,)

Indices 0..7 · one dimension

0	1	2	3	4	5	6	7
10	“20”	-5	Empty	Null	#N/A	“n/a”	True

After

Return valueValues / state

No array dimensions

Item	Value
Sum	25

Numeric text “20” contributes 20. Empty, Null, error, nonnumeric text and Boolean values are skipped.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Option Explicit

Public Sub ExampleFinanceSum()
    Dim f As New Finance
    Dim values As Variant
    values = VBA.Array(10, "20", -5, Empty, Null, CVErr(xlErrNA), "n/a", True)
    Debug.Print f.Sum(values) '25
End Sub

Behavior and edge cases#

Accepts an allocated 1D array or a 2D array. A 1D array is expanded through the global Arr instance declared in a standard module. For 2D input, only the column at LBound(vArr, 2) is read; additional columns are ignored.
Loops over the actual first-dimension lower and upper bounds. A horizontal 2D vector therefore contributes only its first cell; reduce it to 1D first if every cell should be included.
The input is passed ByVal and is not deliberately modified. Non-array input or a detected rank other than 1 or 2 returns the default numeric value 0.
Error values, Null and Empty are skipped before IsNumeric is evaluated. Numeric text is accepted and converted with CDbl using VBA locale rules; text that is not numeric is skipped. VBA Boolean and Date values are not numeric for IsNumeric.
There is no overflow recovery. Values that pass IsNumeric but cannot be converted or accumulated within Double limits can raise a runtime error.
Processes accepted values in original row order with ordinary Double addition; it does not use compensated summation.

Performance#

O(n) time for n rows in the first column, with O(1) accumulator state. The 1D-to-2D normalization allocates O(n) elements; passing a Variant array ByVal can also require an array copy.

View implementation

Function Sum(ByVal vArr As Variant) As Double
    'Returns the sum of numeric values in a column vector

    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim vValue                  As Variant
    Dim dblSum                  As Double

    If Not IsArray(vArr) Then Exit Function
    If Arr.DimCounter(vArr) = 1 Then vArr = Arr.DimExpand(vArr)
    If Arr.DimCounter(vArr) <> 2 Then Exit Function

    lCol = LBound(vArr, 2)

    For lRow = LBound(vArr, 1) To UBound(vArr, 1)
        vValue = vArr(lRow, lCol)
        If Not IsError(vValue) Then
            If Not IsNull(vValue) Then
                If Not IsEmpty(vValue) Then
                    If IsNumeric(vValue) Then dblSum = dblSum + CDbl(vValue)
                End If
            End If
        End If
    Next lRow

    Sum = dblSum
End Function