VBA Arrays
An array is a data structure which stores a set of sequentially indexed variables of the same data type in a contiguous block of memory. Arrays are used to store lists and tables of data. Arrays can have one dimension or multiple. Arrays in VBA can have a maximum of 60 dimensions. Arrays begin at index 0 by default.
Array Functions
VBA provides a number of useful built-in functions for working with arrays. Note that many high-level functions such as sorting, searching, and getting the length of an array do not exist in VBA. These functions must be implemented by the user. Download and import the modArray module to use high-level functions.
| Function | Description |
|---|---|
| Array | Returns an array of type Variant. Cannot be assigned to a static array. Must be assigned to a dynamic array or a Variant. |
| Lbound | Returns the lower bound of an array. Causes an error if the array is not dimensioned. |
| Ubound | Returns the upper bound of an array. Causes an error if the array is not dimensioned. |
| Join | Returns a string representing all array elements joined by a delimiter. The default delimiter is a space character (Chr 32). |
| Split | Splits up a string by a delimiter and places the elements into a string array. The default delimiter is a space character (Chr 32). |
| Filter | Returns an array which is a filtered subset of the source array's elements. |
| Erase | Resets the elements of a static array to the default value determined by the array's data type. Resets dynamic arrays to an uninitialized state with no values or dimensions. |
| ReDim | Reinitializes an array with a new given size. Resets elements to their default values based on the array's type. |
| ReDim Preserve | Creates a copy of an array with a new given size. The array's original values are copied into the new array. |
Option Base {0|1}
The Option Base statement sets the default starting index for arrays within a given module. If Option Base is not specified then arrays begin at index 0 by default. Only 0 or 1 can be used with Option Base. Add Option Base followed by 0 or 1 at the top of a module.
Option Base 1
Public Sub Example()
Dim Arr() As Variant
Arr = Array(2, 3, 5)
Debug.Print LBound(Arr) 'Prints 1
Debug.Print UBound(Arr) 'Prints 3
End SubStatic Vs Dynamic Arrays
Arrays can be declared as static arrays or dynamic arrays.
Static Arrays
Static arrays have a predefined size and number of dimensions. Static arrays are given an explicit lower and upper bound at design-time.
Public Sub Example()
'Explicit lower and upper bound
Dim Arr(0 To 2) As Long
End SubThe lower bound can be omitted from the declaration. If no lower bound is explicitly given, it is determined by the Option Base statement or it will be 0 by default. Keep in mind that when the lower bound is ommitted, the number provided in the array declaration is the upper bound and not the size of the array.
Public Sub Example()
'Lower bound is implicitly 0 and upper bound is 2
Dim Arr(2) As Long
End SubAll elements in a static array will be initialized with the default value of the array's type.
Public Sub Example()
Dim Arr(0 To 2) As Long
'Default value for Long is 0
Debug.Print Arr(0) 'Prints 0
Debug.Print Arr(1) 'Prints 0
Debug.Print Arr(2) 'Prints 0
End SubUsing the Erase statement on a static array resets all values in the array to the default value based on the array's data type and the array keeps its dimensions and size.
Public Sub Example()
Dim Arr(0 To 2) As Long
Arr(0) = 1
Arr(1) = 2
Arr(2) = 3
Debug.Print Arr(0) 'Prints 1
Erase Arr
Debug.Print Arr(0) 'Prints 0 (default value for Long)
End SubDynamic Arrays
Dynamic arrays have no predefined size or dimensions. Dynamic Arrays are dimensioned using the Redim statement or they are dimensioned implicitly when they are assigned an array.
Public Sub Example()
Dim Arr() As Long
ReDim Arr(0 To 2) As Long
Arr(0) = 1
Arr(1) = 2
Arr(2) = 3
End SubPublic Sub Example()
Dim Arr() As Variant
Arr = Array(1, 2, 3)
End SubUsing the Erase statement on a dynamic array resets the array to an uninitialized state with no dimensions or size.
Public Sub Example()
Dim Arr() As Variant
Arr = Array(1, 2, 3)
Debug.Print Arr(0) 'Prints 1
Erase Arr
Debug.Print Arr(0) 'Causes Error
End SubVariant Arrays
Arrays of any type can be stored in a variable of type Variant. Variant arrays can be assigned using the Array function or by assigning an existing array to a Variant. Assigning an existing array to a Variant creates a copy of the original array and stores it in the Variant.
Public Sub Example()
Dim Arr As Variant
'Assign using Array function
Arr = Array(1, 2, 3, 4, 5)
'Assign copy of another array
Dim LArr(0 To 2) As Long
Arr = LArr
End SubVariant Arrays Are Dynamic Arrays
An array stored in a Variant is a dynamic array, even if it was assigned from a static array. Using ReDim and Erase have the same effects on a Variant array that they have on normal dynamic arrays.
Public Sub Example()
Dim LArr(0 To 2) As Long
LArr(0) = 1
LArr(1) = 2
LArr(2) = 3
Dim Arr As Variant
Arr = LArr
Erase Arr
ReDim Arr(0 To 2)
Arr(0) = 1
Arr(1) = 2
Arr(3) = 3
End SubVariant Arrays Can Change Type
The data type of a Variant array can be changed using ReDim, which is not permitted for other types of arrays.
Public Sub Example()
Dim LArr() As Long
ReDim LArr(0 To 2)
LArr(0) = 1
LArr(1) = 2
LArr(2) = 3
'ReDim LArr(0 To 2) As Double 'Not Allowed - Causes compile error
Dim Arr As Variant
'Arr is of type Variant/Long()
Arr = LArr
'Arr is now of type Variant/Double()
ReDim Arr(0 To 2) As Double
End SubVariant Array Vs Array of Variants
An array stored in a Variant is not the same as an array of type Variant. Arrays stored in Variants can have explicit data types. An array of type Variant is an array of Variants that can store any type of data.
Public Sub Example()
'Variant Array
Dim Arr As Variant
Dim LArr(0 To 2) As Long
LArr(0) = 1
LArr(1) = 2
LArr(2) = 3
'Variant array stores an array with an explicit type
'Arr is of type: Variant/Long()
Arr = LArr
Arr(0) = "Hello" 'Causes error
End SubPublic Sub Example()
'Array of Variants
Dim VArr(0 To 2) As Variant
'An arrays of Variants can store any type of data
'Array is of type: Variant()
Arr(0) = 1
Arr(1) = True
Arr(2) = "Hello"
End SubBug: Variant Object Array
There is a bug that occurs when using arrays of objects with a Variant. The LBound and UBound functions normally cause a runtime error when used on uninitialized arrays. When LBound or UBound is called on an uninitialized array of objects stored in a Variant, the functions return 0 and -1 respectively. Storing the object array in the Variant also changes the behavior of LBound and UBound on the original object array to also return 0 and -1. Having an array with an upper bound lower than its lower bound is not valid and declaring an array like this explicitly causes a compile error.
Public Sub Example()
Dim Arr
Dim OArr() As Object
'Dim OArr1(0 To -1) As Object 'Compile error
Debug.Print LBound(OArr) 'Causes Error
Debug.Print UBound(OArr) 'Causes Error
Arr = OArr
Debug.Print LBound(Arr) 'Prints 0
Debug.Print UBound(Arr) 'Prints -1
Debug.Print LBound(OArr) 'Prints 0, Does not cause error
Debug.Print UBound(OArr) 'Prints -1, Does not cause error
End SubThis bug only appears to affect arrays of objects and does not seem to occur for other types.
Public Sub Example()
Dim Arr
Dim LArr() As Long
Arr = LArr
Debug.Print LBound(Arr) 'Causes Error
Debug.Print UBound(Arr) 'Causes Error
End SubThe implication of this bug is significant in some cases. Consider a function to retrieve the number of dimensions of an array. The condition on line 9 is necessary for the function to work for arrays of objects.
Public Function DimensionsOfArray&(Arr)
If Not IsArray(Arr) Then
Err.Raise 5
End If
Dim i&
i = 1
On Error GoTo Fail
Do Until UBound(Arr, i) < LBound(Arr, i)
DimensionsOfArray = i
i = i + 1
Loop
Fail:
On Error GoTo 0
End FunctionMulti-Dimensional Arrays
Multi-Dimensional arrays are arrays that have more than one dimension. Arrays can have a up to 60 dimensions in VBA. Multi-Dimensional arrays are declared using a comma to separate the bounds of each dimension. To access a multi-dimensional array at a specific location, specify the index within each dimension separated by a comma. To get the lower and upper bounds of a specific dimension include the optional dimension argument in the LBound and UBound functions.
Public Sub Example()
Dim Arr(0 To 2, 0 To 2) As Long
Dim i As Long
Dim j As Long
Dim k As Long
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
Arr(i, j) = k
k = k + 1
Next j
Next i
Debug.Print Arr(0, 0), Arr(0, 1), Arr(0, 2)
Debug.Print Arr(1, 0), Arr(1, 1), Arr(1, 2)
Debug.Print Arr(2, 0), Arr(2, 1), Arr(2, 2)
'0 1 2
'3 4 5
'6 7 8
End SubJagged Arrays
A jagged array is an array of arrays. Jagged arrays are similar to multi-dimensional arrays except instead of multiple dimensions there are multiple levels of nested single-dimensional arrays. A jagged array must be an array of type Variant. To access elements in a jagged array, use a set of parentheses for each level.
Public Sub Example()
Dim Arr(0 To 2) As Variant
Arr(0) = Array(0, 1, 2)
Arr(1) = Array(3, 4, 5)
Arr(2) = Array(6, 7, 8)
Debug.Print Arr(0)(0), Arr(0)(1), Arr(0)(2)
Debug.Print Arr(1)(0), Arr(1)(1), Arr(1)(2)
Debug.Print Arr(2)(0), Arr(2)(1), Arr(2)(2)
'0 1 2
'3 4 5
'6 7 8
End SubUser-Defined Type Arrays
Arrays can be used with User-Defined Types. An array of a user-defined type can be used instead of a 2D array for storing records of data. Arrays of user-defined types are useful for storing tabular data in a type-safe, meaningful, and memory efficient way. Note that arrays of user-defined types cannot be iterated over using a For Each loop. Arrays of user-defined types are discussed again in the section on user-defined types.
Option Explicit
Public Type ExampleType
Message As String
Number As Long
End Type
Public Sub Example()
Dim Arr(0 To 2) As ExampleType
With Arr(0)
.Message = "Hello"
.Number = 1
End With
With Arr(1)
.Message = "Hi"
.Number = 2
End With
With Arr(2)
.Message = "Hey"
.Number = 3
End With
Debug.Print Arr(0).Message, Arr(0).Number
Debug.Print Arr(1).Message, Arr(1).Number
Debug.Print Arr(2).Message, Arr(2).Number
'Causes Compile Error - For Each cannot be used with array of User-Defined Types
'Dim mt As Variant
'For Each mt In Arr
' Debug.Print mt
'Next mt
End SubAssigning Arrays
Arrays can be assigned in a variety of ways.
Directly Assign Elements
Dimensioned arrays can have elements assigned directly by index.
Public Sub Example()
Dim Arr(0 To 2) As Long
'Assign elements individually
Arr(0) = 1
Arr(1) = 2
Arr(2) = 3
'Assign elements directly using a loop
Dim i As Long
For i = Lbound(Arr) To Ubound(Arr)
Arr(i) = i
Next i
End SubArray Function
The Array function creates an array of type Variant containing the elements passed as arguments.
Public Sub Example()
Dim Arr() As Variant
Arr = Array(1, 2, 3, 4, 5)
End SubPublic Sub Example()
Dim Arr As Variant
Arr = Array(1, 2, 3, 4, 5)
End SubSplit Function
The Split function splits up a string by a delimiter into a string array.
Public Sub Example()
Dim Arr() As String
Arr = Split("1,2,3,4,5", ",")
End SubPublic Sub Example()
Dim Arr As Variant
Arr = Split("1,2,3,4,5", ",")
End SubExcel Ranges
Excel Ranges can be assigned directly to arrays. Assigning a Range to an array will assign a two-dimensional array containing the values in the Range's cells. Even if the Range is only one row or column the array will still be two-dimensional. If the Range contains date or currency type data the Value property of the Range object should be used. Otherwise Value2 can be used.
Public Sub Example()
Dim Arr() As Variant
Arr = Range("A1:C3").Value 'Use if data contains date or currency
Arr = Range("A1:C3").Value2
End SubSingle cell ranges cannot be assigned to arrays because the Value property of a single cell returns the value of that cell and not an array. When assigning to an array, a type error will occur. When assigning to a Variant, the value will be assigned which would cause an error when attempting to access the Variant as an array.
Public Sub Example()
Dim Arr1() As Variant
Arr1 = Range("A1:C3").Value 'Assigns an array
Arr1 = Range("A1").Value 'Causes error
Dim Arr2 As Variant
Arr2 = Range("A1:C3").Value 'Assigns an array
Arr2 = Range("A1").Value 'Assigns the value in cell A1
Debug.Print Arr2(0) 'Causes error
End SubTo assign an array to a Range, ensure that the Range is sized correctly to fit the array and that the array is oriented properly. The Resize function can be used to obtain an appropriately sized Range.
Public Sub Example()
Dim Arr(0 To 2, 0 To 2) As Variant
Arr(0, 0) = 1
Arr(0, 1) = 2
Arr(0, 2) = 3
Arr(1, 0) = 4
Arr(1, 1) = 5
Arr(1, 2) = 6
Arr(2, 0) = 7
Arr(2, 1) = 8
Arr(2, 2) = 9
Dim RowCount As Long
RowCount = UBound(Arr, 1) - LBound(Arr, 1) + 1
Dim ColCount As Long
ColCount = = UBound(Arr, 2) - LBound(Arr, 2) + 1
Range("A1").Resize(RowCount, ColCount).Value = Arr
End SubTo assign a one-dimensional array to a Range, the array may need to be transposed. One-dimensional arrays are equivelent to a row of data. A one-dimensional array can be assigned directly to a row. To assign a one-dimensional array to a column, the array will need to be transposed.
Public Sub Example4()
Dim Arr() As Variant
Arr = Array(1, 2, 3, 4, 5)
'Array can be directly assigned to a row
Range("A1:E1") = Arr
'Array must be transposed to assign to column
Range("A1:A5") = WorksheetFunction.Transpose(Arr)
End SubExcel Evaluate Function
The Evaluate function in Excel VBA can be used to assign to an array. Using square brackets around an expression is the same as using the Evaluate function.
Public Sub Example()
Dim Arr() As Variant
Arr = Evaluate("{1, 2, 3}")
Arr = [{1,2,3}]
Arr = Evaluate("{1,2;1,2;1,2}")
Arr = [{1,2;1,2;1,2}]
Arr = Evaluate("=TRANSPOSE({1;2;3;4;5})")
Arr = [=TRANSPOSE({1;2;3;4;5})]
End SubLooping Over Arrays
To iterate over an array, use a For loop with the LBound and UBound functions.
Public Sub Example()
Dim Arr() As Long
Redim Arr(0 To 99) As Long
Dim i As Long
For i = LBound(Arr) To UBound(Arr)
Arr(i) = i
Next i
End SubTo iterate over a multi-dimensional array, include the dimension in the LBound and UBound functions.
Public Sub Example()
Dim Arr() As Long
Redim Arr(0 To 99, 0 To 2) As Long
Dim i As Long
Dim j As Long
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
Arr(i, j) = i
Next j
Next i
End SubTo iterate over a jagged array, treat each array as a single dimensional array with the LBound and UBound functions.
Public Sub Example()
Dim Arr() As Variant
Redim Arr(0 To 2) As Variant
Arr(0) = Array(1, 2, 3)
Arr(1) = Array(4, 5, 6)
Arr(2) = Array(7, 8, 9)
Dim i As Long
Dim j As Long
For i = LBound(Arr) To UBound(Arr)
For j = LBound(Arr(i)) To UBound(Arr(i))
Debug.Print Arr(i)(j)
Next j
Next i
End SubResizing Arrays
To resize an array, use the Redim Preserve statement. Redim Preserve creates a copy of an array with new dimensions and copies the values from the original array to the new array. ReDim Preserve can be used to increase an array's size to allow for the addition of new elements to the array. Excessive array copying is inefficient so it is best to use an ArrayList data structure to minimize copying.
Note: Be sure to include the Preserve keyword otherwise the values in the array will be erased.
Public Sub Example()
'Add element to array
Dim Arr() As Long
ReDim Arr(0 To 2) As Long
Arr(0) = 1
Arr(1) = 2
Arr(2) = 3
ReDim Preserve Arr(0 To 3) As Long
Arr(3) = 4
End Sub