VBA Data Types and Limits Variant
Example
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.
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.
| Value | Resulting type |
|---|---|
| String values | String |
| Non-floating point numbers in Integer range | Integer |
| Non-floating point numbers in Long range | Long |
| Non-floating point numbers outside of Long range | Double |
| All floating point numbers | Double |
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().
