Excel VBA Array Tutorial

Excel VBA arrays are a crucial skill to master when learning VBA in Excel.

An array in VBA is a special type of variable that can store multiple values of the same data type. By using arrays, you can read and manipulate a list of data faster than if it were stored in an object such as a range or table.

In this tutorial, we will see multiple Excel VBA array examples to gain insight into how to use the different types of arrays.

Download the sample file to practice along with the tutorial.

What is an Excel VBA array?

We learned about different types of variables earlier in this VBA series, but the variables in that tutorial could only store a single value. An Excel VBA array can store a list of values of the same data type.

VBA arrays can be one-dimensional or two-dimensional, and we will see examples of both in this tutorial. Also, the number of items stored in an array can be fixed or dynamic.

Each item in an array is assigned an index. This is its position within the array and can be used to read and manipulate the value. By default, arrays start with an index value of 0. So, the first item in an array is position 0, the next is position 1, and so on.

Declare a VBA array

When you declare an array in Excel VBA, you specify its size and data type.

For example, we have the following range of data containing values for each month of the year. So, for this dataset, we know that there are 12 values.

The following code declares an array variable named MonthValues that contains 12 elements and has the Currency data type.

Dim MonthValues(12) As Currency

Remember, arrays, by default, use base 0 indexing. So although there are 12 elements in the array, its upper bound is 11. The upper bound is the last index position.

In the following code, the Option Base statement is inserted at the top of the module. This specifies to use of base 1 indexing in the module. The upper bound of the array is 12 in this instance.

Option Base 1

Sub OneDimensionalArray()

Dim MonthValues(12) As Currency

Sub

An alternative, and easier method, is to set the lower and upper bound of the array when declaring the variable using the To clause.

Dim MonthValues(1 To 12) As Currency

Using this method, the boundary size of the array is clearer to anyone using your VBA code.

You can set the lower bound to any value. For example, you could have specified 5 To 16 for the array size, but that would have been strange for this example.

You do not always know the length of an array in advance. So, for these instances, you create a dynamic array by omitting the array length when declaring.

Dim MonthValues() as Currency

You can then use the keyword Redim when you are ready to set the VBA array length.

ReDim MonthValues(1 To 12)

Using month values may seem like an odd choice to use for a dynamic array, but not all 12-month values may be available at a given time in the procedure.

Populate array with cell values

Once an Excel VBA array has been declared, you will want to populate it with cell values or constant values that you know already.

For example, let’s imagine that we want to populate an Excel VBA array of strings with the month names. The following code uses the Array function to assign the string values to the array.

Dim MonthValues() As Variant

MonthValues = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun")

It is important for this technique that the array variable is of a Variant data type. It has also been declared as a dynamic array with no size specified during its declaration.

In the following image,  we have stepped into the code (link to debugging article), and the Locals window shows the month names assigned to the array. Notice that the index starts from 0.

So, this is useful to know, but it is more likely that you will need to populate an array from cell values.

For this example, we will populate the array with the month values stored in the range B2:B13. A For Next loop will be used to assign a value to each index of the array.

Sub PopulateCellValues()

Dim MonthValues(1 To 12) As Currency

Dim i As Long

For i = 1 To 12

    MonthValues(i) = Cells(i + 1, 2).Value

Next

End Sub

If you need to change the value for a specific index position, you can reference this explicitly.

In the following example, the index position (or subscript) to change is provided by the value in cell D2. An arbitrary value of 10 is assigned to that index of the array.

Sub PopulateCellValues()

Dim MonthValues(1 To 12) As Currency

Dim i As Long

i = Range("D2").Value

MonthValues(i) = 10

End Sub

If you provide an index that does not exist, the Subscript out of Range error is shown.

Excel VBA dynamic arrays

A dynamic array in Excel VBA is an array whose size can be changed during the execution of the sub or function.

We briefly saw how to declare and re-dimension a dynamic array earlier in this tutorial. Let’s visit them in more detail now.

When looping through the elements of an array to read, change, or print its values, you need to specify the first and last element in the array. So, you may be wondering how to do this with a dynamic array.

Thankfully, there are the LBound and UBound functions in Excel VBA for this task.

In the following code, the array is populated with the values from range B2:B13, just as in a previous example.

However, the LBound and UBound functions are used in the For Next loop in place of the fixed values of 1 and 12 used previously. This is the most effective way to loop through the elements of an Excel VBA array.

Option Base 1

Sub DynamicArray()

Dim MonthValues() As Currency

Dim i As Byte

Dim s As Byte

s = Range("D2").Value

ReDim MonthValues(s)

For i = LBound(MonthValues) To UBound(MonthValues)

    MonthValues(i) = Cells(i + 1, 2).Value

Next

End Sub

In this example, the size of the array is specified by the value in cell D2. The code will work without knowing the value entered. The code would continue to operate regardless of the range ending in cell B5, B8, or B13.

You may also notice that Option Base 1 was specified to ensure that the array started with an index of 1.

If you need to change the size of the array later, the ReDim keyword can be used again to re-dimension the array.

When changing the array size again, all existing values assigned to the array are lost unless the Preserve keyword is also used.

ReDim Preserve MonthValues(24)

Excel VBA arrays and ranges

When populating an array with values from a range, you can pass all range values directly to the array without looping through its elements.

In the following code, the month values in range B2:B13 are assigned to the MonthValues array in a single statement. It is essential that the array is of a Variant data type.

Sub RangeToArray()

Dim MonthValues() As Variant

MonthValues = Range("B2:B13").Value

End Sub

The values of the array can be printed to a range in the same manner. The following statement prints the values to range C2:C13 of a sheet named “Archive”.

Sheets("Archive").Range("C2:C13") = MonthValues

Note the range was specified explicitly. A loop between the LBound and UBound elements of the array could have been deployed instead of this explicit reference.

Two-dimensional Excel VBA arrays

All the examples so far have used one-dimensional arrays. Before we finish this tutorial, let’s see an example of a two-dimensional VBA array.

In the following code, the range A2:B13 has been assigned to the array named MonthData. This has created a two-dimensional, 12-by-2 array. It is twelve rows high and two columns wide.

Sub TwoDimensional()

Dim MonthData() As Variant

MonthData = Range("A2:B13").Value

End Sub

If a one-dimensional array is a list of values, then a two-dimensional array is a list of lists.

In the following image, the Locals window shows the values stored in the array. The first two rows are expanded to show that each row index contains two values.

Excel VBA arrays can handle up to 60 dimensions, so they can store much larger datasets than are demonstrated here.

To retrieve data from a specific position in the two-dimensional array, the row, and column index would need to be given. For example, the following two statements print the values from both columns of row 1 into cells F2 and G2, respectively.

Range("F2").Value = MonthData(1, 1)

Range("G2").Value = MonthData(1, 2)

If we wanted to manipulate each value in the array, or a specific dimension of the array, a For Next loop could be used.

In this example, a For Next loop is used to multiply each element in the second column of the array by 20%.

For i = 1 To UBound(MonthData)

    MonthData(i, 2) = MonthData(i, 2) * 0.2

Next

In this example, the two-dimensional Excel VBA array was created by assigning a range to it, a cool technique. However, it is important to know how to specify the size of the array when you declare it.

In the following code, the row and column dimensions' lower and upper bounds are given when declaring the array.

Dim MonthData(1 To 12, 1 To 2) As Variant

To populate the array from cell values using a loop, a nested For Next loop is required to loop through each list within a list (or columns for each row).

Sub TwoDimensional()

Dim MonthData(1 To 12, 1 To 2) As Variant

Dim r As Byte, c As Byte

For r = 1 To 12

    For c = 1 To 2

        MonthData(r, c) = Cells(r + 1, c)

    Next c

Next r

End Sub

The counter variables are named r for the row index and c for the column index, respectively, to make them easy to identify.

The loop for the two columns of the array is nested within the outer loop for the rows of the array.

Once you are familiar with looping structures such as this, they can be easily adapted for other arrays. The variable aspect of this framework is the use of the Cells object to retrieve data from the sheet.

Learn Excel VBA today?

Learning how to use Excel VBA array variables is very important if you want to master Excel VBA. Yet, there is so much more to learn.

Enroll in our Excel macros and VBA online course to fast-track your VBA skills. It contains 41 practical lessons to master all the Excel VBA fundamentals.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial