Access Excel VBA Array

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

1. One-dimensional Array
2. 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.

Syntax

Dim ArrayName(size) As [DataType]

OR

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.

Example

Suppose we want to create a name list in which we want to store 6 names.

Dim nameList(5) as String

OR

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)

Example

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"

Erase Array

When you no longer need the array, use Erase keyword to delete the array

Erase nameList()

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

change cell range to array

Excel VBA select multiple worksheets

Excel VBA separate line break data into different row