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.
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."
Else
S = "P1 Is Present (P1 = " & CStr(P1) & ")"
End If
If IsMissing(P2) = True Then
S = S & " " & "P2 Is Missing"
Else
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.
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)
Else
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
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