To create a multiple dimensional array, you need to define the dimensions while declaring the array. Well, you can define as many as dimensions that you need (VBA allows 60 dimensions) but you will probably not need to use more than 2 or 3 dimensions of any of the arrays. Using a two-dimensional array is like having rows and columns.
In this tutorial, we will look at how to create a 2-D and 3-D array.
Create a Multi-Dimensional Array in VBA
- Use the Dim statement to declare the array with the name that you want to give.
- After that, enter a starting parenthesis and define the element count for the first dimension.
- Next, type a comma and enter a count of elements that you want to have in the second dimension, and close the parentheses.
- In the end, define the data type for the array as a variant or any data type you want.
Here’s the code.
Sub vba_multi_dimensional_array() Dim myArray(5, 2) As Variant myArray(1, 1) = 1 myArray(2, 1) = 2 myArray(3, 1) = 3 myArray(4, 1) = 4 myArray(5, 1) = 5 myArray(1, 2) = 6 myArray(2, 2) = 7 myArray(3, 2) = 8 myArray(4, 2) = 9 myArray(5, 2) = 10 End Sub
The above code for the array creates an array with 5 rows and 2 column and will look something like this.
More on VBA Arrays
VBA Add New Value to the Array | VBA Clear Array | VBA Loop Through an Array | VBA Range to an Array | VBA Search for a Value in an Array | VBA Sort Array | VBA Array Length (Size) | VBA Array with Strings | VBA Dynamic Array | ISARRAY Function | ARRAY Function | VBA Arrays
If you are preparing for an interview, make sure to check out these VBA interview questions.