Declare a two-dimensional array with both sets of bounds, rows first: Dim sales(1 To 12, 1 To 3) As Double holds 12 rows and 3 columns. Refer to an element by row and column, as in sales(4, 2) = 1500 for row 4, column 2.
Give both the lower and upper bound. Dim sales(12, 3) creates 13 rows and 4 columns, numbered from 0, unless the module has Option Base 1.
Loop through it with one loop inside another, using LBound and UBound with the dimension number:
Dim r As Long, c As Long
For r = LBound(sales, 1) To UBound(sales, 1)
For c = LBound(sales, 2) To UBound(sales, 2)
sales(r, c) = r * c
Next c
Next r
The quickest way to fill one is from a range: arr = Range("A1:C12").Value gives a 12-by-3 array starting at 1, and Range("E1:G12").Value = arr writes it back in one step. A third dimension works the same way, as in Dim cube(1 To 12, 1 To 3, 1 To 2), but it can’t be written to a sheet directly.
To create a multiple-dimensional array, you need to define the dimensions while declaring the array. Well, you can define as many dimensions as you need (VBA allows 60 dimensions), but you will probably not need to use more than 2 or 3 dimensions of any of the arrays. Using a two-dimensional array is like having rows and columns.
In this tutorial, we will look at how to create a 2-D and 3-D array.
Create a Multi-Dimensional Array in VBA
- Use the Dim statement to declare the array with the name that you want to give.

- After that, enter a starting parenthesis and define the element count for the first dimension (the last index number, as the array starts from 0).

- Next, type a comma and enter a count of elements that you want to have in the second dimension, and close the parentheses.

- In the end, define the data type for the array as a variant or any data type you want.

Here’s the code.
Sub vba_multi_dimensional_array() Dim myArray(5, 2) As Variant myArray(1, 1) = 1 myArray(2, 1) = 2 myArray(3, 1) = 3 myArray(4, 1) = 4 myArray(5, 1) = 5 myArray(1, 2) = 6 myArray(2, 2) = 7 myArray(3, 2) = 8 myArray(4, 2) = 9 myArray(5, 2) = 10 End Sub
The above code fills an array with values in 5 rows and 2 columns, and it will look something like this.

But there’s one thing to know here. In VBA, an array starts from 0 by default, so Dim myArray(5, 2) actually has 6 rows (0 to 5) and 3 columns (0 to 2). The code above only uses rows 1 to 5 and columns 1 to 2, which is why it looks like the table above. To get an array with exactly 5 rows and 2 columns, define the start and end of each dimension:
Dim myArray(1 To 5, 1 To 2) As Variant
You can also add Option Base 1 at the top of the module to make all the arrays in it start from 1.
Loop Through a Two-Dimensional Array
To go through all the elements of a two-dimensional array, you need two loops, one inside the other: the first one for the rows and the second one for the columns. UBound and LBound take a second argument for the dimension number, so UBound(myArray, 2) gives you the last index of the second dimension.
Sub vba_loop_two_dimensional_array()
Dim myArray(1 To 5, 1 To 2) As Variant
Dim r As Long, c As Long
For r = LBound(myArray, 1) To UBound(myArray, 1)
For c = LBound(myArray, 2) To UBound(myArray, 2)
myArray(r, c) = r * c
Debug.Print r, c, myArray(r, c)
Next c
Next r
End Sub
Create a Three-Dimensional Array
A three-dimensional array is like having more than one table with the same rows and columns, just like the sheets in a workbook. To create one, add a third dimension in the Dim statement. In the following code, the first dimension is for 3 months, the second one for 4 regions, and the third one for 2 values (sales and profit).
Sub vba_three_dimensional_array()
Dim myArray(1 To 3, 1 To 4, 1 To 2) As Variant
myArray(1, 1, 1) = 1500
myArray(1, 1, 2) = 300
myArray(3, 4, 2) = 450
Debug.Print myArray(3, 4, 2)
End Sub
To get a value from this array, you need to specify all three index numbers, like myArray(3, 4, 2) for the profit of the fourth region in the third month.
Store a Range in a Two-Dimensional Array
The quickest way to fill a two-dimensional array is to take the values from a range. When you assign a range with more than one cell to a Variant variable, VBA creates a two-dimensional array that starts from 1, with the rows in the first dimension and the columns in the second.
Sub vba_range_to_2d_array()
Dim myArray As Variant
myArray = Range("A1:B5").Value
Debug.Print UBound(myArray, 1), UBound(myArray, 2)
Range("D1").Resize(UBound(myArray, 1), UBound(myArray, 2)).Value = myArray
End Sub
This code prints 5 and 2 (5 rows and 2 columns), and the last line writes the whole array back to the sheet, starting from cell D1. You can learn more about it in Range to an Array.
Dynamic Multi-Dimensional Array
If you don’t know the size of the array while writing the code, declare it without dimensions and use ReDim later. And to keep the values when you resize it, use ReDim Preserve, but with Preserve, you can only change the last dimension.
Sub vba_dynamic_two_dimensional_array()
Dim myArray() As Variant
ReDim myArray(1 To 5, 1 To 2)
myArray(1, 1) = 1
ReDim Preserve myArray(1 To 5, 1 To 3)
myArray(1, 3) = 11
End Sub
If you try to change the first dimension with Preserve, like ReDim Preserve myArray(1 To 6, 1 To 3), VBA shows the Subscript Out of Range error. Read more in VBA Dynamic Array.
Frequently Asked Questions
How many dimensions can a VBA array have?
Up to 60. But in real life, you will rarely need more than two or three.
How do I get the number of rows and columns in a 2D array?
Use UBound(myArray, 1) – LBound(myArray, 1) + 1 for the rows and UBound(myArray, 2) – LBound(myArray, 2) + 1 for the columns. More on this in VBA Array Size.
Can I add a new row to a 2D array with ReDim Preserve?
Not directly, as Preserve only resizes the last dimension. Either keep the rows in the last dimension (columns as the first), or create a new, bigger array and copy the values into it with a loop.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.