Multidimensional arrays in VBA store data of the same type in multiple dimensions, like rows and columns in a table. They are ideal for representing Excel ranges, matrices, or datasets (e.g., sales by region and month). While VBA supports up to 60 dimensions, 2D or 3D arrays are most common.
1. 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"
2. Dynamic 2D Arrays
Dynamic arrays are declared without a size and resized later using ReDim. With ReDim Preserve, only the last dimension (columns) can be resized without losing data.
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
3. Using the Array Function
The Array function creates a 1D Variant array with mixed data types. For multidimensional arrays, assign Variant arrays manually or use ReDim.
4. 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
5. Resizing with ReDim Preserve
ReDim Preserve resizes a dynamic array while retaining existing data, but only the last dimension (columns) can be resized.
The following code is written to declare and assign a 2D array after that to change the dimension of the array.
