Using Optional Arguments

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 aren't required in order 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 an easy way to add functionality without updating all the code that calls those procedures. In addition, if you declare arguments that aren't always needed 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 once you denote one argument as optional, any arguments that follow it in the argument list must also be optional, as shown in this 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 need a way to check whether the optional argument was passed in. In many cases, if an optional argument hasn't been passed in, you may want it to have a default value. If the calling procedure doesn't 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 but Variant will always have a value, and it may not be possible to determine within the procedure whether the value was passed in or whether it's the default value. If you need to 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"
      Debug.Print varTest
   End If
End Sub

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