VBA Multi/Two-Dimensional Arrays

How to create two or more dimensional arrays in VBA?

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

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:

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 VBA 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:

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: