Unlimited Function Arguments – ParamArray in VBA

Recently I stumbled upon an interesting functionality provided by VBA, termed by ‘ParamArray’ keyword.

This key word is used in to send unknown number of arguments in the form of variant array to the function/sub-procedure in VBA.

This can be illustrated via following code:

Option Explicit

'The following VBA illustrates the use of ParamArray functionality illustrated by a
'functiuon returning the count of arguments passed to the function.

Function param_array_function(ParamArray Args() As Variant) As Integer

    Dim iCount As Integer
    iCount = UBound(Args)
    param_array_function = IIf(iCount < 0, 0, iCount + 1)

End Function


Sub Test_Proc()

Debug.Print "Args :" & param_array_function(1, 2, 3, 4, 5)
Debug.Print "Args :" & param_array_function("amol", "pandey", "excel", "vba")
Debug.Print "Args :" & param_array_function(1, 2, 3)
Debug.Print "Args :" & param_array_function(1, 2)

End Sub

This functionality can be very handy to design function/sub-procedures which are generic enough to take in multiple arguments previously unknown and provides an alternative to send in arguments previously being packed in array/collection for the same purposes.

Refrences:
Link: http://www.cpearson.com/excel/optionalargumentstoprocedures.aspx