VBA Variant Array
In this Article
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.
- They’re easier to declare.
- You don’t have to worry about what data type is being entered into the array (ex. if a cell contains text or numbers).
- You can populate the array with the Array function (if the Array size is not defined when declared).
- 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
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.
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!