Skip to main content

Excel Variant data type

The Variant data type is the data type for all variables that are not explicitly declared as some other type (using statements such as Dim, Private, Public, or Static).

The Variant data type has no type-declaration character.

A Variant is a special data type that can contain any kind of data except fixed-length String data. (Variant types now support user-defined types.) A Variant can also contain the special values Empty, Error, Nothing, and Null. You can determine how the data in a Variant is treated by using the VarType function or TypeName function.

Use the VarType function to test what type of data is held in a Variant.

Numeric data can be any integer or real number value ranging from -1.797693134862315E308 to -4.94066E-324 for negative values and from 4.94066E-324 to 1.797693134862315E308 for positive values.

Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer. However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double.

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

Use the Variant data type in place of any data type to work with data in a more flexible way. If the contents of a Variant variable are digits, they may be either the string representation of the digits or their actual value, depending on the context. For example:

Dim MyVar As Variant 
MyVar = 8884

In the preceding example, MyVar contains a numeric representation—the actual value 8884. Arithmetic operators work as expected on Variant variables that contain numeric values or string data that can be interpreted as numbers. If you use the + operator to add MyVar to another Variant containing a number or to a variable of a numeric type, the result is an arithmetic sum.

The value Empty denotes a Variant variable that hasn't been initialized (assigned an initial value). A Variant containing Empty is 0 if it is used in a numeric context, and a zero-length string ("") if it is used in a string context.

Don't confuse Empty with Null. Null indicates that the Variant variable intentionally contains no valid data.

In a Variant, Error is a special value used to indicate that an error condition has occurred in a procedure. However, unlike for other kinds of errors, normal application-level error handling does not occur. This allows you, or the application itself, to take some alternative action based on the error value. Error values are created by converting real numbers to error values by using the CVErr function.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>