Overview of a 2-Dimensional Array (2D Array)
- Definition
A 2-dimensional array, also known as a 2D array, is an array that has data spread out in two separate directions. In VBA, a 2D array is declared by specifying the number of rows and columns that it will have, and each element in the array can be accessed using two indices, one for the row and one for the column.
- Syntax
Dim arrayName(rowSize, columnSize) As datatypeWe can see that a 2 dimensional is now declared and defined. Here, the size of the array in both directions is defined with rowSize and columnSize.
- Initialize
For the initialization of a 2 dimensionalized array, you need to initialize with the data type. And the type of data that is going to be inserted in the array is specified in the datatype. The types of data could be integer, long, double, string, date, etc. The variant data type is a mixture of data types, which means that the data in the array are going to be mixed, and no set data type. Among all of those types, the variant data type consumes the most amount of space in the memory. It is good practice to avoid this data type. Integer takes the least amount of space in the memory.
- Data input in the array
In order to input data in an array, the user needs to address each array element according to their address and then assign value to them.
For example, in the case of the array mentioned above, we store the string “random” in the array array name (5).
Dim arrayName(rowSize, colSize) as variant
arrayName(5)="random"- Show data
To show the value in the immediate window, we can use the code:
Debug.Print arrayName(5)For showing in the message box:
Messagebox arrayName(5)To show the values in the worksheet:
activesheet.cells(5,5).values= arrayName(5)Example of Arrays in Excel
For a 1-dimensional array, you can follow the procedure in the video.
The code we used is:
Sub OneDimensionalArrayExample()
Dim myArray(1 To 5) As Integer
myArray(1) = 10
myArray(2) = 20
myArray(3) = 30
myArray(4) = 40
myArray(5) = 50
numRows = UBound(myArray)
For i = 1 To numRows
ActiveSheet.Cells(2 + i, 2).Value = myArray(i)
Next i
End Sub
For 2-dimensional arrays, follow the below code, which will initialize the 2-dimensional array and then print it on the worksheet.
Sub TwoDimensionalArrayExample()
Dim myArray(1 To 3, 1 To 2) As Integer
myArray(1, 1) = 1
myArray(1, 2) = 2
myArray(2, 1) = 3
myArray(2, 2) = 4
myArray(3, 1) = 5
myArray(3, 2) = 6
numRows = UBound(myArray, 1)
numCols = UBound(myArray, 2)
For i = 1 To numRows
For j = 1 To numCols
ActiveSheet.Cells(2 + i, 2 + j).Value = myArray(i, j)
Next j
Next i
End Sub
Dynamic Arrays in Excel
We are giving this dynamic array example where you can resize your array as your wish and input values according to that.
Sub DynamicArrayExample()
Dim myArray() As Variant
myArray = Array("apple", "banana", "cherry", "date", "elderberry")
numRows = UBound(myArray)
For i = 0 To numRows
ActiveSheet.Cells(5 + i, 2).Value = myArray(i)
Next i
Dim response As VbMsgBoxResult
response = MsgBox("Your arrays are now present. Do you want to Expand your array dynamically?", _
vbYesNoCancel, "Confirmation")
Select Case response
Case vbYes
Dim sourceRange2 As String
sourceRange = InputBox("Enter a Fruit Name", "Value Enter")
sourceRange2 = InputBox("Enter another Fruit Name", "Value Enter")
ReDim Preserve myArray(UBound(myArray) + 2)
myArray(UBound(myArray) - 1) = sourceRange
myArray(UBound(myArray)) = sourceRange2
numRows1 = UBound(myArray)
For j = 0 To numRows1
ActiveSheet.Cells(5 + j, 3).Value = myArray(j)
Next j
Case vbNo
MsgBox "You Decided to Quit"
Case vbCancel
MsgBox "You clicked Cancel."
End Select
End Sub
VBA Code Explanation
Sub DynamicArrayExample()- This line defines the name of the macro.
Dim myArray() As Variant- This line declares a dynamic array named “myArray” that will store an unspecified number of elements of any data type.
myArray = Array("apple", "banana", "cherry", "date", "elderberry")- This line initializes the array “myArray” with five string values.
numRows = UBound(myArray)- This line assigns the number of rows in the array to the variable “numRows” by using the UBound function to get the upper bound of the array.
For i = 0 To numRows
ActiveSheet.Cells(5 + i, 2).Value = myArray(i)
Next i- This loop writes the values in “myArray” to cells in the second column of the current worksheet, starting at row 5.
Dim response As VbMsgBoxResult
response = MsgBox("Your arrays are now present. Do you want to Expand your array dynamically?", _
vbYesNoCancel, "Confirmation")- This line displays a message box with a confirmation message and three buttons: Yes, No, and Cancel. It assigns the user’s response to the variable “response”.
Select Case response
Case vbYes- This line begins a Select Case statement that checks the value of “response”.
Dim sourceRange2 As String
sourceRange = InputBox("Enter a Fruit Name", "Value Enter")
sourceRange2 = InputBox("Enter another Fruit Name", "Value Enter")- These lines prompt the user to enter two fruit names, and store them in variables “sourceRange” and “sourceRange2”.
ReDim Preserve myArray(UBound(myArray) + 2)
myArray(UBound(myArray) - 1) = sourceRange
myArray(UBound(myArray)) = sourceRange2
numRows1 = UBound(myArray)
For j = 0 To numRows1
ActiveSheet.Cells(5 + j, 3).Value = myArray(j)
Next j- These lines resize the “myArray” array by two elements and assign the new values entered by the user. Then, it writes the updated array values to cells in the third column of the current worksheet, starting at row 5.
Case vbNo
MsgBox "You Decided to Quit"
Case vbCancel
MsgBox "You clicked Cancel."
End Select- These lines display message boxes with appropriate messages depending on which button the user clicked. If the user clicked “No”, the macro will display a message saying “You Decided to Quit”. If the user clicks “Cancel”, the macro will display a message saying “You clicked Cancel.”.
End Sub- This line marks the end of the macro.
How to Launch the VBA Editor in Excel
- You can easily launch the VBA editor by pressing Alt + F11 or using the Visual Basic command from the Developer tab.
- After entering the Developer tab, select Insert and choose Module, and you will see an editor where you can put the necessary code.
Note:
If you can’t find the Developer tab in the ribbon, then you need to enable it from the options.
Excel VBA 2-Dimensional Array Initialization: 2 Examples
Example 1 – Static 2-Dimensional Array
- Here’s a code with a static array:
Sub Initialize_Static_Array()
Dim data(1 To 3, 1 To 3) As String
data(1, 1) = "Name"
data(1, 2) = "Age"
data(1, 3) = "Gender"
data(2, 1) = "John"
data(2, 2) = "32"
data(2, 3) = "Male"
data(3, 1) = "Jane"
data(3, 2) = "28"
data(3, 3) = "Female"
numRows = UBound(data, 1)
numCols = UBound(data, 2)
For i = 1 To numRows
For j = 1 To numCols
ActiveSheet.Cells(3 + i, 1 + j).Value = data(i, j)
Next j
Next i
End Sub
VBA Code Breakdown
Sub Initialize_Static_Array()- Sub Initialize_Static_Array(): This line starts the sub procedure called “Initialize_Static_Array”.
Dim data(1 To 3, 1 To 3) As String- Dim data(1 To 3, 1 To 3) As String: This line declares an array variable called “data” with 3 rows and 3 columns, all of which will store strings.
data(1, 1) = "Name"
data(1, 2) = "Age"
data(1, 3) = "Gender"
data(2, 1) = "John"
data(2, 2) = "32"
data(2, 3) = "Male"
data(3, 1) = "Jane"
data(3, 2) = "28"
data(3, 3) = "Female"- The array is now going to be filled up by the string data.
numRows = UBound(data, 1)- numRows = UBound(data, 1): This line assigns the number of rows in the “data” array to the variable “numRows” using the UBound function.
numCols = UBound(data, 2)- numCols = UBound(data, 2): This line assigns the number of columns in the “data” array to the variable “numCols” using the UBound function.
For i = 1 To numRows- For i = 1 To numRows: This line starts a loop that will iterate through each row of the “data” array.
For j = 1 To numCols- For j = 1 To numCols: This line starts a nested loop that will iterate through each column of the “data” array.
ActiveSheet.Cells(3 + i, 1 + j).Value = data(i, j)- ActiveSheet.Cells(3 + i, 1 + j).Value = data(i, j): This line assigns the value of the current element in the “data” array to the cell at the corresponding row and column in the worksheet.
Next j- Next j: This line ends the inner loop that iterates through each column of the “data” array.
Next i- Next i: This line ends the outer loop that iterates through each row of the “data” array.
End Sub- End Sub: This line ends the sub procedure called “Initialize_Static_Array”.
Example 2 – Dynamic 2-Dimensional Array
Use this code:
Sub dynamic_array_initiation()
Dim sourceRange1 As Range
Set sourceRange1 = Application.InputBox("Select a range", "Range selection", Type:=8)
Dim numRows As Long, numCols As Long
numRows = sourceRange1.Rows.Count
numCols = sourceRange1.Columns.Count
Dim old_Dynamic_Array() As Variant
ReDim old_Dynamic_Array(1 To numRows, 1 To numCols)
Dim i As Long, j As Long
For i = 1 To numRows
For j = 1 To numCols
old_Dynamic_Array(i, j) = sourceRange1.Cells(i, j).Value
Next j
Next i
x = UBound(old_Dynamic_Array, 1)
Y = UBound(old_Dynamic_Array, 2)
Dim response As VbMsgBoxResult
response = MsgBox("Your Selecred Cells array dimension is (" & x & "," & Y & "). Do you want to Expand your Selection?", vbYesNoCancel, "Confirmation")
Select Case response
Case vbYes
Dim sourceRange2 As Range
Set sourceRange2 = Application.InputBox("Select a range", "Range selection", Type:=8)
Dim numRows1 As Long, numCols1 As Long
numRows1 = sourceRange2.Rows.Count
numCols1 = sourceRange2.Columns.Count
Dim new_Dynamic_Array() As Variant
ReDim new_Dynamic_Array(1 To numRows1, 1 To numCols1)
Dim k As Long, l As Long
For k = 1 To numRows1
For l = 1 To numCols1
new_Dynamic_Array(k, l) = sourceRange2.Cells(k, l).Value
Next l
Next k
x1 = UBound(new_Dynamic_Array, 1)
y1 = UBound(new_Dynamic_Array, 2)
MsgBox "Your Old Array dimension was (" & x & "," & Y & "). Your new array dimension is (" & x1 & "," & y1 & ")"
Case vbNo
MsgBox "You Decided to Quit"
Case vbCancel
MsgBox "You clicked Cancel."
End Select
End Sub
VBA Code Breakdown
Sub dynamic_array_initiation()- Sub dynamic_array_initiation(): This is the beginning of the subroutine definition. “dynamic_array_initiation” is the name of the subroutine.
Dim sourceRange1 As Range- Dim sourceRange1 As Range: This declares a variable named “sourceRange1” as a Range object.
Set sourceRange1 = Application.InputBox("Select a range", "Range selection", Type:=8)- Set sourceRange1 = Application.InputBox(“Select a range”, “Range selection”, Type:=8): This line prompts the user to select a range of cells in the Excel worksheet. The selected cells are stored in the “sourceRange1” variable.
Dim numRows As Long, numCols As Long- Dim numRows As Long, numCols As Long: This declares two variables named “numRows” and “numCols” as Long integers.
numRows = sourceRange1.Rows.Count- *numRows = sourceRange1.Rows.Count: This sets the value of “numRows” to the number of rows in the “sourceRange1” variable.
numCols = sourceRange1.Columns.Count- numCols = sourceRange1.Columns.Count: This sets the value of “numCols” to the number of columns in the “sourceRange1” variable.
Dim old_Dynamic_Array() As Variant- Dim old_Dynamic_Array() As Variant: This declares a variable named “old_Dynamic_Array” as a Variant array.
ReDim old_Dynamic_Array(1 To numRows, 1 To numCols)- ReDim old_Dynamic_Array(1 To numRows, 1 To numCols): This resizes the “old_Dynamic_Array” array to match the number of rows and columns in the selected range.
Dim i As Long, j As Long- Dim i As Long, j As Long: This declares two variables named “i” and “j” as Long integers.
For i = 1 To numRows- For i = 1 To numRows: This begins a loop that will iterate through each row in the selected range.
For j = 1 To numCols- For j = 1 To numCols: This begins a nested loop that will iterate through each column in the selected range.
old_Dynamic_Array(i, j) = sourceRange1.Cells(i, j).Value- old_Dynamic_Array(i, j) = sourceRange1.Cells(i, j).Value: This sets the value of the current element in the “old_Dynamic_Array” array to the value of the corresponding cell in the selected range.
Next j- Next j: This ends the nested loop that iterates through the column.
Next i- Next i: This ends the loop that iterates through the rows.
x = UBound(old_Dynamic_Array, 1)- x = UBound(old_Dynamic_Array, 1): This sets the value of “x” to the upper bound of the first dimension (rows) of the “old_Dynamic_Array” array.
Y = UBound(old_Dynamic_Array, 2)- Y = UBound(old_Dynamic_Array, 2): This sets the value of “Y” to the upper bound of the second dimension (columns) of the “old_Dynamic_Array” array.
Dim response As VbMsgBoxResult- Dim response As VbMsgBoxResult: This declares a variable named “response” as a VbMsgBoxResult enumeration.
response = MsgBox("Your Selecred Cells array dimension is (" & x & "," & Y & "). Do you want to Expand your Selection?", vbYesNoCancel, "Confirmation")- response = MsgBox(“Your Selecred Cells array dimension is (” & x & “,” & Y & “). Do you want to Expand your Selection?”, vbYesNoCancel, “Confirmation”): This displays a message box asking the user if they want to expand their selection of cells. The response is stored in the “response” variable.
Select Case response- Select Case response: This begins a “Select Case” block that will execute different code based on the value of the “response” variable.
Case vbYes- Case vbYes: This case is executed if the user clicked the “Yes” button in the message box.
Dim sourceRange2 As Range- Dim sourceRange2 As Range: This declares a variable named “sourceRange2” as a Range object.
Set sourceRange2 = Application.InputBox("Select a range", "Range selection", Type:=8)- This line opens an input box that asks the user to select a new range. The Type:=8 argument specifies that the input box should accept a range input.
Dim numRows1 As Long, numCols1 As Long- These two lines declare two new variables numRows1 and numCols1 as Long data types. These variables will be used to store the number of rows and columns in the new range selected by the user.
numRows1 = sourceRange2.Rows.Count- This line assigns the number of rows in the new range selected by the user to the variable numRows1.
numCols1 = sourceRange2.Columns.Count- This line assigns the number of columns in the new range selected by the user to the variable numCols1.
Dim new_Dynamic_Array() As Variant- This line declares a new dynamic array called new_Dynamic_Array with no fixed size or dimension.
ReDim new_Dynamic_Array(1 To numRows1, 1 To numCols1)- This line resizes the new_Dynamic_Array to match the number of rows and columns in the new range selected by the user.
Dim k As Long, l As Long
For k = 1 To numRows1- This line starts a For loop that will run numRows1 times.
For l = 1 To numCols1- This line starts a nested For loop that will run numCols1 times.
new_Dynamic_Array(k, l) = sourceRange2.Cells(k, l).Value- This line assigns the value of each cell in the new range selected by the user to the corresponding element of the new_Dynamic_Array.
Next l- This line marks the end of the inner For loop.
Next k- This line marks the end of the outer For loop.
x1 = UBound(new_Dynamic_Array, 1)- This line assigns the number of rows in the new_Dynamic_Array to the variable x1.
y1 = UBound(new_Dynamic_Array, 2)- This line assigns the number of columns in the new_Dynamic_Array to the variable y1.
MsgBox "Your Old Array dimension was (" & x & "," & Y & "). Your new array dimension is (" & x1 & "," & y1 & ")"- This line displays a message box that shows the dimensions of both the old and new arrays.
Case vbNo- This is a part of the Select Case block that checks if the user clicked the “No” button in the message box that asked if they wanted to expand their selection.
MsgBox "You Decided to Quit"- This line displays a message box that informs the user that they have decided to quit.
Case vbCancel
MsgBox "You clicked Cancel."- This is a part of the Select Case block that checks if the user clicked the “Cancel”.
End Select- This line of code will denote the end of the Message box with options.
End Sub- This will denote the end of the subprocedure.
Read More: Excel VBA Multidimensional Arrays
VBA 2-Dimensional Array with Different Data Types
While in most cases the data types used in arrays are generic numbers like integer or long, users can use a variety of data formats for the arrays, like strings, date, boolean, etc. Like the example code given below,
Dim boolArray(2, 2) As Boolean
boolArray(0, 0) = True
boolArray(0, 1) = False
boolArray(1, 0) = True
boolArray(1, 1) = True
boolArray(2, 0) = False
boolArray(2, 1) = TrueWe can also use the date type of a 2-D array in VBA, like below.
Sub array_with_dates()
Dim dateArray(2, 1) As Date
dateArray(0, 0) = #5/23/2022#
dateArray(0, 1) = #6/15/2022#
dateArray(1, 0) = #7/1/2022#
dateArray(1, 1) = #8/12/2022#
dateArray(2, 0) = #9/1/2022#
dateArray(2, 1) = #10/10/2022#
For i = 0 To UBound(dateArray, 1)
For j = 0 To UBound(dateArray, 2)
ActiveSheet.Cells(i + 3, j + 2) = dateArray(i, j)
Next j
Next i
End Sub
VBA Code Breakdown
Dim dateArray(2, 1) As Date- This will denote the array initiation of the array named as date array, which type is data.
dateArray(0, 0) = #5/23/2022#
dateArray(0, 1) = #6/15/2022#
dateArray(1, 0) = #7/1/2022#
dateArray(1, 1) = #8/12/2022#
dateArray(2, 0) = #9/1/2022#
dateArray(2, 1) = #10/10/2022#- This part will be denoted as the date data input in the arrays.
For i = 0 To UBound(dateArray, 1)- This will denote the upper limit of the array in the first dimension and begin for loop.
For j = 0 To UBound(dateArray, 2)- This will denote the upper limit of the array in the second dimension and begin for loop.
ActiveSheet.Cells(i + 3, j + 2) = dateArray(i, j)- This part of the code will output the array values in the worksheet.
Next j- This will exit the previous loop.
Next i- This will exit the first loop.
End Sub- This will exit the subprocedure.
Another code for string type 2 dimensional data is
Sub array_with_string()
Dim string_array(1 To 3, 1 To 3) As String
string_array(1, 1) = "apple"
string_array(1, 2) = "banana"
string_array(1, 3) = "cherry"
string_array(2, 1) = "dog"
string_array(2, 2) = "cat"
string_array(2, 3) = "bird"
string_array(3, 1) = "red"
string_array(3, 2) = "green"
string_array(3, 3) = "blue"
For i = 1 To UBound(string_array, 1)
For j = 1 To UBound(string_array, 2)
ActiveSheet.Cells(i + 3, j + 1) = string_array(i, j)
Next j
Next i
End Sub
VBA Code Explanation
Sub array_with_string()- The start of a subroutine named “array_with_string”.
Dim string_array(1 To 3, 1 To 3) As String- Declares a two-dimensional array of strings with dimensions 3 x 3.
string_array(1, 1) = "apple"
string_array(1, 2) = "banana"
string_array(1, 3) = "cherry"
string_array(2, 1) = "dog"
string_array(2, 2) = "cat"
string_array(2, 3) = "bird"
string_array(3, 1) = "red"
string_array(3, 2) = "green"
string_array(3, 3) = "blue"- This part will be denoted as the date data input in the array.
For i = 1 To UBound(string_array, 1)- Starts a loop that iterates through the rows of the array, from 1 to the upper bound of the first dimension.
For j = 1 To UBound(string_array, 2)- Starts a nested loop that iterates through the columns of the array, from 1 to the upper bound of the second dimension.
ActiveSheet.Cells(i + 3, j + 1) = string_array(i, j)- Assigns the value of the current element of the array to a cell in the worksheet. The “+3” and “+1” are used to offset the location so that the first element appears in cell B4.
Next j- Ends the nested loop.
Next i- Ends the outer loop.
End Sub- Ends the subroutine.
Read More: How to Redim 2D Array with VBA in Excel
How to Store Data from a Range to a Two-Dimensional Array in Excel VBA
We can extract data from the worksheet to the array using this code:
Sub TestArrayOutput()
Dim myArray() As Variant
Dim numRows As Integer
Dim numCols As Integer
Dim i As Integer
Dim j As Integer
Dim myRange As Range
Set myRange = Range("B5:C10")
numRows = myRange.Rows.Count
numCols = myRange.Columns.Count
ReDim myArray(1 To numRows, 1 To numCols)
For i = 1 To numRows
For j = 1 To numCols
myArray(i, j) = myRange.Cells(i, j).Value
Next j
Next i
MsgBox UBound(myArray, 1)
MsgBox UBound(myArray, 2)
End Sub
Read More: How to ReDim Preserve 2D Array in Excel VBA
How to Initialize a Multidimensional Array at Once in Excel VBA
Here’s the code that will present the 3-dimensional code all at once.
Sub three_dimensional_array()
Dim ThreeDArray As Variant
ReDim ThreeDArray(1, 1, 1)
ThreeDArray(0, 0, 0) = "Aria Johnson"
ThreeDArray(0, 0, 1) = "Caleb Rodriguez"
ThreeDArray(0, 1, 0) = "Maya Chen"
ThreeDArray(0, 1, 1) = "Liam Campbell"
ThreeDArray(1, 0, 0) = "Liam Campbell"
ThreeDArray(1, 0, 1) = "Ava Nguyen"
ThreeDArray(1, 1, 0) = "Ethan Kim"
ThreeDArray(1, 1, 1) = "Sofia Patel"
m = 3
For i = 0 To UBound(ThreeDArray, 1)
For j = 0 To UBound(ThreeDArray, 2)
For k = 0 To UBound(ThreeDArray, 3)
ActiveSheet.Cells(m, 2) = ThreeDArray(i, j, k)
m = m + 1
Next k
Next j
Next i
End Sub
VBA Code Breakdown
Sub multi_dimensional_array()- This is a subroutine named dimensional_array.
Dim ThreeDArray As Variant- This declares a variable named ThreeDArray as a Variant data type.
ReDim ThreeDArray(1, 1, 1)- This resizes the ThreeDArray to have three dimensions with the size of 2 in each dimension.
ThreeDArray(0, 0, 0) = "Aria Johnson"
ThreeDArray(0, 0, 1) = "Caleb Rodriguez"
ThreeDArray(0, 1, 0) = "Maya Chen"
ThreeDArray(0, 1, 1) = "Liam Campbell"
ThreeDArray(1, 0, 0) = "Liam Campbell"
ThreeDArray(1, 0, 1) = "Ava Nguyen"
ThreeDArray(1, 1, 0) = "Ethan Kim"
ThreeDArray(1, 1, 1) = "Sofia Patel"- String Data is now input in the three-dimensional array.
m = 3- This initializes the variable m with the value 3.
For i = 0 To UBound(ThreeDArray, 1)- This loop iterates from 0 to the upper bound of the first dimension of the ThreeDArray.
For j = 0 To UBound(ThreeDArray, 2)- This loop iterates from 0 to the upper bound of the second dimension of the ThreeDArray.
For k = 0 To UBound(ThreeDArray, 3)- This loop iterates from 0 to the upper bound of the third dimension of the ThreeDArray.
ActiveSheet.Cells(m, 2) = ThreeDArray(i, j, k)
m = m + 1- This assigns the value of the element at the current index in the ThreeDArray to a cell in the active.
Next k- Loop of the innermost for loop ends.
Next j- Loop of the second innermost for loop ends.
Next i- Loop of the outermost for loop ends.
End Sub- End of the subprocedure.
Read More: How to Use UBound on Multidimensional Array with VBA in Excel
Frequently Asked Questions
How to Initialize an array?
You can start or initialize an array using the literals{}. The array elements might be inside the literal, and doing this will automatically add values to the array:
Sub array_with_literals()
' Initialize an array with literals
Dim myArray As Variant
myArray = Array("apple", "banana", "cherry", "dog", "cat", "bird", "red", "green", "blue")
' Output the values of the array to the active worksheet
For i = 0 To UBound(myArray)
ActiveSheet.Cells(i + 1, 1) = myArray(i)
Next i
End SubHow do you initialize a 2D array of objects?
Use the following code given below:
Dim objArray(1 To 3, 1 To 2) As Object
Another example that include looping,
For i = 1 To 3
For j = 1 To 2
Set objArray(i, j) = New SomeObject
Next j
Next iYou also can declare the object properties at the same time, like the code given below:
For i = 1 To 3
For j = 1 To 2
Set objArray(i, j) = New SomeObject
objArray(i, j).Property1 = "Value1"
objArray(i, j).Property2 = "Value2"
' ...
Next j
Next iIn this way, we can add properties with the code.
How do you dynamically declare a 2D array?
Use this code:
Dim myArray() As Variant
Dim numRows As Long
Dim numCols As Long
numRows = 3
numCols = 4
ReDim myArray(1 To numRows, 1 To numCols)The code will re-dimension itself according to the given dimensions.
What is Ubound in a VBA 2D Array?
Ubound is the upper limit of your array. In other words, Ubound means the highest index of the array. For example, if you have an array arr with 5 elements, the UBound(arr) function will return 4, since the highest index number in the array is 4.
In the below code, we can see that the myArray is initialized with 10 separate elements. now the upper limit of the array index is placed in the immediate window.
Sub UpperLimitOfArray()
Dim myArray(1 To 10) As Integer
Debug.Print UBound(myArray)
End Sub
Things to Remember
- Always be careful about the data type of the array to which they are assigned. Wrong data type assigning might create unwanted errors.
- Indexing of arrays always starts from 0, not 1. Wrong indexing might leave empty space in the array and waste space.
- The dynamic array needs to be handled carefully. If the array is re-dimensioned, the previously stored data always gets erased if the redim preserved option is not used.
Download the Practice Workbook
Related Articles
- Excel VBA Multidimensional Array for Assigning Values
- Excel VBA to Declare Multidimensional Array of Unknown Size
