VBA Data Types and Limits Variant


Dim Value As Variant    'Explicit
Dim Value               'Implicit

A Variant is a COM data type that is used for storing and exchanging values of arbitrary types, and any other type in VBA can be assigned to a Variant. Variables declared without an explicit type specified by As [Type] default to Variant.

Variants are stored in memory as a VARIANT structure that consists of a byte type descriptor (VARTYPE) followed by 6 reserved bytes then an 8 byte data area. For numeric types (including Date and Boolean), the underlying value is stored in the Variant itself. For all other types, the data area contains a pointer to the underlying value.

enter image description here

The underlying type of a Variant can be determined with either the VarType() function which returns the numeric value stored in the type descriptor, or the TypeName() function which returns the string representation:

Dim Example As Variant
Example = 42
Debug.Print VarType(Example)    'Prints 2 (VT_I2)
Debug.Print TypeName(Example)   'Prints "Integer"
Example = "Some text"
Debug.Print VarType(Example)    'Prints 8 (VT_BSTR)
Debug.Print TypeName(Example)   'Prints "String"

Because Variants can store values of any type, assignments from literals without type hints will be implicitly cast to a Variant of the appropriate type according to the table below. Literals with type hints will be cast to a Variant of the hinted type.

ValueResulting type
String valuesString
Non-floating point numbers in Integer rangeInteger
Non-floating point numbers in Long rangeLong
Non-floating point numbers outside of Long rangeDouble
All floating point numbersDouble

Note: Unless there is a specific reason to use a Variant (i.e. an iterator in a For Each loop or an API requirement), the type should generally be avoided for routine tasks for the following reasons:

  • They are not type safe, increasing the possibility of runtime errors. For example, a Variant holding an Integer value will silently change itself into a Long instead of overflowing.
  • They introduce processing overhead by requiring at least one additional pointer dereference.
  • The memory requirement for a Variant is always at least 8 bytes higher than needed to store the underlying type.

The casting function to convert to a Variant is CVar().