Using Parameter arrays in VBA

A parameter array can be used to pass an array of arguments to a procedure. You don't have to know the number of elements in the array when you define the procedure.
 
Copy the data in the box below into a new VBA module.
Run the procedure "PassingAParamArray".
The output goes to the Immediate window. If the Immediate window is not open, either press "Ctrl+G", or, "View > Immediate Window" from the menu.
 
Option Explicit
Option Base 1

'' ***************************************************************************
'' Purpose  : Create some test data
'' Written  : 01-Jul-2002 by Andy Wiggins, Byg Software Limited
''
Sub PassingAParamArray()
Dim x As Integer, y As String, z As String
Dim aa(3)
Dim bb(2)
Dim cc(4)

    '' Assign values to integer and string types
    x = 1
    y = "Two"
    z = "3"

    '' Assign values to an array
    aa(1) = "aa array"
    aa(2) = "Two"
    aa(3) = "Three"

    '' Assign values to another array
    bb(1) = "bb array"
    bb(2) = "Five"

    '' Assign array "bb" to the second element of "cc" to create a nested array
    cc(1) = "Nested array"
    cc(2) = bb

    '' Call this Sub to output test data to the immediate window
    PassToHere x, y, z, aa, bb, cc

End Sub

'' ***************************************************************************
'' Purpose  : Output test data to the immediate window (Ctrl+G)
'' Written  : 01-Jul-2002 by Andy Wiggins, Byg Software Limited
''
Sub PassToHere(x As Integer, y As String, z As String, ParamArray pa() As Variant)

    '' Regular parameters
    Debug.Print x
    Debug.Print y
    Debug.Print z

    '' The array is passed as the final elements in the Sub parameter.
    '' This Sub has four arguments, but in "PassingAParamArray" it shows six.
    '' The fourth, fifth and sixth are treated as one by this Sub.
    '' To address it we need:
    ''      First:  Address the array element
    ''      Second: Address the array element within the first element
    Debug.Print pa(0)(1)
    Debug.Print pa(0)(2)
    Debug.Print pa(0)(3)

    Debug.Print pa(1)(1)
    Debug.Print pa(1)(2)

    Debug.Print pa(2)(1)
    Debug.Print pa(2)(2)(1) '' Address an element within a nested array

    '' From the Excel Help file
    '' Used only as the last argument in arglist to indicate that the
    '' final argument is an Optional array of Variant elements.
    '' The ParamArray keyword allows you to provide an arbitrary number
    '' of arguments.
    '' The ParamArray keyword can't be used with ByVal, ByRef, or Optional.
End Sub

Published: 2001
Last modified: 01-Mar-2011