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.