Multidimensional Arrays in Excel VBA

Multidimensional Arrays are used to store data of similar data types of more than one dimension. A multidimensional array has a dimension up to 60 but usually, we don’t use arrays of dimensions more than 3 or 4. Here, we will see how to declare a multidimensional array in many ways and also how to create and re-size the multidimensional array

Declare a 2D Array

To declare a 2D array we have to use “Dim” then the name of the variable where it will store the 2D array and the dimension of the 2D array will be separated by commas. In the following code, a 2D array is declared as a “4 x 5” 2D array in a single line which is also known as a fixed array whose dimension can’t be changed later

This same array can also be declared explicitly using “To”

Dynamic Array

It is used to declare an array in two lines and we can also change the dimension of the array. Only the upper bound of the last dimension i.e column can be changed

Note: By default, the lower bound of an array is 0, and the upper bound of an array is n. Where,

• Lower bound is the lowest index of the array.
• Upper bound is the highest index of the array.  

In the following code, an array variable is declared in one line using “Dim” and in another line using “ReDim” we can declare the dimension of the array

We can also “ReDim”. We can also change the dimension of the array. In the following code, we have changed the upper bound dimension of the column from 6 to 8

Array Function

Using Array function we can also declare the multidimensional array which gives us the benefit of declaring an array of variant data type

Creating and Displaying a 2D Array

In the following code, we will declare a 2D then we will assign values to it, and then we will display it using a nested for loop.

Output

Resizing the Array using ReDim Preserve

The following code is written to declare and assign a 2D array after that to change the dimension of the array.