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