Optional Arguments

In previous versions of Microsoft Excel, optional arguments to procedures were required to use the Variant data type. In Microsoft Excel 97 VBA, it is now possible to declare optional arguments to your procedures with a specific data type. If one of these arguments is not passed, however, that argument will not evaluate as missing.

Any optional argument of a specifically declared data type that is not passed by the calling procedure will assume the default value of that data type (0 for integers and longs, an empty string for strings, and so on). Therefore, if you rely on IsMissing you will have to continue to declare optional arguments as type Variant.

You can also provide your own default values for your optional arguments. In the following examples, you'll see different default values depending on which method you use in your code. Here are some simple code fragments to illustrate these differences. All three examples assume that you call this routine without an argument.

Example 1

Implied variant (the same as Microsoft Excel 5/95):

Sub MyProcedure (Optional MyArgument)

Example 2

Specific type, VBA default:

Sub MyProcedure (Optional iMyArgument as Integer)

Example 3

Specific type, developer default:

Sub MyProcedure (Optional iMyArgument as Integer = -1)

Example 1 would pass the argument as a variant. If you tested the argument with IsMissing, it would evaluate to True.

Examples 2 and 3 would both contain a default integer value, either 0 in Example 2 (the VBA default value for integers) or -1 in Example 3. This allows you to provide a specific default if you don't want to use the VBA default value for the data type of your argument.