VBA Multi-Dimensional Array


- Written by Puneet

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

  1. Use the Dim statement to declare the array with the name that you want to give.
  2. After that, enter a starting parenthesis and define the element count for the first dimension.
  3. Next, type a comma and enter a count of elements that you want to have in the second dimension, and close the parentheses.
  4. 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 for the array creates an array with 5 rows and 2 columns and will look something like this.

Last Updated: June 22, 2023