Variant

The Variant data type is capable of holding any type of data except fixed-length strings and user defined types.
The Variant data type can be used for all data types except fixed length strings and user defined types.
This data type automatically works out which data type is the most appropriate based on the value you assign.

Dim myVariable As Variant 

You can use the Variant data type in place of any other data type 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 data type can also contain the special values "Empty", "Null" and "Error",
This might seem like an easy option but you compromise both speed and memory by using Variant data types.


Variant is the Default

If you do not explicitly declare the data type of your variables then the Variant data type is assigned.
This data type can contain any type of data and its exact data type can change depending on what you do with it.

Option Explicit 
Dim iValue
iValue = 200
Call MsgBox(TypeName(iValue))

Decimal

The variant has a special sub type called Decimal that can be used to hold very large numbers.


String-Variant

The variant has a special sub type called string variant that can be used to hold variable length strings.


Empty

This keyword/value can only be used in conjunction with the Variant data type.
The Empty data type is the default value when you declare a variable as a Variant.

Dim myVariant As Variat 
If (VBA.IsEmpty(myVariant) True) Then
If (myVariant = Empty) Then

Null

This keyword/value can only be used in conjunction with the Variant data type.
This keyword/value is used when a variable declared as variant does not contain any valid data.

Dim myVariant As Variat 
If (VBA.IsNull(myVariant) True) Then
If (myVariant = Null) Then

Error




Calculations

You can perform operations on Variant variables but you need to be careful.
Arithmetic operations or functions can only be performed on numerical values.
If you are performing string concatenation make sure you use the ampersand (&) and not the plus (+).


Variant variables with numbers require 16 bytes of memory.
Variables of the Variant data type with characters usually require 22 bytes of memory plus the memory required by the string.


For negative numbers: -1.797693134862315E308 to -4.94066E-324.
For positive numbers: 4.94066E-324 to 1.797693134862315E308.



© 2018 Better Solutions Limited. All Rights Reserved. © 2018 Better Solutions Limited TopPrevNext