Creating and Using Multi-Dimensional Arrays in VBA

Building upon the one-dimensional array tutorial, multi-dimensional arrays provide an opportunity to easily create or transfer large tables of data. In many instances, thousands of data points can be created with a few simple lines of code.

Note: multi-dimensional arrays can be inefficient and difficult to manage for complex sets of data. If you are working with a large set of data that requires frequent updates or additions, I suggest learning about Collection classes.


This post within the Getting Started with VBA series continues the focus on Arrays in VBA. I suggest you read the tutorial on one-dimensional arrays before proceeding.

A one-dimensional array interacting with a spreadsheet—in its basic form—can either collect or print data either down a column or across a row. What if a need arises to collect or print both down and across, such as populating a data table? Multi-dimensional arrays might be your most important tool.

While a one-dimensional array will contain one index per element, e.g. myArray(1), a multi-dimensional array will include two or more indexes per element, e.g. myArray(1, 2). The first index represents the first dimension of an array; the second index represents the second dimension of the array. Multi-dimensional arrays are often referred to by the number of dimensions they have. An array with two dimensions: a 2D array; an array with three dimensions: a 3D array. There is no limit to the number of dimensions an array can have, although the number of dimensions should be kept to a minimum for the sake of clarity.

An effective way to think about a 2D array is to look at a table of data on a spreadsheet. We can use a 2D array to output the numbers 0 through 99, with 10 numbers on each row. The 1st dimension (left index) represents rows, while the 2nd dimension (right index) represents columns.

An example of a 2D array visualized on a spreadsheet.
An example of a 2D array visualized on a spreadsheet. Notice that the index values—when joined together—coincidentally correspond with the array’s numeric values. While this is purposefully staged in this example, it’s a good way to visualize how a 2D arrays operate.

Tutorial File

The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening.

Static Multi-Dimensional Arrays

Similar to one-dimensional arrays, multi-dimensional arrays can be both static and dynamic. A multi-dimensional array is considered static when there’s no intention to resize the array at any point after it’s initial declaration. Each dimension must have its lower bound and upper bound specified within the declaration statement. Multiple dimensions must be separated by a coma.

The following is an example of a declaration and value assignment for the 2D array myIntegerArray.

Arrays should be assigned a data type consistent with all values within the entire array. Given that the examples in this post will be limited to integer or floating point numbers, the arrays will be assigned a data type of Integer or Double. In situations where multiple data types will be stored in an array, use the Variant data type. Arrays can also be assigned String and Boolean data types.

Creating and Printing Static Multi-Dimensional Arrays

Similar to their one-dimensional counterparts, there are two primary methods of creating and printing multi-dimensional arrays: explicit creation or creation with a For Loop.


It is possible to explicitly build and print each value within multi-dimensional arrays. The following subroutine prints all 15 values of the myIntegerArray array.

The output is as follows:

Multi-dimensional arrays can be explicitly printed on spreadsheets.
Multi-dimensional arrays can be explicitly printed on spreadsheets.

Given how tedious this approach can be, it’s important to look toward more efficient ways of building and printing multidimensional arrays. Explicit creation and printing can work well for small 2D arrays, or for extracting precise array values when their keys are known. The following methods use loops and other techniques to more effectively manage large arrays.

For Loop

The most practical way of creating and printing an array is by using a For Loop. This requires nested loops — when creating an array, each dimension must have its own nested loop.

For Loop Creates and Prints an Entire 2D Array

A 2D array can be created within a For loop nested inside another For loop, permitting the iteration through each possible key combination. In the example below, the myIntegerArray is declared with 1st dimension bounds of 0 to 9, and 2nd dimension bounds of 0 to 9. In all, the For loop will force 100 iterations to assign numbers 0 through 99 to the array indices.

Note that the second set of loops which print the array is almost identical to the first set of loops which create the array — the internal printing vs. assigning is the only difference. Given that the 2D array contains 100 values, and two sets of loops iterate for creation and printing, over 200 lines of code would have been required to explicitly create and print the array. Loops were effectively used to create and print this array with only 28 lines of code. The output is as follows:

Output from a simple 2D array.
Output from a simple 2D array.
For Loop Creates and Prints an Entire 3D Array

Complex procedures may require arrays which contain more than two dimensions. In these situations, it helps to think about arrays in a hierarchical structure. If we were to add decimal values to the previous counter example, the array can be broken down into the following structure.

  • Increments of 10.0 for the 1st dimension
    • Increments of 1.0 for the 2nd dimension
      • Increments of 0.1 for the 3rd dimension

Each dimension has a specific role in expanding the number table. In the next example, the output looks like the earlier examples, but a data validation list will appear when clicking on any cells within the output table, providing a list of decimal values (tenths) for each number. To summarize, the 3rd dimension is a “container” for the 2nd dimension.

While there is a lot of “noise” in the 2nd and 3rd loop levels to add the data validation list, this script script is effectively three levels of nested loops, one for each dimension.

This subroutine may seem intimidating at first, but explicit creation and printing each array value would take over 2000 lines of code — 47 lines is much more efficient. The output—along with one of the drop-downs toggled—appears as follows:

Output from a 3-dimensional array, creating dropdown lists to show decimal values.
Output from a 3-dimensional array, creating dropdown lists to show decimal values.

Dynamic Multi-Dimensional Arrays

As explained in detail in the previous entry, dynamic arrays are arrays which can be resized after their initial declaration.

Resizing All Dimensions of an Array

Given that variables cannot exist in declaration statements, a ReDim statement can be used to resize an array after its declaration. The drawback of the ReDim statement is that it will remove any existing values/elements within the array. With that, this approach only works if an array is resized prior to adding values.

This approach is effective if the size of the array relies on user-inputs or other elements on a spreadsheet (such as number of rows or columns with data). To illustrate this, imagine a procedure which begins by declaring an array, then requests some input value from the user which will determine its size. Now that the array has been fully resized, the subroutine can continue by adding data to the array.

The following subroutine is modeled after the previous examples, but invokes a user input form, allowing the user to specify the size of the 2D array’s dimensions and count total.

Upon running the subroutine, two input boxes will appear asking the user to input the height and width of the array. The remainder of the script will create the array using a nested For loop structure based on user input. The procedure finishes by a printing the array.

One important difference in this script relates to the second For loop structure which prints the array. At first glance, it may seem like the array’s upper-bounds are determined by a simple UBound() function. However, unlike a UBound() function for a one-dimensional array, the UBound() function for multi-dimensional arrays requires that the array dimension be specified as a second argument.

Resizing a Multi-Dimensional Array During Creation

Similar to its one-dimensional counterparts, the ReDim Preserve statement can be used on a multi-dimensional array. However, it comes with a caveat — only the last dimension of the array can be changed. The following subroutine provides identical output to the previous example while using the ReDim Preserve statement.

There are a few things to note about the use of the ReDim Preserve statement. Given that dynamic multi-dimensional arrays have a tenancy to rely on vertical increases/decreases in size (and the 1st dimension impacts its vertical size), the array will typically need to be transposed during its creation, expanding to the right. The Application.Transpose function can be used to accomplish this. To get the proper vertical effect, the nested loops used to print the array must be inverted, permitting the array to be re-transposed into its correct form.

Overall, I believe that ReDim Preserve makes procedures more complicated than they need to be. Here are a few reasons why ReDim Preserve should be avoided:

  1. The transposing and re-transposing actions make the code confusing and un-intuitive.
  2. In most situations, there should be a means of determining each dimension’s size prior to creating the array. Note that the previous example accomplished the exact same task as this example, but with more clarity and fewer lines of code.
  3. While it wouldn’t be noticeable on most modern computers, the ReDim Preserve statement does require more processing time than a basic ReDim statement.

Using a Spreadsheet Range to Create a Multi-Dimensional Array

This tutorial wraps up with the easiest method of creating and printing multi-dimensional arrays. 2D arrays can be created instantly from Excel ranges, and a range of the same size can instantly be assigned all values of a 2D array. The following script invokes the subroutine from this post’s first example, then creates an array from the numbers within the range A1:E6, then prints that array below the number table in range A12:E17.

Output is as follows:

Below the main table, output from an array generated and printed through the Range object.
Below the main table, output from an array generated and printed through the Range object.

Given the structure of spreadsheets, this method of creating an array is limited to two-dimensions. While arrays generated from ranges are considered dynamic given that the array is not tied to a size at declaration, it can be somewhat challenging to let user inputs modify the creation or print size. One way to mitigate this limitation is to use named ranges.

Other Notes

Leave a Reply