VBA Variant Array

This tutorial will demonstrate how to create and work with Variant Arrays.

Array Variant Type

An array is a type of variable.  As with other variables, arrays are declared using the Dim Statement for a module or procedure level array, or the Public statement for a global level array.

A variant array is an array that is declared as having a variant data type. Variant data types can store any type of values: text, numbers, dates, time, or objects.

Variant Array Advantages

Variant arrays are special because they are easier to work with than typical arrays.

1. They’re easier to declare.
2. You don’t have to worry about what data type is being entered into the array (ex. if a cell contains text or numbers).
3. You can populate the array with the Array function (if the Array size is not defined when declared).
4. You don’t need to use the REDIM statement to resize / re-populate the array (if the Array size is not defined when declared).

Declaring a Variant Array

To declare a variant array, you can write this simple statement

Dim varNames()

This is considered a dynamic variant array.

Notice that you don’t need to specify the data type (it is assumed to be variant) or the array size.

Assign Values to Variant Array

Variant arrays can hold any type of values: text, numbers, dates, time, or objects.

To assign values to a variant array, you can use the Array Function:

'populate the array
    varNames() = Array("Fred", "Wilma", "Barney", "Betty")

This method may be easier to you than using the standard method:

'populate each element
   varNames(0) = "Fred"
   varNames(1) = "Wilma"
   varNames(2) = "Barney"
   varNames(3) = "Betty"

Resize Variant Array

Dynamic variant arrays can change size at runtime. You do not need to use the typical Redim or Redim Preserve statements required to resize standard arrays.

Variant Array Example

Putting all this together, this procedure will populate, and repopulate a variant array:

Sub TestArray()
'declare the  variable
    Dim varNames() As Variant
'populate the array
    varNames() = Array("Fred", "Wilma", "Barney", "Betty")
'return the values
    MsgBox Join(varNames, ",")
'repopulate the array
    varNames() = Array(400, 500)
'return the new values
    MsgBox Join(varNames, ",")
End Sub

We have populated the varNames array with 4 names, and then with 2 numbers.  The message boxes will appear as follows.