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 vMyVariable 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.
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 vMyVariant As Variant
If (VBA.IsEmpty(vMyVariant) True) Then
If (vMyVariant = 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 vMyVariant As Variant
If (VBA.IsNull(vMyVariant) True) Then
If (vMyVariant = Null) Then
Error
This keyword/value can be used to store an error number.
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.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext