Extremum

Signature#

Function Extremum(ByVal vArr As Variant, ByVal eExtremum As ExtremumType) As Double

Parameters#

Returns#

Double. The selected numeric extremum. Returns 0 when no numeric value qualifies, the input shape is rejected, or eExtremum is neither eMin nor eMax.

Example#

Option Explicit

Public Sub ExampleFinanceExtremum()
    Dim f As New Finance
    Dim values As Variant
    values = VBA.Array(12, "-3", Empty, Null, CVErr(xlErrNA), 7)
    Debug.Print f.Extremum(values, 0) '-3; Finance.ExtremumType.eMin = 0
    Debug.Print f.Extremum(values, 1) '12; Finance.ExtremumType.eMax = 1
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.
• Seeds the accumulator with the first accepted numeric value, so all-negative inputs produce the correct maximum and all-positive inputs produce the correct minimum.
• Finance.ExtremumType uses eMin=0 and eMax=1. Arrays.GroupType has different values for its members named eMin and eMax; do not interchange these values.
• The returned 0 does not distinguish a true zero extremum from invalid selection or absence of observations.

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 Extremum(ByVal vArr As Variant, ByVal eExtremum As ExtremumType) As Double
    'Returns the minimum or maximum numeric value in a column vector
    'eMax = Max
    'eMin = Min
    
    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim vValue                  As Variant
    Dim dblExtremum             As Double
    Dim dblValue                As Double
    Dim bFound                  As Boolean

    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
    If eExtremum <> eMin And eExtremum <> eMax 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)
                        If Not bFound Then
                            dblExtremum = dblValue
                            bFound = True
                        ElseIf eExtremum = eMax Then
                            If dblValue > dblExtremum Then dblExtremum = dblValue
                        Else
                            If dblValue < dblExtremum Then dblExtremum = dblValue
                        End If
                    End If
                End If
            End If
        End If
    Next lRow

    If bFound Then Extremum = dblExtremum
End Function