ParamArray

The ParamArray keyword allows you to accept a dynamic number of arguments.
The word ParamArray is an abbreviation for parameter array.
Instead of using a large number of optional parameters it might be better to use a parameter array instead.
This can only be used as the last argument in a procedure or function.
This keyword cannot be used with ByRef, ByVal or Optional (it is always ByRef).
The parameter array must be defined with a Variant data type.
The parameter array is always zero based and is not effected by the Option Base statement.
The parameter array can contain different data types, including objects.


Accessing with a For Each - Next Loop

This subroutine requires one integer argument.
Followed by any number of other arguments.
These arbitrary arguments can be of any data type.
This subroutine uses a For-Each-Next loop to through the parameter array.

Public Sub Procedure_One(ByVal iConstant As Integer, _ 
                         ParamArray aArgumentsArray() As Variant)
Dim vArg As Variant
   For Each vArg In aArgumentsArray
      Debug.Print vArg
   Next vArg
End Sub

Public Sub RunThis_One()
   Call Procedure_One(100, "one")
   Call Procedure_One(100, "one", "two")
   Call Procedure_One(100, 1, 2, 3, 4, 5)
End Sub

Accessing with a For - Next Loop

This subroutine uses a For-Next loop to loop through the parameter array.

Public Sub Procedure_Two(ByVal iConstant As Integer, _ 
                         ParamArray aArgumentsArray() As Variant)
Dim vArg As Variant
Dim icount As Integer

   For icount = 0 To UBound(aArgumentsArray)
      vArg = aArgumentsArray(icount)
      Debug.Print vArg
   Next icount
End Sub

Public Sub RunThis_Two()
   Call Procedure_Two(100, 1, 2, 3, 4, 5)
End Sub

Empty Parameter Array

This subroutine can detect if the parameter array is empty.
Instead of leaving the argument completely blank you could pass in the keyword Nothing.

Public Sub Procedure_Three(ByVal iConstant As Integer, _ 
                           ParamArray aArgumentsArray() As Variant)
Dim vArg As Variant

   If ((LBound(aArgumentsArray) = 0) And _
       (UBound(aArgumentsArray) = -1)) Then
       Debug.Print "Arguments array was not provided"
       Exit Sub
   End If
   
   If (aArgumentsArray(0) Is Nothing) Then
       Debug.Print "'Nothing' was passed in"
       Exit Sub
   End If

   For Each vArg In aArgumentsArray
      Debug.Print vArg
   Next vArg
End Sub

Public Sub RunThis_Three()
   Call Procedure_Three(100)
   Call Procedure_Three(100, Nothing)
End Sub

Passing In One Dimensional Array

These two subroutines will extract the arguments when they are passed in using a one dimensional array.
This subroutine extracts the one dimensional array from the first argument and then uses a for-loop.

Public Sub Procedure_Four(ByVal iConstant As Integer, _ 
                          ParamArray aArgumentsArray() As Variant)
Dim vArguments As Variant
Dim vArg As Variant

   vArguments = aArgumentsArray(0)
   For Each vArg In vArguments
      Debug.Print vArg
   Next vArg
End Sub

Public Sub RunThis_Four()
Dim myarray As Variant
    ReDim myarray(1) As String 'zero based array, do not use Option Base 1
    myarray(0) = "one"
    myarray(1) = "two"
    Call Procedure_Four(100, myarray)
End Sub

It is possible to access the arguments directly using an 'array of array' type syntax.

Public Sub Procedure_Five(ByVal iConstant As Integer, _ 
                          ParamArray aArgumentsArray() As Variant)
Dim icount As Integer
Dim vArg As Variant

   For icount = 0 To UBound(aArgumentsArray(0))
      vArg = aArgumentsArray(0)(icount)
      Debug.Print vArg
   Next icount
End Sub

Public Sub RunThis_Five()
    Call Procedure_Five(100, Array("one","two"))
End Sub

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext