Multidimensional Arrays in VBA – Excel

In the previous article, we learned about implementing 1 Dimensional Arrays. Today we will learn about the usage of Multidimensional Arrays in VBA with the help of examples.

So let us start learning.

What are Multidimensional Arrays?

Multidimensional Arrays are used to store data of similar type in more than one dimension. A Multidimensional Array can have up to 60 dimensions but most of the time we do not need more than 3 or 4 dimensions.

This is an example of two dimensional integer array whose dimensions are 5 x 5.

The total number of elements in an array is equal to the product of the dimensions. For Instance, if we have a three dimensional array of dimensions 3 x 4 x 1 then the total number of elements will be 12. We can store 12 values in each block of element having the specified data type of that of the array.

Syntax to Declare Multidimensional Array

The following rules must be followed when declaring a Multidimensional Array in VBA.

• While declaring a multidimensional array, we would separate different dimensions of the array using commas.
• Each dimension would have a lower bound and an upper bound for indexes of the elements.
• It is optional to specify the lower bound of a dimension as the default value of the lower bound of an array is always zero (index for the first element).
• There can be a maximum of 30 dimensions in one Multidimensional Array.

The syntax to declare a Multidimensional Array is as follows.

Dim <arr_name>([<lower1> To ]<upper1>,[<lower2> To ]<upper2>,[<lower3> To ]<upper3>) As Data_Type

Here the lower and upper represents the lower and upper bound of a specific dimension. The part in the square bracket is optional.

The following can be an example of declaring a two dimensional Array.

Dim twoDimensionalArray(1 to 5, 1 to 5) As Integer

The elements of this array would have the following indexes as follows.

Using Multidimensional Arrays in VBA

In this section of the blog, we will learn some examples to implement multidimensional arrays including the declaration and initialization. Assigning values to the elements of an array is known as initialization.

Example 1 – Declare and Initialize a Two Dimensional String Array

In this example, we would declare a two dimensional string array and then print the elements. Both of the operations to initialize the array and then print the elements would be done by looping through each dimension of the array.

Run the following Sub Procedure to see the working.

Sub exInsertDataIn2DArray()
'declare the String Array of size 3 x 3
Dim arr(1 To 3, 1 To 3) As String
'loop through first dimension of array
For i = 1 To 3
    'loop through second dimension of array
    For j = 1 To 3
        'assigning value to array elements
        arr(i, j) = "This is element (" & i & " , " & j & ")"
    Next j
Next i
'again looping through first dimension of array
For i = 1 To 3
    'again looping through second dimension of array
    For j = 1 To 3
        'print the elements of array we have stored
        MsgBox arr(i, j)
    Next j
Next i
End Sub

• Press Alt and F8 to open the list of available macros and select this macro exInsertDataIn2DArray to run.

Example 2 – 3D array Using the Ubound

Each dimension of array has a lower bound and an upper bound for the indexing of its elements. We can use the Ubound function to get the Upper Bound of a specific dimension of an array.

UBound(<array_name>,<dimension_number>)

For instance, look at this example.

Sub Example()
Dim arr(1 To 10, 2 To 20) As Integer
MsgBox "Upper Bound of first dimension: " & UBound(arr, 1) & ", Upper Bound of second dimension: " & UBound(arr, 2)
End Sub

Run this Sub Procedure, and it will print the upper bound of the first dimension and second dimension of the array named arr.

Now, we would use a 3D array to print the values in the first three rows and the first three columns of two worksheets. This is what we mean.

We would like to print the contents in the first three rows and three columns in the Sheet1 and Sheet2.

Use the following code to implement. The 3D array has one dimension for row, one for column and one for the sheets.

Sub Ex3Darray()

'declare array, number of rows=2, number of columns=2 and number of sheets=2
Dim arr(2, 2, 2)
Dim iRow As Integer, iCol As Integer, iSht As Integer
'Loop through sheets, 1 to 2
For iSht = 1 To UBound(arr, 3)
    'activate the sheet
    Worksheets("Sheet" & iSht).Activate
    'Loop through columns, 0 to 2
    For iCol = 0 To UBound(arr, 2)
    'Loop through rows, 0 to 3
        For iRow = 0 To UBound(arr, 1)
            arr(iRow, iCol, iSht) = ThisWorkbook.Sheets("Sheet" & iSht).Cells(iRow + 1, iCol + 1)
            'Check output in immediate window, press Ctrl G
            MsgBox arr(iRow, iCol, iSht)
        Next
    Next
Next

End Sub

When you run the code, it prints the contents of three dimensional array named arr.

This brings us to an end.

Thank you for reading.