SquaredSum

Signature#

Function SquaredSum(ByVal vArr As Variant) As Double

Parameters#

Returns#

Double. The sum of x*x for accepted numeric values, or 0 when none qualify or the input shape is rejected.

Example#

Option Explicit

Public Sub ExampleFinanceSquaredSum()
    Dim f As New Finance
    Dim values As Variant
    values = VBA.Array(3, 4, "5", Empty, Null, CVErr(xlErrNA))
    Debug.Print f.SquaredSum(values) '50 = 9 + 16 + 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.
• Each accepted value is converted to Double once, squared and added to a Double accumulator. Squaring can overflow even when the original value fits in a Double.

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.

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

    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim vValue                  As Variant
    Dim dblValue                As Double
    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
                        dblValue = CDbl(vValue)
                        dblSum = dblSum + dblValue * dblValue
                    End If
                End If
            End If
        End If
    Next lRow

    SquaredSum = dblSum
End Function