21 February 2010

VBA Is*() Functions Only Apply To Variant Types

VBA has several informational functions prefixed with "Is", such as IsNull(), IsMissing() and IsEmpty(), that only work with Variant types. I didn't realise it until I tried to apply them to non-Variant variables and noticed that they didn't work as I expected or always returned the same result.

For instance, if you define optional parameters for a procedure, you can only test if they are used in the procedure call using IsMissing() if the parameter is a Variant type. Using any other type of parameter with IsMissing() always returns False because all other parameter types have a default value supplied by the compiler or by the programmer.

Similarly, only Variant variables can have a Null value so that they can be tested by the IsNull() function. Object types can have a Nothing value, not Null, and can only be tested using the Is operator.

In the same vein, only Variant variables can be uninitialised and tested with IsEmpty(). All other variable types are initialised to some value (e.g. Objects are initialised to Nothing, Integers are initialised to 0).