VBA Multi/Two-Dimensional Arrays with 4 Examples

How to create two or more dimensional arrays in VBA?

A two-dimensional array in VBA can be created as follows:

Dim arr_2D(1 To 6, 1 To 3) As String

In the above statement, an array of eighteen elements of string type is created in VBA.

You may also create a 2D array like this:

Dim arr_2D(3 To 7, 2 To 4) As String

That is, the array index of the first dimension starts at 3 and ends at 7.

Similarly, the second dimension of the array starts at 2 and ends at 4.

An example of a two-dimensional array

In this example, we will create a string type 2-D array.

Then we will assign the values to the elements.

Finally, we will display an array element in the message box.

VBA Code:

Sub Multi_arr_ex()

Dim arr_2D(1 To 3, 1 To 2) As String

'Assign values to all elements in the 2-d array
arr_2D(1, 1) = "Java"
arr_2D(1, 2) = "C#"
arr_2D(2, 1) = "Visual Basic"
arr_2D(2, 2) = "Python"
arr_2D(3, 1) = "C"
arr_2D(3, 2) = "C ++"

'Display an array element

MsgBox "Element at (2,2) = " & arr_2D(2, 2)


End Sub

Output:

Display all array elements using For loop

We will use a For..Next loop to iterate through all elements in the above 2-D array:

VBA code

Sub Multi_arr_ex()
Dim arr_2D(1 To 3, 1 To 2) As String


'Assign values to all elements in the 2-d array

arr_2D(1, 1) = "Java"

arr_2D(1, 2) = "C#"

arr_2D(2, 1) = "Visual Basic"

arr_2D(2, 2) = "Python"

arr_2D(3, 1) = "C"

arr_2D(3, 2) = "C ++"


For x = 1 To 3

    For y = 1 To 2

        Arr_val = Arr_val & arr_2D(x, y) & vbNewLine

    Next y

Next x

MsgBox Arr_val


End Sub

Result:

This is how it worked:

We used two For loops (Outer and inner loop)
In the outer loop, the first dimension of the array iterated
For each item of the first dimension, all elements of the second dimension also iterated
We collected the array element values in the String type variable with concatenation and line breaks.
Finally, we displayed the array elements in a message box

Getting the length of a two-dimensional array example

The example below gets the array length of a 2D array and is displayed in the message box.

We used UBound and LBound functions as shown in the example below:

Sub Arrays_ex()

'Creating a 2D array

Dim arr_products(1 To 6, 1 To 3) As String
Dim i As Integer, j As Integer, k As Integer


'Using UBound and multiply

i = UBound(arr_products, 1)

j = UBound(arr_products, 2)

k = i * j

MsgBox "2-D Array has " & k & " elements."

End Sub

Output:

Learn more about array lengths in VBA

Understand 2D array with Excel Sheet Data

In this example, we will use the following sheet to explain how a two-dimensional array works with Excel sheets.

For that we have the following sample data:

We will create a 2D array and assign cell values to its elements.

Then we will iterate through array elements by For loop.

Finally, we will display the array elements with the cell values:

Sub Arrays_ex()

'Creating a 2D array

Dim arr_products(1 To 8, 1 To 4) As String

Dim x As Integer, y As Integer

'Assigning cell values as array element values

For x = 1 To 8

    For y = 1 To 4

        arr_products(x, y) = Cells(x, y).Value

    Next y

Next x

'Displaying whole array in Message Box

For i = 1 To UBound(arr_products)

   Str_Prods = Str_Prods & arr_products(i, 1) & "            " & arr_products(i, 2) & "    " & arr_products(i, 3) & "        " & arr_products(i, 4) & vbNewLine

Next i

MsgBox Str_Prods

End Sub

Result: