In some situations, you may not know ahead of time exactly how many arguments will be passed to a procedure (sub or function). If this is the case, then you might consider using the ParamArray keyword to define a procedure that takes a variable number of arguments.
To do this, only one argument in a procedure definition can be marked with ParamArray—and it must be the last argument in the list (or the only argument). For example, here's a function that returns the sum of any number of values passed to it:
Private Function Sum(ParamArrayargs() As Variant) As Single
Dim total As Single
Dim i As Integer
For i = 0 To UBound(args)
total = total + args(i)
Sum = total
You could call this function like this:
answer = Sum(1,2,3,4,5)
and the returned value will be 15. You can call it like this too:
answer = Sum(11, 14, 55, 78, 102, 45, 99, 101, 55, 21)
Since the arguments in the ParamArray are type Variant, you can mix types as well, passing a combination of numbers, strings, dates, and so on. (Of course, the code in the procedure needs to know how to sort out these various types.)
Advance your scripting skills to the next level with TechRepublic's free Visual Basic newsletter, delivered each Friday. Automatically sign up today!