Using an Indefinite Number of Arguments

Generally, the number of arguments in the procedure call must be the same as in the procedure specification. Using the ParamArray keyword with an argument allows you to specify that a procedure will accept an arbitrary number of arguments. The argument used with ParamArray must be an array of Variant variables. For example, you could write the following AddInts procedure, using the ParamArray keyword with the array argument intNums() to allow the procedure to handle any number of arguments passed to it.

Function AddInts(ParamArray intNums())
    Dim x As Integer
    Dim y As Variant

    For Each y In intNums
        x = x + y
    Next y
    AddInts = x
End Function

Note that a For Each control variable in an array — y in the preceding example — must be a Variant variable.

You can subsequently call AddInts with any number of arguments, as shown in the following example.

Sub ShowSum()
    MsgBox AddInts(1, 3, 5)
    MsgBox AddInts(1, 3, 5, 7, 8, 9, 34, 98, 123)
End Sub

You can use the ParamArray keyword only with the last argument in an argument list.