Microsoft Office Development and Consultancy

VBA

What is the Variant Datatype ?

The Variant datatype is the datatype for all variables that are not explicitly declared.

The Variant datatype is capable of holding data of any type except fixed-length strings and user defined types.

or

The Variant datatype can be used for all datatypes except fixed length strings and user defined types.

You can determine how the data in a Variant is treated using the VarType function or TypeName function.

You can use the Variant datatype in place of any other datatype to work with data in a more flexible way.

An error occurs when the Variant variables containing Currency, Decimal and Double values exceed their respective ranges.

The Variant datatype can also contain the special values “Error”, “Null”, “Nothing” and “Empty”.

Empty

The value Empty denotes a variant variable that has not been assigned to an initial value.

A Variant containing Empty is 0 if it is used in a numerical context and a zero length string (“”) if used in a string context.

Null

The value Null denotes a variant variable that intentionally contains no data.

Error

The Error value in a Variant datatype is used to indicate that an error condition occurred in the procedure or function.

Normal error handling does not occur giving you the chance to take alternative action in code

Error values can be created by converting real numbers to error values using the CVErr() function

Nothing

An object variable has not been set if it is assigned to “Nothing”.

IsMissing Function

This function can be used to test for optional parameters.

This function does not work on simple data types (like Integer, String, Date) but does work on Variant.