Dear John, How Do I... Work with Variants?

Variants are extremely flexible (some say too flexible), but they do allow for some clever new ways to structure and organize your data.

If not declared as something else, all variables default to Variant. You can store just about anything in a Variant variable, including arrays, objects, UDT structures, and other Variants. Arrays always contain like elements, which means you normally can't mix strings and numbers in the same array, for instance. But an array of Variants gets around this limitation. Consider the code below, which creates a Variant array and loads it with an integer, a string, and another Variant. To hint at the flexibility here, I've even stored a second Variant array in the third element of the primary Variant array. Remember, you can store just about anything in a Variant!

Option Explicit

Private Sub Form_Click()
    Dim i      `Note that this defaults to Variant
    Dim vntMain(1 To 3) As Variant
    Dim intX As Integer
    Dim strA As String
    Dim vntArray(1 To 20) As Variant
    `Fill primary variables
    strA = "This is a test."
    For i = 1 To 20
        vntArray(i) = i ^ 2
    Next i
    `Store everything in main Variant array
    vntMain(1) = intX
    vntMain(2) = strA
    vntMain(3) = vntArray()
    `Display sampling of main Variant's contents
    Print vntMain(1)         `0
    Print vntMain(2)         `This is a test.
    Print vntMain(3)(17)     `289
End Sub

Notice, in the last executable line of the procedure, how the Variant array element within another Variant array is accessed. The vntMain(3)(17) element looks, and indeed acts, somewhat like an element from a two-dimensional array, but the subscripting syntax is quite different. This technique effectively lets you create multidimensional arrays with differing dimensions and data types for all elements.

For Each Loops

Variants serve an important role in For EachDear John, How Do I... Next loops. You can loop through each member of a collection, or even through a normal array, using a For Each statement. The only type of variable you can use for referencing each element is an Object or a Variant. When looping through arrays you must use a Variant. When looping through a collection you may use an Object variable, but you always have the option of using a Variant.

Flexible Parameter Type

The Variant type is useful as a parameter type, especially for object properties for which you want to set any of several types of data in a single property. You can pass just about anything by assigning it to a Variant variable and then passing this data as an argument.

Variant-Related Functions

You should be aware of several useful functions for working with Variants. TypeName returns a string describing the current contents of a Variant. The family of Is functions, such as IsNumeric and IsObject, provides fast logical checks on a Variant's contents. Search the online help for more information about these and other related functions.

Empty and Null

Be careful of the difference between an Empty Variant and a Null one: a Variant is Empty until it's been assigned a value of any type; Null is a special indication that the Variant contains no valid data. A Null value can be assigned explicitly to a Variant, and a Null value propagates through all calculations. The Null value most often appears in database applications, where it indicates unknown or missing data.

Data Type Coercion

Variants are very flexible, but you need to be careful when dealing with the complexities of automatic data conversions. For example, some of the following program steps may surprise you:

Option Explicit

Private Sub Form_Click()
    Dim vntA, vntB
    vntA = "123"
    vntB = True
    Print vntA + vntB            `122
    Print vntA & vntB            `123True
    Print vntA And vntB = 0      `0
    Print vntB And vntA = 0      `False
End Sub

The first Print statement treats the contents of the two Variants as numeric values, and the second Print statement treats them as strings. The last two Print statements produce considerably different results based on the operational hierarchy, which isn't at all obvious. The best advice I can provide is this: Be cautious and carefully check out the results of your coding to be sure that the results you get are what you expect.