ThreeWave Optional Parameters To Procedures

This page describes two methods of implementing optional and a variable number of parameters to a VBA procedure.


The majority of procedures (Sub and Function procedures) use a fixed number of parameters and most often these parameters have explicit data types. This is all well and good, but there may be circumstances in which the number of parameters cannot be known until run-time. There are two methods that you can use to handle a variable number of parameters. The first, Optional Variants, defines a fixed number of parameters but makes these parameters optional, effectively putting an upper limit on the number of paramters than may be passed. The second method is to use a ParamArray type parameter. This allows for any number of parameters, including none. In either method, if your procedure requires some parameters but not others, declare all the required parameters first and then declare the optional parameters with either the Optional attribute or with a ParamArray type parameter after declaring the required parameters.


Optional Variants

You can declare one or more parameters as optional parameters of the Variant data type. With these optional Variant parameters, you can use the IsMissing function to determine whether a parameter was included or omitted. There are a few rules that govern the use optional parameters:

  • The Optional keyword must be present to make a parameter optional.
  • The data type should be (but need not be, see below) a Variant data type.
  • The optional parameter(s) must be at the end of the parameter list.
  • The IsMissing function will work only with parameters declared as Variant. It will return False when used with any other data type.
  • User defined types (UTDs) cannot be optional parameters.
We'll examine each of the rules. First, the keyword Optional must be used in the parameter declaration. This keyword is what makes the parameter optional. Without it, the parameter is required. For example,

    Function Test(L1 As Long, L2 As Long, 
        Optional P1 As Variant, Optional P2 As Variant) As String

In this example, L1 and L2 are required parameters and P1 and P2 are optional parameters. Since P1 and P2 are Variant types, we can use the IsMissing to determine whether the parameters were passed to the procedure.

The data type should be a Variant type varible. Actually, this is only partially true. If you need to test specifically whether a parameter was actually passed to the procedure, you must declare it as a Variant so you can use the IsMissing function to determine if the parameter was passed. IsMissing may be used only with Variant types. It will return False for any other data type, even it that parameter is declared as Optional and is in fact missing. An optional parameter that is not a Variant will be assigned the default value for that data type (0 for numeric data types, and empty string for String types, and Nothing for all object type variables. For example,

    Function Test(L1 As Long, L2 As Long, _
            Optional P1 As Variant, Optional P2 As Variant) As String
        Dim S As String
        If IsMissing(P1) = True Then
            S = "P1 Is Missing."
            S = "P1 Is Present (P1 = " & CStr(P1) & ")"
        End If
        If IsMissing(P2) = True Then
            S = S & "  " & "P2 Is Missing"
            S = S & "  " & "P2 Is Present (P2 = " & CStr(P2) & ")"
        End If
        Test = S
    End Function
Here, both L1 and L2 are required but P1 and P2 are optional. Since both are Variant types, we can use IsMissing to determine whether the parameter was passed in. IsMissing returns True of the Variant parameter is omitted, or False is the Variant parameter is included. If the data type of the optional parameter is any data type other than Variant, IsMissing will return False.

However, you can declare optional parameters of any data type and provide a default value to be used if that parameter is omitted. But the IsMissing function works only with Variant data types, so if you use an optional Long with a default value, there is no way to determine whether that parameter was actually passed to the procedure with a value that happens to be the same as the default value, or whether the parameter was omitted. For example,

    Function Test2(Optional V As Variant, Optional L As Long = -1) As String

Here, both V and L are optional, but only V is a Variant type, so only V may be tested with the IsMissing function. If L is omitted, IsMissing will return False since L is not a Variant. If omitted, L will have a value of -1. This may well be sufficient, but there is no way to determine whether L was actually passed into the procedure. If you write code such as this, you should choose a default value that will never occur if optional value is passed in. What that default value should be depends entirely on what the procedure does and the operating environment in which it is used.

Optional parameters must be the last parameters declared for the procedure. That is, once one parameter is declared as Optional only other Optional parameters may follow it. (This is not the case for Property procedures. See the section on Property Procedures later in this article.) The first procedure declarion shown below (GoodFunction) is valid because all the optional parameters are at the end of the parameter list. The second procedure declaration (BadFunction) is invalid because a required parameter (L2) follows an optional parameter (M1). You will get a compiler error ("Expected: Optional") if you attempt to use code like the BadFunction declaration.

    Function GoodFunction(L1 As Long, L2 As Long, _
        Optional M1 As Variant, Optional M2 As Variant) As Variant

    Function BadFunction(L1 As Long, Optional M1 As Variant, _
        L2 As Long, Optional M2 As Variant) As Variant

A User Defined Type (UDT -- a structure declared with the Type keyword) cannot be an optional parameter, nor can a UDT be an element in a ParamArray parameter type. Code that attempts to do this will cause the compiler to emit an error.


ParamArray Parameter Type

By using the Optional keyword, you can declare a function that accepts both required and optional parameters, but you are still limited to a fixed number of parameters. For example, the declaration

    Sub Test(L1 As Long, Optional P1 As Variant, Optional P2 As Variant)
allows P1 and P2 as optional parameters, but this function is still limited to a maximum of three parameters -- you can't pass in four or more parameters. Moreover, each optional parameter must be tested with IsMissing to determine whether it was passed. This can be onerous if you have a large number of optional parameters.

The solution to this problem is the ParamArray parameter type. A ParamArray allows any number of parameters, including none at all, to be passed into a procedure. Note that a ParamArray allows for optional parameters following any number of required parameters. Only the ParamArray elements are option. Any parameters declared before the ParamArray are required. There are a few rules for using a ParamArray:

  • The ParamArray variable must be an array of Variant data types.
  • The ParamArray variable must be the last parameter in the parameter list. (The reason for this is that were another parameter to follow the ParamArray, it would be impossible for the code to determine where the ParamArray variables end and the subsequent variables begin.)
  • No Optional parameters may appear in the parameter list. That is, the usages of Optional and ParamArray are mutually exclusive. You can have one or neither, but not both.
  • The function must declare at most one ParamArray variable. It is illegal (and nonsensical if you really think about it) to have two ParamArray parameters declared for a procedure.
  • Since a ParamArray parameter is an array, it is always passed by reference (ByRef) so any changes in the called procedure (which declares the ParamArray) to the elements in the ParamArray are changed in the calling procedure. (See Passing And Returning Arrays for more information about passing arrays.)
  • The base of the ParamArray array is 0, regardless of the setting of the module's Option Base statement. If zero parameters are passed, the LBound is 0 and the UBound is -1.
  • A User Defined Type (UDT) cannot be an element of a ParamArray parameter type.

The following is an example of a procedure declaration that accepts one required parameter, L1, and then accepts any number of additional parameters in the ParamArray Args() parameter.

    Sub Test(L1 As Long, ParamArray Args() As Variant)
Since the ParamArray Args() parameter is an array, you can use normal array methods on the array. For example, you can loop through the elemets:
    Function SumUp(ParamArray Args() As Variant) As Double
        Dim Sum As Double
        Dim Ndx As Long
        For Ndx = LBound(Args) To UBound(Args)
            Sum = Sum + Args(Ndx)
        Next Ndx
        SumUp = Sum
    End Function

Since the data type of the ParamArray Args() As Variant declares Variant data type, the elements of Args may contain anything, including numbers, strings, objects, and arrays, in addition to being Empty or NULL. Unless you have tight control over what is being passed in the array, your code should check the type of each Args element to ensure it is valid. For example, a better version of the SumUp function above would be:

    Function SumUpBetter(ParamArray Args() As Variant) As Double
        Dim Sum As Double
        Dim Ndx As Long
        For Ndx = LBound(Args) To UBound(Args)
            If IsNull(Args(Ndx)) = False Then
                If IsArray(Args(Ndx)) = False Then
                    If IsObject(Ndx) = False Then
                        If IsError(Ndx) = False Then
                            Sum = Sum + Args(Ndx)
                        End If
                    End If
                End If
            End If
        Next Ndx
        SumUpBetter = Sum
    End Function
While all this testing may seem like overkill, you must remember that with a Variant, you have no control over what that variable might contain and your code should test for possible errors.

You cannot have optional arguments within the ParamArray itself. If you omit an argument in a ParamArray an error is placed in that element of the array. For example,

    Function FFF(ParamArray Args() As Variant) As Long
        Dim N As Long
        Dim D As Double
        For N = LBound(Args) To UBound(Args)
            If IsError(Args(N)) = True Then
                Debug.Print "ERROR In ParamArray At Element: " & CStr(N)
                D = D + Args(N)
            End If
        Next N
        FFF = D
    End Function
Call this function with code like the following:
    Sub AAA()
        Dim D As Double
        D = FFF(1, , 3, 4)
        Debug.Print D
    End Sub
This will illustrate that the second value passed in the ParamArray (the missing element) is treated as an error by the code in function FFF.

The array variable declared as a ParamArray has a LBound value of 0, regardless of the module's Option Base setting. If no parameters were passed via the ParamArray variable, the LBound of the ParamArray is 0 and the UBound of the ParamArray is -1. The number of elements in the ParamArray can be determine code similar to the following:

    Function FFF(ParamArray Args() As Variant) As Long
        Dim NumArgs As Long
        NumArgs = UBound(Args) - LBound(Args) + 1
        FFF = NumArgs
    End Function


Optional Arguments In Property Procedures

The rules for working with Optional parameters and ParamArray variables are different when working with Proprerty procedures in class modules. Typically, a Property Get declarations take no parameters an return a single value. A typical Property Let procedure take one parameter, the value of that property. However, it is perfectly legal to use optional parameters in a procedure's declaration. For example, the following code is valid:

    Public Property Get Value(Optional A As Integer, Optional B As Integer) As Long
        Value = pValue + A + B
    End Property
    Public Property Let Value(A As Integer, B As Integer, V As Long)
        pValue = A + B + V
    End Property
A few things are noteworthy. The parameter declarations of the Get procedure must match the parameter declaration of Let, but the last (or only) parameter to the Let procedure must matct the return data type of the Get procedure. You can see this in the code above in which the return type of the Get Value procedure is a Long, the same as the last parameter of the Let procedure (parameter V). You'll also notice that the parameters A and B of the Get procedures are declared Optional while they are required in the Let procedure. Their data types (Integer in the example) must match, but they need not match in optionality.

Like the optional parameters shown above, a Propert Get/Let pair may use a ParamArray parameter. As before, the variable types of the Get/Let pair must match. For example,

    Public Property Let Value2(ParamArray Args() As Variant, V As Long)
        ' some code
    End Property
    Public Property Get Value2(ParamArray Args() As Variant) As Long
        ' some code
    End Property
Note here than the last argument to the Let procedure (parameter V) is the same type as the return type of the Get procedure. In practical terms, you will likely use the ParamArray array in a Property Let procedure than you would in a Property Get procedure, but just be aware that the ParamArray must exist in both procedures even if you use it only in one.

This differing behavior between Property Get/Let procedures and regular Sub and Function procedures (such as having optional parameters occurring before required parameters) becomes clear when you see how those properties are used in code. Using the Value2 Get/Let properties shown above, we would access these properties with code like the following:

    Dim C As Class1
    Dim L As Long
    Set C = New Class1
    ' LET Value2
    C.Value2(1, 2, 3) = 5
    ' GET Value2
    L = C.Value2(11, 22, 33)
In the Let procedure, the ParamArray applies to the values within the parentheses (1, 2, 3), and the actual property assignment variable (V in the declaration, 5 in the sample code) is the last parameter declared for the Let procedure. If you find yourself declaring multiple parameters for a Get or Let procedure, you may want to consider changing it from a property to a method of the class.

This page last updated: 29-September-2007