The ParamArray keyword (short for Parameter Array) allows you to accept a variable number of arguments into a procedure. The ParamArray must be the last argument in the list, and it can't be used in the same argument list as an Optional argument.
The ParamArray is an optional variant array. That is, the array can be empty, or it can contain any number of variant elements. To see how this operates, here's a quick example:
Private Sub cmdCallDoStuff_Click()
Dim blnOK As Boolean blnOK = DoStuff("Wednesday", 1234, _ CDate("04/12/1999"), 123.444)
Private Sub cmdCallDoOtherStuff_Click()
Dim blnOK As Boolean
Dim oTest As testEXE.txtClass
Set oTest = New testEXE.txtClass blnOK = DoStuff(123, 9999999.99, "Hello World", _ oTest)
Set oTest = Nothing End Sub
52 Chapter 3 - VBA Variables and Data Types
Private Function DoStuff(ParamArray anyArgs()) As Boolean
Dim i As Integer
For i = 0 To UBound(anyArgs)
MsgBox anyArgs(i) & vbCrLf & TypeName(anyArgs(i)) Next i
As you can see from this simple example, a ParamArray gives you incredible flexibility, allowing the calling program to pass any data type—including objects—in any order to a procedure or function. However, this flexibility is often a drawback. Imagine yourself as the programmer of the calling procedure. What arguments is the function looking for? What data types are they supposed to be? I would recommend that you not get too excited at the flexibility offered by Param-Array, and that you think instead about the wider benefits of type-safe functions that are both easy to use and easy to maintain.
One last word of caution about ParamArrays. I have found in the past (and particularly with early releases of Windows NT 4.0) that ParamArrays within remote server applications often displayed unstable and inexplicable behavior.
Was this article helpful?