Share via

Using Optional Arguments

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Optional arguments are arguments that are not required for a procedure to be compiled and run. Many built-in functions and methods take optional arguments. Adding optional arguments to user-defined procedures is a way to add functionality without updating all the code that calls those procedures. In addition, if you declare arguments that are not always required as optional, you can minimize resource use by passing only those arguments that are necessary for a given procedure call.

To define an optional argument in a user-defined procedure, use the Optional keyword. You can have as many optional arguments as you want, but when you denote one argument as optional, any arguments that follow it in the argument list must be optional also, as shown in the following procedure definition:

Function SomeProc(strRequired1 As String, _
                  strRequired2 As String, _
                  Optional lngOpt1 As Long, _
                  Optional blnOpt2 As Boolean)

Within the body of the procedure, you must have a way to check whether the optional argument was passed in. In many cases, if an optional argument has not been passed in, you might want it to have a default value. If the calling procedure does not provide a value for an optional argument, the optional argument is automatically initialized in the same way it would be if it were a variable — string arguments are initialized to a zero-length string, numeric arguments to zero (0), Boolean arguments to False, and so on.

You can override this default initialization by providing a different default value for the optional argument in the procedure definition. The value you provide becomes the default value when the calling procedure fails to pass a value for the optional argument. The following procedure definition sets the default value for an argument of type Long to 1 and for an argument of type Boolean to True:

Function SomeProc(strRequired1 As String, _
                  strRequired2 As String, _
                  Optional lngOpt1 As Long=1, _
                  Optional blnOpt2 As Boolean=True)

As you can see, an argument of any data type except Variant always will have a value, and it might not be possible to determine within the procedure whether the value was passed in or whether it is the default value. If you must know whether the argument was passed in, define the optional argument as type Variant. Then, use the IsMissing function within the procedure to determine whether the argument has been passed in, as shown in the following procedure:

Sub TestIsMissing(varTest As Variant)
   If IsMissing(varTest) Then
      Debug.Print "Missing"
   Else
      Debug.Print varTest
   End If
End Sub

The IsMissing function works only with the Variant data type; because any other data type always will have a default initialization value, the IsMissing function will return False regardless of whether a value has been passed for the argument.

See Also

Tips for Defining Procedures in VBA | Using Parameter Arrays | Passing Arguments by Value or by Reference