The VBA Array is a very convenient and efficient for storing multiple items of usually the same data type. The size of a VBA Array can be either fixed or dynamic depending on how it is declared. Arrays can also be 1 or multi-dimensional. In some cases however you might be better of considering other alternatives to arrays such as Collections depending on the scenario.
A
one-dimensional VBA Array
contains a sequence of elements within a single dimension. The default numbering sequence of these elements starts at 0. You can redefine the starting and ending index of the sequence of elements using the
X to Y
statement. See an example below:
Let’s start by introducing a simple one-dimensional VBA Array of 10 items:
'10 items of type Long
Dim onedimArray(1 to 10) As Long
'Set the 2nd item value to 20
onedimArray(2) = 10
Notice that I have sized the array for indices 1 to 10. This is not a required statements as I can also declare the VBA Array size without the index range.
Dim onedimArray(9) As Long '10 items of type Long (0 to 9)
Another example of 10 item array indexed from 2 to 11.
Dim onedimArray(2 to 11) As Long '10 items of type Long
onedimArray(1) = 10 'ERROR! Index starts at 2!
Multi-dimensional VBA Array
A
multi-dimensional VBA Array
contains a sequence of elements within multiple dimensions (as many as defined). The default numbering sequence of these elements, within each dimension, starts at 0. You can redefine the starting and ending index of the sequence of elements using the
X to Y
statement. See an example below:
In some cases you might not want to limit yourself to just 1 dimension. VBA Arrays can have multiple dimensions. See a simple example below:
Dim twodimArray(5, 15) As Long
twodimArray(1,15) = 10
twodimArray(2,10) = 10
Dim threedimArray(5, 10, 15) As Long
threedimArray(2, 10, 12) = 3
threedimArray(5, 10, 15) = 9
In case of dynamic arrays (read on) multidimentional arrays in VBA have proven problems with using the
Preserve
statement to preserve the arrays contents when resizing it. For more information on how to workaround this problem see this post
here
.
Fixed VBA array
The VBA Arrays above were all of fixed size i.e. their size was defined when declaring the VBA Array using the
Dim
statement. Let’s quickly compare fixed and dynamic sized arrays.
Dim fixedArray(5) As String 'this is a fixed size Array indexed 0 to 5
Redim fixedArray(10) 'ERROR! FORBIDDEN!
Dim dynamicArray() As String
Redim dynamicArray(5) 'OK. Declaring the size of a Dynamic Array
Fixed size arrays should be used when you know before execution how many items your VBA Array should contain.
Fixed arrays are equivalent in usage to dynamic arrays although simply have the limit of being un-resizable
.
Dynamic VBA array
Dynamic sized VBA Arrays are arrays that can be sized and re-sized even multiple times. This is useful
when you are either not sure before execution what the required size of your VBA Array should be
or
you want to optimize memory by allocating a large sized array for only a short period of time.
Let’s start with a simple example:
Dim dynamicArray() As Long 'Declare a dynamic Array
'Set the size of the Array to 10 items (0 to 9)
ReDim dynamicArray(9)
dynamicArray(1) = 20
If you want to set your own index range feel free to use the ReDim statement as such:
'Declare a dynamic Array
Dim dynamicArray() As Long
'Set the size of the Array to 10 items
ReDim dynamicArray(2 to 11)
dynamicArray(1) = 20
ReDim Statement – Resizing a Dynamic VBA Array
The
ReDim
statement is used to set the size of your VBA Array or to resize it if needed. See an example below.
'Declare a dynamic Array
Dim dynamicArray() As Long
'Set the size of the array to 10 items
ReDim dynamicArray(9)
dynamicArray(2) = 5
'Resize the size of the Array from 10 to 15 (erasing all items)
ReDim dynamicArray(14)
Debug.Print dynamicArray(2) 'Result: 0 - item not preserved
The problem with a regular ReDim is that your previous items will get erased. When you want to resize an VBA Array of existing items you need to use
ReDim with Preserve
. See an example below:
'Declare a dynamic Array
Dim dynamicArray() As Long
'Set the size of the Array to 10 items
ReDim dynamicArray(9)
dynamicArray(2) = 5
'Resize the Array and preserve items
ReDim Preserve dynamicArray(14)
Debug.Print dynamicArray(2) 'Result: 5 - item preserved
Erasing Dynamic VBA Arrays
Dynamic VBA Arrays can contain large amounts of data
that can use a lot memory space
. You can erase (free memory from) a Dynamic VBA Array by using the
Erase
statement. It is important therefore to wipe out the Array in a proper manner. For this use the
Erase procedure
like so:
Dim arr() As Long
ReDim arr(100,100)
arr(0,0)= 0 * 0
arr(100,100) = 100 * 100
Erase arr 'Erase the Array and Free memory!
Sizing and moving through Arrays
Sizing VBA Arrays
VBA Arrays can be sized by
either specifying the size of the array
(dimension) or
by specifying the index range of the VBA Array
. I can definitely recommend the first approach as being more practical and less error prone.
Getting the size of an Array has always caused some confusion. First let’s understand the
UBound
and
LBound
functions:
LBound vs. UBound
'Declare dynamic Array
Dim dynamicArray() As Long
'Set the size of the Array
ReDim dynamicArray(1 To 10)
Debug.Print UBound(dynamicArray) 'Result: 10
Debug.Print LBound(dynamicArray) 'Result: 1
'Resize the array
ReDim Preserve dynamicArray(2 To 20)
Debug.Print UBound(dynamicArray) 'Result: 20
Debug.Print LBound(dynamicArray) 'Result: 2
From the example above we see that the
UBound function alone might not always provide correct results
.
Let’s summarize this with an example
'Declare dynamic Array
Dim dynamicArray() As Long
'Set the size of the Array
ReDim dynamicArray(1 To 10)
'Print size of the Array
Debug.Print UBound(dynamicArray)-LBound(dynamicArray) +1 'Result: 10. CORRECT!
Traversing a VBA Array
To traverse (iterate) through a VBA Array you can either use the
For
or
For Each
loops.
'Traverse and print array elements
Dim arr(10) As Long
For Each arrItem In arr
Debug.Print arrItem
Next arrItem
For example
'Traverse and print array elements
Dim arr(10) As Long, i as Long
For i = LBound(arr) to UBound(arr)
Debug.Print arr(i)
Next i
VBA Array Functions
Below a list of various VBA Functions useful when dealing with Arrays:
Basic functions
Function
Details
LBound
(Array, Rank) – return the lowest subscript (index range) of the VBA Array within a certain dimension (Rank). E.g.
'Declare static Array
Dim arr(5,2 to 3) as Long
Debug.Print LBound(arr) 'Result:0
Debug.Print LBound(arr,2) 'Result:2
UBound
(Array, Rank) – return the highest subscript (index range) of the VBA Array within a certain dimension (Rank). E.g.
'Declare static Array
Dim arr(5,2 to 3) as Long
Debug.Print UBound(arr) 'Result:5
Debug.Print UBound(arr,2) 'Result:3
ReDim
Reallocate storage space for the array (set new array size). Use the
Preserve statement
to preserve elements in the Array when resizing it. Applies to
dynamic sized arrays
only
Erase
Release array variable and deallocate the memory used. Applies to
dynamic sized arrays
only
Dim numbersArray as Variant
numbersArray = Array(1,2,3,4,5)
Debug.Print numbersArray(1) 'Result: 1
For String Arrays it might be more convenient to use the VBA Split function:
Dim stringArray as Variant
stringArray = Split("Tom;Andrew;Jack",";")
Debug.Print stringArray(1) 'Result: Andrew
Other custom VBA Array functions
Merging 2 VBA Arrays
Function Merge(ByVal arr1 As Variant, ByVal arr2 As Variant) As Variant
Dim tmpArr As Variant, upper1 As Long, upper2 As Long
Dim higherUpper As Long, i As Long, newIndex As Long
upper1 = UBound(arr1) + 1 : upper2 = UBound(arr2) + 1
higherUpper = IIf(upper1 >= upper2, upper1, upper2)
ReDim tmpArr(upper1 + upper2 - 1)
For i = 0 To higherUpper
If i < upper1 Then
tmpArr(newIndex) = arr1(i)
newIndex = newIndex + 1
End If
If i < upper2 Then
tmpArr(newIndex) = arr2(i)
newIndex = newIndex + 1
End If
Next i
Merge = tmpArr
End Function
Comparing two arrays (1 Dimensional)
Function Compare1DArrays(ByVal arr1 As Variant, ByVal arr2 As Variant) As Boolean
Dim i As Long
For i = LBound(arr1) To UBound(arr1)
If arr1(i) <> arr2(i) Then
Compare1DArrays = False
Exit Function
End If
Next i
Compare1DArrays = True
End Function
Public Sub QuickSort(vArray As Variant, lowerBound As Long, upperBound As Long)
Dim pivot As Variant
Dim tmpSwap As Variant
Dim tmpLow As Long
Dim tmpHi As Long
tmpLow = lowerBound
tmpHi = upperBound
pivot = vArray((lowerBound + upperBound) \ 2)
While (tmpLow <= tmpHi)
While (vArray(tmpLow) < pivot And tmpLow < upperBound)
tmpLow = tmpLow + 1
While (pivot < vArray(tmpHi) And tmpHi > lowerBound)
tmpHi = tmpHi - 1
If (tmpLow <= tmpHi) Then
tmpSwap = vArray(tmpLow)
vArray(tmpLow) = vArray(tmpHi)
vArray(tmpHi) = tmpSwap
tmpLow = tmpLow + 1
tmpHi = tmpHi - 1
End If
If (lowerBound < tmpHi) Then QuickSort vArray, lowerBound, tmpHi
If (tmpLow < upperBound) Then QuickSort vArray, tmpLow, upperBound
End Sub
VBA Array Limits and Errors
VBA Array Limits
What are the limits of Arrays? For one thing the maximum length of every dimension is defined by an Integer meaning that each dimension can have a maximum size of (2^31)-1 (equal to
2’147’483’647
). Although, I would assume you could sooner reach the limit of the actual memory used by the array (about
500MB
for 32-bit VBA and about
4GB
for 64-bit VBA).
Typical VBA Array errors
Typical errors listed below:
Runtime Error 9: Subscript out of range
– you are trying to access/set an item of the array outside its range e.g. if you array is of size 10 setting the item of index 11 will give you an out of range error
Other data structures
The Array has its limits and is certainly not as versatile as you may need. In some cases it is better to replace a VBA Array with a VBA Dictionary, ArrayList, Stack, Queue or other data structure objects. Read more here:
The VBA Dictionary and other data structures
F.A.Q.
Below a list of Frequent Asked Questions on Array usage:
I want to to store items in my VBA Array by Key name instead of index number. Is this possible?
No this is not possible using an Array. Use a
Dictionary Object
.
Should I use the UBound function to get the size of an VBA Array?
No! This in case of some arrays will provide a correct result HOWEVER in some cases will provide incorrect. See
this section
for more.
My VBA Array will keep expanding. But I don’t want to keep using ReDim to correct the Array size. What to do?
Use the
ArrayList object
. It allows you to add items without needing to resize it either by upsizing or downsizing your VBA Array.
Can I pass an array as an argument to a Function or Sub?
Sure. See the example below where I pass an Array by it’s reference to modify it within a separate procedure.
'Test passing an array ByRef (by reference) to a Procedure (Sub)
Sub Test()
Dim arr() As Long
ReDim arr(0) As Long
arr(0) = 1
SetArr arr
Debug.Print arr(0) 'Result: 10
End Sub
'Change first element of VBA Array to 10
Sub SetArr(ByRef arr)
arr(0) = 10
End Sub
Tom. Excel / VBA / C# enthusiast and hobbist. Collecting and sharing my knowledge and experience with beginner/advanced analysts and VBA developers. My posts are written with one thing in mind: teaching analysts how to do things properly.
