Pass optional objects to VBA functions

Not every argument has a value every single time you call it. When you need a function that's flexible enough to handle an empty argument, use VBA's Optional keyword.

Many of Office's built-in functions have optional arguments. If you don't explicitly specify a setting, the function uses a default. When you write your own code using VBA, you can mimic this functionality by using VBA's Optional keyword. Doing so adds a bit of flexibility to passed arguments, as you can choose to pass a value, or not. To take advantage of this behavior, use the following form:

Function name (arg1 AS datatype, Optional arg2 AS datatype)
End Function

When you call this function, you must supply a value for arg1 because it isn't optional; arg2, on the other hand is — you can choose to pass a value, or not.

WARNING: Be careful where you place the Optional keyword in your argument list. All arguments that follow Optional are optional by default, which opens the door to logic bugs.

There's a big gotcha when using Optional — just what value do you want VBA to use in the absence of a passed value?

The easiest way to handle a missing value is to assign a default value to the argument as follows:

Function name (arg1 AS datatype, Optional arg2 AS datatype = defaultvalue)
End Function

VBA automatically applies defaultvalue to arg2 when the function passes nothing.

In addition, you can use the IsMissing() function in the following form to check for missing arguments:

If IsMissing(arg2) = True Then...

If you choose this route, be sure to declare arg2 as a Variant because IsMissing() works only with the Variant datatype. That makes the IsMissing() function a little less handy than simply defining a default for the optional argument. A default is always my first line of defense in this situation, but IsMissing() comes in handy when a default isn't practical.

By Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.