Working with Arrays for VBA Programming in Excel 2016

By John Walkenbach

Most programming languages support arrays, including VBA. An array is a group of variables that share a name. You refer to a specific variable in the array by using the array name and an index number in parentheses. For example, you can define an array of 12 string variables to hold the names of the months of the year. If you name the array MonthNames, you can refer to the first element of the array as MonthNames (1), the second element as MonthNames (2), and so on.

Declaring arrays

Before you can use an array, you must declare it. No exceptions. Unlike with normal variables, VBA is very strict about this rule. You declare an array with a Dim or Public statement, just as you declare a regular variable. However, you also need to specify the number of elements in the array. You do this by specifying the first index number, the keyword To, and the last index number — all inside parentheses. The following example shows how to declare an array of 100 integers:

Dim MyArray(1 To 100) As Integer

When you declare an array, you can choose to specify only the upper index. If you omit the lower index, VBA assumes that it’s 0. Therefore, both of the following statements declare the same 101-element array:

Dim MyArray (0 To 100) As Integer
Dim MyArray (100) As Integer

If you want VBA to assume that 1 (rather than 0) is the lower index for your arrays, include the following statement in the Declarations section at the top of your module:

Option Base 1

This statement forces VBA to use 1 as the first index number for arrays that declare only the upper index. If this statement is present, the following statements are identical, both declaring a 100-element array:

Dim MyArray (1 To 100) As Integer
Dim MyArray (100) As Integer

Multidimensional arrays

The arrays created in the previous examples are all one-dimensional arrays. Think of one-dimensional arrays as a single line of values. Arrays you create in VBA can have as many as 60 dimensions — although you rarely need more than two or three dimensions in an array. The following example declares an 81-integer array with two dimensions:

Dim MyArray (1 To 9, 1 To 9) As Integer

You can think of this array as occupying a 9 x 9 matrix — perfect for storing all numbers in a Sudoku puzzle.

To refer to a specific element in this array, you need to specify two index numbers (similar to its “row” and its “column” in the matrix). The following example shows how you can assign a value to an element in this array:

MyArray (3, 4)= 125

This statement assigns a value to a single element in the array. If you’re thinking of the array in terms of a 9 x 9 matrix, this assigns 125 to the element located in the third row and fourth column of the matrix.

Here’s how to declare a three-dimensional array, with 1,000 elements:

Dim My3DArray (1 To 10, 1 To 10, 1 To 10) As Integer

You can think of a three-dimensional array as a cube. Visualizing an array of more than three dimensions is more difficult.

Dynamic arrays

You can also create dynamic arrays. A dynamic array doesn’t have a preset number of elements. Declare a dynamic array with an empty set of parentheses:

Dim MyArray () As Integer

Before you can use this array, you must use the ReDim statement to tell VBA how many elements the array has. Usually, the number of elements in the array is determined while your code is running. You can use the ReDim statement any number of times, changing the array’s size as often as needed. The following example demonstrates how to change the number of elements in a dynamic array. It assumes that the NumElements variable contains a value, which your code calculated.

ReDim MyArray (1 To NumElements)

When you redimension an array by using ReDim, you wipe out any values currently stored in the array elements. You can avoid destroying the old values by using the Preserve keyword. The following example shows how you can preserve an array’s values when you redimension the array:

ReDim Preserve MyArray (1 To NumElements)

If MyArray currently has ten elements, and you execute the preceding statement with NumElements equaling 12, the first ten elements remain intact, and the array has room for two additional elements (up to the number contained in the variable NumElements). If NumElements equals 7 however, the first seven elements are retained but the remaining three elements meet their demise.