This VBA tutorial explains how to create and use VBA Array in Access and Excel VBA.
You may also want to read:
Store unique array item with Scripting Dictionary
Access Excel VBA Array
In Excel worksheet, you can convert a formula arguments to Array by pressing CTRL+SHIFT+ENTER, click here to see example.
In VBA, Array is a variable that can store multiple data of the same data type.
There are two types of Array in VBA
- One-dimensional Array
- Two-dimensional Array
For most of the time we use One-dimension Array, therefore this tutorial will only talk about One-dimensional Array.
If you want to store non-duplicated items, use Scripting Dictionary instead.
Declare VBA Array
Before we can use Array variable, we have to declare its type as Array, regardless whether we use Option Explicit keyword. Array variable name has brackets () in the suffix.
Declare Static Array
If we know exactly the size of the Array we need to use and you are not going to change the size, then declare Static Array.
Dim ArrayName(size) As [DataType]
Dim ArrayName(from_index to_index) As [DataType]
Data Type is optional. If you don’t define the data type, it will default as Variant, which is in fact more flexible yet occupies more memory.
By default, Array index starts from 0. For example, if we declare ArrayName(5), it creates 6 Array memory from 0 to 5.
Suppose we want to create a name list in which we want to store 6 names.
Dim nameList(5) as String
Dim nameList(0 to 5) As String
Store items in VBA Array
To store items, specify which array index you want to store in
Dim nameList(2) as String nameList(0)= "Peter" nameList(1)= "Apple" nameList(2)= "Cat"
Alternatively, you can declare all items in one row
nameList = Array("Peter","Apple","Cat")
In this declaration, we can skip the Dim statement because we have specified the Array keyword.
Declare Dynamic Array
It is not convenient to create static Array because most of the time, we have no idea how many items we need to store, and it is a waste of memory to create a larger Array than we actually use. The purpose of dynamic Array is to resize the Array memory every time we insert a new item.
To declare a dynamic Array, use Redim keyword to declare the size the Array
Dim DynamicArr() ReDim DynamiccArr(2)
However, each time we use Redim keyword, all the existing items we previously stored in the Array are gone, we need to use Preserve Keyword to keep the existing items.
ReDim Peserve DynamiccArr(2)
Suppose we initially want to store only one name in nameList
Dim nameList() ReDim nameList(0) nameList(0)= "Peter"
In the above declaration, we can use Redim Preserve instead of Redim to standardize the syntax.
Suppose later on we decide to add two more names to the list
Dim nameList() ReDim nameList(0) nameList(0) = "Peter" ReDim Preserve nameList(2) nameList(1) = "Apple" nameList(2) = "Cat"
When you no longer need the array, use Erase keyword to delete the array
After erase, you need to re-declare the array if you need to use it again.
Retrieve VBA Array items
We can retrieve all Array items using For Loop
Dim nameList() ReDim Preserve nameList(2) nameList(0) = "Peter" nameList(1) = "Apple" nameList(2) = "Cat" For i = 0 To 2 MsgBox (nameList(i)) Next i
If we don’t know the beginning and ending of i, we can use LBound (lower bound) and UBound (upper bound) Function to get the lowest and highest index of the Array.
For i = LBound(nameList()) To UBound(nameList()) MsgBox (nameList(i)) Next i
There is another even easier way to retrieve array items, without using LBound and UBound
For Each i In nameList MsgBox (i) Next i
Get Array Index from Array Items
Suppose you have an Array Item value but you don’t know the Index, then you can use the below Function
Public Function getArrayIndex(arrayList As Variant, arrayItem) For i = LBound(arrayList) To UBound(arrayList) If arrayList(i) = arrayItem Then getArrayIndex = i Exit Function End If Next i End Function
Note that if Array contains two identical values, only the smallest index can be returned.
More VBA Array Examples
In some of my older posts, I have some practical examples that make use of Array, click the below link to see the details
Excel VBA select multiple worksheets
Excel VBA separate line break data into different row