Skip to content

Showcase

This demonstration module summarizes numeric values in a one-dimensional array, such as values collected by a spreadsheet macro. It is a self-contained sample, rather than code from a production project.

Pass real numeric values: blanks, strings (including numeric strings), Boolean values, and errors are ignored.

Dim values As Variant
values = Array(12, Empty, "pending", 8)
Debug.Print SumNumbers(values)    ' 20
Debug.Print CountNumbers(values)  ' 2

Rendered documentation

The documentation below is generated directly from the module's comments and signatures. Follow the procedure links or expand a source block to inspect the result.

Summarize numeric values in a one-dimensional array. Use SumNumbers for a total and CountNumbers for the number of numeric entries.

Public Function SumNumbers(ByVal values As Variant) As Double

Add the numeric entries in an array.

Parameters:

  • values ( Variant ) –

    A one-dimensional array, such as Array(12, Empty, "pending", 8). Strings, Boolean values, blanks, and errors are ignored.

Returns:

  • Double –

    The sum of the numeric entries, or zero if none are present.

Examples:

Debug.Print SumNumbers(Array(12, Empty, "pending", 8))  ' 20

See also CountNumbers for the number of numeric entries.

Source code 
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Public Function SumNumbers(ByVal values As Variant) As Double
    ' Add the numeric entries in an array.
    '
    ' Args:
    '     values: A one-dimensional array, such as Array(12, Empty, "pending", 8).
    '         Strings, Boolean values, blanks, and errors are ignored.
    '
    ' Returns:
    '     The sum of the numeric entries, or zero if none are present.
    '
    ' Examples:
    '     ```vb
    '     Debug.Print SumNumbers(Array(12, Empty, "pending", 8))  ' 20
    '     ```
    '
    ' See also [CountNumbers][] for the number of numeric entries.
    Dim item As Variant
    For Each item In values
        Select Case VarType(item)
            Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
                SumNumbers = SumNumbers + CDbl(item)
#If Win64 Then
            Case vbLongLong
                SumNumbers = SumNumbers + CDbl(item)
#End If
        End Select
    Next item
End Function

Public Function CountNumbers(ByVal values As Variant) As Long

Count the numeric entries in an array.

Parameters:

  • values ( Variant ) –

    A one-dimensional array. Strings, Boolean values, blanks, and errors are ignored.

Returns:

  • Long –

    The number of numeric entries, or zero if none are present.

Examples:

Debug.Print CountNumbers(Array(12, Empty, "pending", 8))  ' 2

See also SumNumbers for the total of the numeric entries.

Source code 
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
Public Function CountNumbers(ByVal values As Variant) As Long
    ' Count the numeric entries in an array.
    '
    ' Args:
    '     values: A one-dimensional array. Strings, Boolean values, blanks, and errors are ignored.
    '
    ' Returns:
    '     The number of numeric entries, or zero if none are present.
    '
    ' Examples:
    '     ```vb
    '     Debug.Print CountNumbers(Array(12, Empty, "pending", 8))  ' 2
    '     ```
    '
    ' See also [SumNumbers][] for the total of the numeric entries.
    Dim item As Variant
    For Each item In values
        Select Case VarType(item)
            Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
                CountNumbers = CountNumbers + 1
#If Win64 Then
            Case vbLongLong
                CountNumbers = CountNumbers + 1
#End If
        End Select
    Next item
End Function

Source

Import this module into your VBA project, or copy the procedures from the listing. Download ArraySummary.bas.

Attribute VB_Name = "ArraySummary"
' Summarize numeric values in a one-dimensional array.
' Use [SumNumbers][] for a total and [CountNumbers][] for the number of numeric entries.

Option Explicit

Public Function SumNumbers(ByVal values As Variant) As Double
    ' Add the numeric entries in an array.
    '
    ' Args:
    '     values: A one-dimensional array, such as Array(12, Empty, "pending", 8).
    '         Strings, Boolean values, blanks, and errors are ignored.
    '
    ' Returns:
    '     The sum of the numeric entries, or zero if none are present.
    '
    ' Examples:
    '     ```vb
    '     Debug.Print SumNumbers(Array(12, Empty, "pending", 8))  ' 20
    '     ```
    '
    ' See also [CountNumbers][] for the number of numeric entries.
    Dim item As Variant
    For Each item In values
        Select Case VarType(item)
            Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
                SumNumbers = SumNumbers + CDbl(item)
#If Win64 Then
            Case vbLongLong
                SumNumbers = SumNumbers + CDbl(item)
#End If
        End Select
    Next item
End Function

Public Function CountNumbers(ByVal values As Variant) As Long
    ' Count the numeric entries in an array.
    '
    ' Args:
    '     values: A one-dimensional array. Strings, Boolean values, blanks, and errors are ignored.
    '
    ' Returns:
    '     The number of numeric entries, or zero if none are present.
    '
    ' Examples:
    '     ```vb
    '     Debug.Print CountNumbers(Array(12, Empty, "pending", 8))  ' 2
    '     ```
    '
    ' See also [SumNumbers][] for the total of the numeric entries.
    Dim item As Variant
    For Each item In values
        Select Case VarType(item)
            Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
                CountNumbers = CountNumbers + 1
#If Win64 Then
            Case vbLongLong
                CountNumbers = CountNumbers + 1
#End If
        End Select
    Next item
End Function