VBA Variant

The Variant data type can be used to store any type of data except fixed-length strings and user-defined types. Variant can also store the special values Empty, Null, Missing, Nothing, and Error. Variant must be used to store Decimal type values because there is no explicit Decimal type. While the Variant data type is very flexible there is a tradeoff between that flexibility and the increased memory that the Variant type uses. It is more efficient to use specific data types when possible and to limit the use of Variants to when it is necessary. Variant is the default data type for variables and functions when no data type is specified.

Empty

Empty is the default value for the Variant data type. Empty is equivalent to 0, a zero-length string, or False depending on the context in which it is used. To test if a Variant is Empty use the IsEmpty function.

Public Sub Example()

    Dim V As Variant

    Debug.Print IsEmpty(V)  'Prints: True

    Debug.Print V = 0       'Prints: True

    Debug.Print V = False   'Prints: True

    Debug.Print V = ""      'Prints: True

    'Empty can be directly assigned to a Variant
    V = Empty

End Sub

Null

Variables of type Variant can be assigned the value Null. Null indicates that the variable does not have a value. Variant is the only type that can store Null. To test if a Variant is Null use the IsNull function.

Public Sub Example()

    Dim V As Variant

    V = Null

    If IsNull(V) Then
        Debug.Print "Variant is Null"
    Else
        Debug.Print "Variant is not Null"
    End If

End Sub

Missing

Missing is a special value that indicates that no argument was passed to an optional parameter of type Variant. To test if an optional parameter of type Variant was passed a value, use the IsMissing function.

Public Sub Example()

    TestMissing

    TestMissing "Hello, World!"

End Sub

Public Sub TestMissing(Optional V As Variant)

    If IsMissing(V) Then
        Debug.Print "No argument was passed to V parameter."
    Else
        Debug.Print "Argument was passed to V parameter."
    End If

End Sub

Error

Variants can be assigned an error value using the CVErr function. This allows functions to return an error value. This can be useful for returning errors from user-defined spreadsheet functions in Excel. The IsError function can be used to check if a Variant is storing an error.

Public Function SumValues(Values As Variant) As Variant

    If Not IsArray(Values) Then
        SumValues = CVErr(2015) '#VALUE! Error
        Exit Function
    End If

    Dim Value As Variant

    For Each Value In Values
        SumValues = SumValues + Value
    Next Value

End Function

Decimal

The Decimal type in VBA cannot be explicitly declared. Variant must be used to store values of type Decimal. The CDec function is used with the Variant data type to store Decimal type values.

Public Sub Example()

    Dim DecimalValue As Variant

    DecimalValue = CDec("79228162514264337593543950335")
    Debug.Print DecimalValue

    DecimalValue = CDec(1E-28) 'CDec("0.0000000000000000000000000001")
    Debug.Print DecimalValue

End Sub

Objects

Objects can be assigned to Variants. This allows objects and non-objects to be passed to the same parameter of a procedure without raising a type error. The IsObject function can be used to check if a Variant is an object. Because Variants can be Objects, Variants can also be set to Nothing.

Public Sub Example(V As Variant)

    If IsObject(V) Then
        Debug.Print "V is an Object"
    Else
        Debug.Print "V is not an Object"
    End If 

End Sub

Arrays

Variants can be used to store arrays. Arrays of any type can be stored in a Variant. The IsArray function can be used to test whether a Variant is storing an array.

Note: An array of type Variant and a Variant containing an array are different concepts altogether.

Public Sub Example()

    'An array of type Variant stored in a Variant
    Dim Arr As Variant
    Arr = Array(1, 2, 3)
    PrintArray Arr

    'An array of type Variant
    Dim VariantArr() As Variant
    VariantArr = Array(1, 2, 3)
    PrintArray VariantArr

    'An array of type String
    Dim StringArr() As String
    StringArr = Split("A,B,C", ",")
    PrintArray StringArr

End Sub

Public Sub PrintArray(Arr As Variant)

    If Not IsArray(Arr) Then
        Err.Raise 5
    End If

    Dim i As Long

    For i = LBound(Arr) To UBound(Arr)
        Debug.Print Arr(i)
    Next i

End Sub