Excel VBA Multidimensional Arrays 

If you’re thinking about storing multiple data against a common entry, a multidimensional array can serve the need. Multidimensional Arrays are arrays with more than one dimension. We use multidimensional arrays in Excel VBA to store data with multiple layers. For example, if we want to store the employee details of a company like Employee ID, Employee Name, and Department, we can do this using multidimensional arrays. Later, we may easily access the data using the Employee ID as the key to access it. To get a glimpse of how it we can store data in a multidimensional array and use it later, you may watch this short video:

Download Practice Workbook

You may download the Practice Workbook and practice yourself.

Overview of Multidimensional Arrays

A multidimensional array is an array with more than one dimension or index. It allows you to store and manipulate data in multiple dimensions. Precisely speaking, any array greater than one dimension is multidimensional.

We may essentially think about a table to understand what a multidimensional array looks like. Like a table, multidimensional arrays can be thought of as a set of data with multiple horizontal rows and vertical columns. You need to use the index of each dimension properly to access that specific data from the array later.

In a different way, you may relate a multidimensional array with specifying the position of an object in a space using a coordinate system. If you think about an ant moving on a thin thread, you just need a single coordinate value (let’s say an x-axis value) to specify the position of the ant.

Now think about the ant but this time, it is running randomly on a piece of paper. So, if we want to specify its position on the paper at a specific time, you need to use two coordinate values that are the x and y coordinate values (one along the length of the paper and another along the width of the paper) to do so.

Lastly, if you want to specify the position of a butterfly flying in your room, you need to use three coordinate values that are the x, y, and z coordinate values (along the length, width, and height) to do. Similarly, you need to specify the index numbers of a multidimensional array element to access the value of that element.

How to Declare an Array in Excel VBA

To begin with an array, you need to declare an array first. You need to use a Dim statement to declare an array in Excel VBA. We already know that we use Dim statements to declare variables of various data types. Similarly, we’ll use this Dim statement to declare an array too.

The syntax looks like this:

Dim arrayName() As dataType

Here, arrayName is the name of the array and you’ll specify the dataType according to your need. The data type can be Integer, String, Double, Variant, etc.

You may define the size of your Array from the very beginning. For example, if we need to store the marks of 10 students in an array, the following statement declares an array of size 10 to store the marks.

Sub declareArrayWithSize()
Dim myArray (9) As Integer
End Sub

Note that, by default, array indexing starts from 0. So, here a total of 10 elements starting from 0 to 9 can be stored. In a different way, you can first declare the array without defining the size of the array from the very beginning. For example, the following code declares an array without declaring the size at the very beginning.

Sub declareArrayWithoutSize()
Dim myArray() As Integer
ReDim myArray(9)
End Sub

Now, let’s say we need to store the marks of 21 students. If you need to keep the previous values of the array intact, then you need to use the Redim Preserve statement. I’ve shown this in the following code.

Sub resizeArray()
Dim myArray() As Integer
ReDim myArray(9)
ReDim Preserve myArray(20)
End Sub

This way, you can achieve the task.

How to Launch VBA Editor in Excel

As we’ll use the VBA environment throughout this work, we need to enable the VBA setup first if not done so. First of all, open the Developer tab. If you don’t have the developer tab, then you have to enable the developer tab. Then select the Visual Basic command.

Hence, the Visual Basic window will open. Then, we’ll insert a Module from the Insert option where we’ll write the VBA Code.

Excel VBA Multidimensional Arrays: 3 Examples

Arrays are useful in various types of cases. You may use an array to store employee details such as Employee Name, Employee ID, and Department. Again, you may use an array to store the marks of the students, store the contact details of the students, or store the temperature throughout the year. Depending on your demand, you may use different dimensions of the array.

I’ll explan how you can create multidimensional arrays using VBA in Excel in the following segments.

1. Two Dimensional-Array (2D Array)

At first, I will create a two dimensional array which is a type of multidimensional arrays using VBA in Excel. Two-dimensional array stores data and you need to pass two index numbers to access that data. The data is dispersed in rows and columns. Let’s discuss the following code.

Sub twoDimensionalArray()
Dim myArray(9, 3) As Integer
myArray(0, 0) = 80
myArray(9, 0) = 85
End Sub

Here, we have a total of 10 students and we now need to store their marks in 4 subjects named Physics, Chemistry, Math, and Biology in an array in sequential order. We can do this by declaring a two-dimensional array of size 10(9+1) by 4(3+1). Here, to access the marks of the 10th student in Physics, we need to pass the arguments 9,0 to the array named myArray. Here, 9 and 0 represent the index of row and column in this array. This is an example of two Dimensional array. Note that, if we need to store the marks of a single student, we can simply store that in a one-dimensional array.

1.1 Declaring 2D Array Using VBA

As you already know, a two-dimensional array contains the index of a row and column, we can declare the total no of rows and columns at first. We need to declare the array using the Dim statement. The syntax looks like this:
Look at the following code to get an understanding of how to declare a 2D array.

Sub twoDimensionalArray()
Dim myArray(1 to 9,1 to 2) As Variant
End Sub

Here, I’ve declared a two-dimensional array named myArray of size 9 by 2. And the type of the array is Variant. Note that, I’ve started the indexing from 1 overwriting the default 0.

Read More: Excel VBA to Declare Multidimensional Array of Unknown Size

1.2 Inserting Values in 2D Array

We can insert values in 2D arrays in several ways. We can take the array values manually in the Module. First of all, we’ve inserted a Module and typed the code.We can insert the values in 2D array using the following code:

Sub InsertValuesTo2DArray()
Dim arr(1 To 9, 1 To 2) As Variantarr(1, 1) = "Employee ID"
arr(1, 2) = "Employee Name"
arr(1, 1) = "1001"
arr(1, 2) = "John Smith"
arr(2, 1) = "1002"
arr(2, 2) = "Mary Johnson"
arr(3, 1) = "1003"
arr(3, 2) = "Peter Lee"
arr(4, 1) = "1004"
arr(4, 2) = "Sarah Kim"
arr(5, 1) = "1005"
arr(5, 2) = "Tom Anderson"
arr(6, 1) = "1006"
arr(6, 2) = "Jessica Davis"
arr(7, 1) = "1007"
arr(7, 2) = "Mike Brown"
arr(8, 1) = "1008"
arr(8, 2) = "Lisa Chen"
arr(9, 1) = "1009"
arr(9, 2) = "Kevin Nguyen"End Sub

Sub InsertValuesTo2DArray():

Dim arr(1 To 9, 1 To 2) As Variant:

arr(1, 1) = "Employee ID"
arr(1, 2) = "Employee Name"
arr(1, 1) = "1001"
arr(1, 2) = "John Smith"
arr(2, 1) = "1002"
arr(2, 2) = "Mary Johnson"
arr(3, 1) = "1003"
arr(3, 2) = "Peter Lee"
arr(4, 1) = "1004"
arr(4, 2) = "Sarah Kim"
arr(5, 1) = "1005"
arr(5, 2) = "Tom Anderson"
arr(6, 1) = "1006"
arr(6, 2) = "Jessica Davis"
arr(7, 1) = "1007"
arr(7, 2) = "Mike Brown"
arr(8, 1) = "1008"
arr(8, 2) = "Lisa Chen"
arr(9, 1) = "1009" 
arr(9, 2) = "Kevin Nguyen":

End Sub:

Again, we can insert the values in a 2D array by taking the array values from a worksheet. For example, the following code takes the array values from the worksheet.

Sub RangeValuesto2DArray()
Dim arr() As Variant 'declare 2D array
'read values from range of cells
arr = Range("B5:C13").Value
End Sub

Sub RangeValuesto2DArray():

Dim arr() As Variant:

arr = Range("B5:C13").Value:

End Sub:

In this case, I’ve declared the data type of the array as Variant because that will not create any data type mismatch error.

Read More: Excel VBA 2 Dimensional Array Initialization

1.3 Displaying Values in Multidimensional Arrays

You can show or display the array values in the worksheet. I’ve already shown the process in section 1.2. We just need to add a single line of statement to show the array values in the worksheet. The code is like this:

Sub InsertValuesTo2DArray()
Dim arr(1 To 9, 1 To 2) As Variantarr(1, 1) = "Employee ID"
arr(1, 2) = "Employee Name"
arr(1, 1) = "1001"
arr(1, 2) = "John Smith"
arr(2, 1) = "1002"
arr(2, 2) = "Mary Johnson"
arr(3, 1) = "1003"
arr(3, 2) = "Peter Lee"
arr(4, 1) = "1004"
arr(4, 2) = "Sarah Kim"
arr(5, 1) = "1005"
arr(5, 2) = "Tom Anderson"
arr(6, 1) = "1006"
arr(6, 2) = "Jessica Davis"
arr(7, 1) = "1007"
arr(7, 2) = "Mike Brown"
arr(8, 1) = "1008"
arr(8, 2) = "Lisa Chen"
arr(9, 1) = "1009"
arr(9, 2) = "Kevin Nguyen"'store array values to worksheet cells
Range("B5:C13").Value = arr
End Sub

Range("B5:C13").Value = arr

Now, press F5 to run the macro.

After running the macro, the array value will be displayed in the range of cells B5:C13. The output is shown in the following video.

I’ve also shown this using images. Here, the following image is the image of the dataset before running the macro, where we’ll display the array values.

After running the macro, the values of the array will take place in the cells like the following image.

You may insert the array elements from a range of cells too. To get a glimpse of how it looks, you may watch the following video.

In this case, we have a dataset containing Employee ID, Employee Name, and Department ranging from cells B4:D13. Now we need to take Employee ID and Employee Name data in an array. To demonstrate that the array contains the exact same data, we then will show the array elements in the same worksheet in the cells ranging from F5:G13.

We just need a single line of statement in the code of inserting array elements from the range to show the array data in the dataset.

Sub RangeValuesto2DArray()
Dim arr() As Variant 'declare 2D array
'read values from range of cells
arr = Range("B5:C13").Value
Range("F5:G13").Value = arr
End Sub

Range("F5:G13").Value = arr

Our dataset looks like this:

After accomplishing the task, the worksheet will look like this:

Read More: How to Use UBound on Multidimensional Array with VBA in Excel

Similar Readings

• Excel VBA to Create Data Validation List from Array
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
• How to Convert Range to Array in Excel VBA (3 Ways)
• Excel VBA to Transpose Array (3 Suitable Examples)
• How to Sort Array with Excel VBA (Both Ascending and Descending Order)

2. Three-Dimensional-Array (3D Array) in Excel

Now, I will create a three dimensional array which is another type of multidimensional arrays using VBA in Excel. Before anything, to get a glimpse of what a three-dimensional array looks like, you may watch the following video.

In Excel VBA, 3D arrays can be useful when working with data that has multiple dimensions or layers of data. We need to specify the array dimensions depending on the use case.

For example, we are recording the average temperature of different places hourly. Now, if we want to insert the hourly average temperature in an array, then we can do this using a 1D array. Now think about this in a different way. If we need to insert the average temperature of hours along with the day, we need to use a 2D array. Again, now if we have data from several locations and if we want to insert the average temperature of an hour mentioning the day and location, then we need to use a 3D array. The array indexes then will represent the location, day, and time that is hour.

Now, let’s say we need to show the temperature of Location1 on Monday at 2:00 AM. So, we can use the following code to insert the temperatures in a 3D array and when we need to show data of a specific time, we may recall the value using that array.

Sub weatherData()
Dim weatherData(1 To 3, 1 To 7, 1 To 24) As Double
Dim Location As Variant, dayOfWeek As Variant, Hour As Integer
Dim LocationName As String, dayOfWeekName As String
' Populate the weather data for Location 1(California) on Monday
weatherData(1, 1, 1) = 75.5
weatherData(1, 1, 2) = 76.2
weatherData(1, 1, 3) = 77.3
' the rest of the data is not shown here, just for _
mentioning the last entry here will be _
WeatherData(1,1,24)=70.6
' Populate the weather data for Location 2(Washington) on Monday
weatherData(2, 1, 1) = 65.4
weatherData(2, 1, 2) = 66.1
weatherData(2, 1, 3) = 68.5
' ' the rest of the data is not shown here, just for _
mentioning the last entry here will be _
WeatherData(2,1,24)=72.8
' Populate the weather data for Location 3(Texas) on Monday
weatherData(3, 1, 1) = 80.1
weatherData(3, 1, 2) = 81.2
weatherData(3, 1, 3) = 82.7
' ' the rest of the data is not shown here, just for _
mentioning the last entry here will be WeatherData(3,1,24)=80
' Display the weather data for Location 1 on Monday at 2:00 AM
Location = InputBox("Enter the location name (California, Washington, Texas):")
dayOfWeek = InputBox("Enter the day (Monday-Sunday):")
Hour = InputBox("Enter the hour number (1-24):", , 1)
Select Case Location
Case "California"
Location = 1
LocationName = "California"
Case "Washington"
Location = 2
LocationName = "Washington"
Case "Texas"
Location = 3
LocationName = "Texas"
Case Else
MsgBox "Invalid location entered."
Exit Sub
End Select
Select Case dayOfWeek
Case "Monday"
dayOfWeek = 1
dayOfWeekName = "Monday"
Case "Tuesday"
dayOfWeek = 2
dayOfWeekName = "Tuesday"
Case "Wednesday"
dayOfWeek = 3
dayOfWeekName = "Wednesday"
Case "Thursday"
dayOfWeek = 4
dayOfWeekName = "Thursday"
Case "Friday"
dayOfWeek = 5
dayOfWeekName = "Friday"
Case "Saturday"
dayOfWeek = 6
dayOfWeekName = "Saturday"
Case "Sunday"
dayOfWeek = 7
dayOfWeekName = "Sunday"
Case Else
MsgBox "Invalid day of week entered"
Exit Sub
End Select
MsgBox "The temperature for " & LocationName & " on " & dayOfWeekName & " at " & Hour & ":00 AM is " & weatherData(Location, dayOfWeek, Hour) & "."
End Sub

Sub weatherData():

weatherData

Dim weatherData(1 To 3, 1 To 7, 1 To 24) As Double:

Dim Location As Variant, dayOfWeek As Variant, Hour As Integer

Dim LocationName As String, dayOfWeekName As String:

weatherData(1, 1, 1) = 75.5
weatherData(1, 1, 2) = 76.2

weatherData(1, 1, 3) = 77.3:

weatherData(1, 1, 1)

Location = InputBox("Enter the location name (California, Washington, Texas):")
dayOfWeek = InputBox("Enter the day (Monday-Sunday):")
Hour = InputBox("Enter the hour number (1-24):", , 1):

Select Case Location
            Case "California"
                        Location = 1
                        LocationName = "California"
            Case "Washington"
                        Location = 2
                        LocationName = "Washington"
            Case "Texas"
                        Location = 3
                        LocationName = "Texas"
            Case Else
                        MsgBox "Invalid location entered."
            Exit Sub

End Select:

Select Case dayOfWeek
            Case "Monday"
                        dayOfWeek = 1
                        dayOfWeekName = "Monday"
            Case "Tuesday"
                        dayOfWeek = 2
                        dayOfWeekName = "Tuesday"
            Case "Wednesday"
                        dayOfWeek = 3
                        dayOfWeekName = "Wednesday"
            Case "Thursday"
                        dayOfWeek = 4
                        dayOfWeekName = "Thursday"
            Case "Friday"
                        dayOfWeek = 5
                        dayOfWeekName = "Friday"
            Case "Saturday"
                        dayOfWeek = 6
                        dayOfWeekName = "Saturday"
            Case "Sunday"
                        dayOfWeek = 7
                        dayOfWeekName = "Sunday"
            Case Else
                        MsgBox "Invalid day of week entered"
            Exit Sub

End Select:

MsgBox "The temperature for " & LocationName & " on " & dayOfWeekName & " at " & Hour & ":00 AM is " & weatherData(Location, dayOfWeek, Hour) & ".":

End Sub:

You can watch the following video to get a glimpse of how it looks.

Now that we are familiar with how a three-dimensional array looks in Excel VBA, I want to demonstrate another example here. In this example, we’ll use the same dataset we are using throughout this article to demonstrate the use of a three-dimensional array. Here, we’ll take the first few entries with their Employee ID, Employee Name and Department as the array element of a three-dimensional array. Then we’ll populate the array values in a new worksheet and will show the array values there.

Sub CreateAndPopulate3DArray()
Dim arr() As Variant 'declare 2D array
'read values from range of cells
arr = Range("B4:D9").Value'create a new worksheet and set the name
Dim newWorksheet As Worksheet
Set newWorksheet = ThisWorkbook.Sheets.Add
newWorksheet.Name = "Populating 3D Array Values"newWorksheet.
Range("B4:D9").Value = arr
'adjust the columns of the new worksheet
newWorksheet.Columns("B:D").AutoFit
End Sub

Sub CreateAndPopulate3DArray():

CreateAndPopulate3DArray.

Dim arr() As Variant:

arr = Range("B4:D9").Value:

Dim newWorksheet As Worksheet:

Set newWorksheet = ThisWorkbook.Sheets.Add:

newWorksheet.Name = "Populating 3D Array Values":

newWorksheet.Range("B4:D9").Value = arr:

newWorksheet.Columns("B:D").AutoFit:

End Sub:

If we run the macro, our output will look like this:

Read More: Excel VBA Multidimensional Array for Assigning Values (6 Ways)

3. Multidimensional Arrays with More Than Three Dimensions in Excel

Although Excel offers up to 32 dimensions of an array, we don’t need to use more than three dimensional in real life frequently. So, we’re covering the most frequently used one-dimensional, two-dimensional, and three-dimensional arrays in this article.

Being said that, if you need to use more than a three-dimensional array, you may just do that using the basics we’ve shown in this article.

How to Declare an Array with Array Function in Excel VBA

To know how the Array function works, you may first check this video.

We can declare an array using the Array function. The Array function creates an array with a specified number of elements and initializes the elements with the values provided as arguments. Here, I’ve shown an example of how to declare and initialize an array using the Array function.

Sub ArrayFunctionUse()
    Dim myArray As Variant
    myArray = Array("1001", "1002", "1003", "1004")
    'set values of range to array values
    Range("B5").Resize(UBound(myArray) + 1, 1).Value = _
    Application.Transpose(myArray)
End Sub

Sub ArrayFunctionUse():

Dim myArray As Variant:

myArray = Array("1001", "1002", "1003", "1004"):

Range("B5").Resize(UBound(myArray) + 1, 1).Value = Application.Transpose(myArray):

Resize(UBound(myArray) + 1, 1)

End Sub:

Now if I run the macro, the output will look like this:

In this case, I’ve taken the EmployeeID as array values and showed them in the worksheet. Note that when declaring an array using the Array function, the data type of the elements in the array is determined automatically based on the type of the values provided as arguments. This is an advantage of using the Array function.

Read More: How to Create an Array in Excel VBA (4 Ideal Methods)

Limitations of Multi-dimensional Arrays in Excel

The main limitation of using multidimensional arrays is related to space. The more the dimension of an array, the more space it will hold in memory. For this reason, we should use multidimensional arrays only when it is a necessity.

Frequently Asked Questions

• How do I create a dynamic multidimensional array in VBA?

To create a dynamic multidimensional array in VBA, you can use the ReDim statement. The ReDim statement allows you to change the size of an array at runtime. I’ve shown the details in How to Declare an Array in Excel VBA segment, so not repeating here.

• How many dimensions can an array have VBA?

According to Microsoft, an array can have as many as 32 dimensions. In real-life scenarios, mostly used multidimensional arrays have up to three dimensions.

Things to Remember

You should keep in mind a few things to properly use multidimensional arrays in Excel VBA.

• By default, array indexes start from 0.
• Define the dimension of an array carefully. Do not use multi dimensions unnecessarily.
• Use ReDim if you need to redefine the array dimension.
• If you need to preserve the previous values of the array, use ReDim Preserve.
• Declare the data type of the array accordingly.
• Use a nested loop to access all the elements of an array.

Conclusion

In this article, I’ve covered the details of multidimensional arrays in Excel VBA. I’ve covered everything from the syntax of the array to the real life use cases. Also, different useful options like redefining the array size and different ways of taking array elements are also covered in this article. I’ve mentioned the things you should remember while using arrays in Excel VBA because it is slightly different from the other programming languages counterpart. I hope that we have got the necessary knowledge to use multidimensional arrays in Excel VBA whenever you need them. Please let me know if you face any problem regarding that. I wish you a real good day!

Related Articles

• Excel VBA Array of Strings (4 Examples)
• How to Find Lookup Value in Array in Excel VBA (2 Easy Ways)
• Excel VBA Array of Arrays (3 Examples)
• VBA Read Text File into Array (2 Suitable Methods)
• How to Use Arrays Instead of Ranges in Excel VBA
• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
• Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)