# Excel VBA Multidimensional Arrays

Get FREE Advanced Excel Exercises with Solutions!

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:

## 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.

#### 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``````

Code Breakdown:

Here, we’ll insert the array values using code in the module.

``Sub InsertValuesTo2DArray():``

A sub procedure is declared named InsertValuesTo2DArray

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

A two dimensional array is declared. The size of the array is 9 by 2.

``````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":````

Here, I’ve inserted the array elements manually. As I’ve described earlier, arr is a 9 by 2 two-dimensional array, so there are total of 18(9*2=18) array elements. We can think of it as a table with 9 rows and 2 columns.

``End Sub:``

The subprocedure ends.

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``````

Code Breakdown:

``Sub RangeValuesto2DArray():``

A sub procedure is declared named RangeValuesto2DArrayRangeValuesto2DArray.

``Dim arr() As Variant:``

An array named arr is declared as of Variant data type leaving the size unspecified.

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

The values from cell range B5:C13 have been taken as the array elements.

``End Sub:``

The subprocedure ends.

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

#### 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``````

Code Breakdown:

This code breakdown is exactly the same as the previous breakdown. We have just one additional statement here:

Here,

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

statement stores the value of our arr array in the B5:C13 range of cells.

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``````

Code Breakdown:

Here,

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

statement stores the value of our arr array in the F5:G13 range of cells.

I’ve already explained the remaining portion of this code in section 1.2.

Our dataset looks like this:

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

### 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``````

Code Breakdown:

``Sub weatherData():``

A sub procedure named

``weatherData``

is declared.

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

A three-dimensional array named weatherData is declared. Here 1 To 3 represents the indexes of three locations, 1 To 7 represents the indexes of seven days in a week and 1 To 24 represents the indexes of 24 hours of a day.

``Dim Location As Variant, dayOfWeek As Variant, Hour As Integer``
``Dim LocationName As String, dayOfWeekName As String:``

Variables named Location, dayOfWeek, and Hour are declared to take input from the user. LocationName and dayOfWeekName are declared to show the respective names in the message box.

``````weatherData(1, 1, 1) = 75.5
weatherData(1, 1, 2) = 76.2``````
``weatherData(1, 1, 3) = 77.3:``

Here

``weatherData(1, 1, 1)``

represents the average temperature of location 1, on day one (that is Monday) and time 1 AM. The temperature value for this entry is 75.5. The other data represents the average temperature in such a fashion.

``````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):``````

Name of location, day of week and hour are as input using InputBox.

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

Various select cases are defined for Location. For example, if the user     gives Texas as input, the location will be selected as 2 in the array. Also, the LocationName variable will hold the value Texas. If the input name doesn’t exist in our dataset, then the message box will show Invalid location entered.

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

Similarly, different cases are defined for the name of the day,

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

This statement shows the average temperature for the location on the given day at a given time in MsgBox.

``End Sub:``

Ends the subprocedure.

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
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``````

Code Breakdown:

``Sub CreateAndPopulate3DArray():``

This statement creates a sub procedure named

``CreateAndPopulate3DArray.``
``Dim arr() As Variant:``

An array named arr is declared and the data type is Variant.

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

This line takes the range of cell values as the array elements.

``Dim newWorksheet As Worksheet:``

Declares a new variable named newWorksheet as a worksheet object.

``Set newWorksheet = ThisWorkbook.Sheets.Add:``

Creates a new worksheet in the current Excel workbook, and assigns it to the newWorksheet object.

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

Sets the newWorksheet name as Populating 3D Array Values.

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

Puts the elements of the arr array in the Populating 3D Array Values worksheet in cells ranging from B4:D9.

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

This statement adjusts the column’s width of the Populating 3D Array Values worksheet.

``End Sub:``

Ends the subprocedure.

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

### 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``````

Code Breakdown:

``Sub ArrayFunctionUse():``

This line defines the beginning of the subroutine.

``Dim myArray As Variant:``

Declares a variable named myArray as a Variant data type.

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

Initializes the myArray variable with an array of four values.

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

This statement sets the values of a range of cells to the values of an array, and transposes the array to fit the cells in the range. Here, Range(“B5”) specifies a single cell in the worksheet,

``Resize(UBound(myArray) + 1, 1)``

resizes the range so that it has the same number of rows at the array, plus one extra row for the header in cell B4 and one column. .Value specifies that the values of the range will be set to the values of the array. Application.Transpose(myArray) transposes the array so that it is oriented vertically, which makes it easier to fit the values into the range.

``End Sub:``

Ends the subprocedure.

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.

## 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.

• 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!