API reference · Finance

Count

Counts non-empty first-column values, optionally restricting the count to numeric values.

FunctionFinance→LongSource L76

Signature#

Function Count(ByVal vArr As Variant, Optional ByVal bNumeric As Boolean = False) As Long

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Input 1D array or 2D array. Only the first column by lower bound is examined for 2D input.
`bNumeric`	`Boolean`ByVal	`False`	False counts values whose CStr representation is non-empty. True additionally requires IsNumeric(value).

Returns#

Long. Number of accepted values. Returns 0 for rejected shapes or when no values qualify.

Example#

SEE THE TRANSFORMATIONCompare the two counting modes

Before

values1D · (8,)

Indices 0..7 · one dimension

0	1	2	3	4	5	6	7
0	“0”	“”	Empty	Null	#N/A	“text”	True

After

Return valuesValues / state

No array dimensions

Item	Value
Count(values)	4
Count(values, True)	2

Default mode counts 0, “0”, “text” and True. Numeric-only mode counts 0 and “0”.

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 ExampleFinanceCount()
    Dim f As New Finance
    Dim values As Variant
    values = VBA.Array(0, "0", "", Empty, Null, CVErr(xlErrNA), "text", True)
    Debug.Print f.Count(values)       '4: 0, "0", "text", True
    Debug.Print f.Count(values, True) '2: 0, "0"
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.
Skips error values, Null and Empty. It then requires Len(CStr(value)) > 0, so a zero-length string is excluded but whitespace text is counted.
With bNumeric=False, numeric values, numeric text, other non-empty text, Boolean values and Date values can count. With bNumeric=True, IsNumeric additionally excludes Boolean and Date values and nonnumeric text.
No numeric conversion with CDbl is performed. This is a count of qualifying values, not a count of rows in the whole 2D table.
CStr is applied before the optional IsNumeric check. Unsupported values such as nested arrays can raise a runtime error; arbitrary nested structures are not supported.

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 Count(ByVal vArr As Variant, Optional ByVal bNumeric As Boolean = False) As Long
    'Counts non-empty values, or only numeric values when bNumeric is True

    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim vValue                  As Variant
    Dim n                       As Long

    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 Len(CStr(vValue)) > 0 Then
                        If bNumeric Then
                            If IsNumeric(vValue) Then n = n + 1
                        Else
                            n = n + 1
                        End If
                    End If
                End If
            End If
        End If
    Next lRow

    Count = n
End Function