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.