We can obviously initialize various types of two-dimensional arrays in Excel, not to mention our selection can be expanded up to three dimensions too. Initialization can be a bit tricky, as wrong initialization could potentially create various types of errors. In this article, we’ll explain how we can properly do Excel VBA 2-dimensional array Initialization. We’ll show the usage of them with examples.
We present an overview of a small code with a sample dataset demonstrating what we are doing in this article.
Overview of 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 datatypeHere, we can see that a 2 dimensional is now declared and defined. Here the size of the array in both directions is defined as 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, store the string “random” in the array array name (5).
Dim arrayName(rowSie, colSize) as variant
arrayName(5)="random"- Show data
We can show data stored in the array in three different ways, we can show them in the immediate window, through the message box, or in the worksheet cell. Each of them is demonstrated below.
To show the value in the immediate window, we can use the code
Debug.Print arrayName(5)For showing in the message box,
Messegebox arrayName(5)To show the values in the worksheet
activesheet.cells(5,5).values= arrayName(5)Example of Arrays in Excel
When we talk about arrays, the dimensions can be 1-dimensional, 2-dimensional, or 3-dimensional. Below we are going to present examples from each type of code.
For 1 dimensional array, you can follow the procedure in the video.
The code we use 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, we can follow the below code, this code will initialize the 2 dimensional array and then print them 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 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. As shown in the video below,
- After entering the Developer tab, tango to Insert > Module, you will see an editor where you can put the necessary code.
Note:
In any case, 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
1. Static 2-Dimensional Array
Static array means the arrays that can not be resized and redimensioned in the middle of the code, and they need to be specifically dimensionalized. We provided an example of such code below.
Steps:
- Below we presented a code that contains a static array, in the static array we need to enter the values in the array one by one.
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”.
2. Dynamic 2-Dimensional Array
Below we presented a 2-dimensional array that can be used dynamically resized to fit our demand.
Steps:
Below we are presenting a two-dimensional array with dynamic functionality.
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 Two-Dimensional Array in Excel VBA
We can extract data from the worksheet to the array. For this, we present the below 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 Multidimensional Array at Once in Excel VBA
Below we presented a code that will present the 3-dimensional code all at once. After the initialization of the code, the information is then pasted to the worksheet.
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 Question
1. 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. Like the code given below,
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 Sub2. How Do You Initialize a 2D Array of Objects?
Obviously, you can open an array that has objects type in VBA For this, you can follow 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 iWe 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.
3. How do you dynamically declare a 2D array?
Obviously, you can dynamically declare the 2D array, and re-dimension, like the code given below,
Dim myArray() As Variant
Dim numRows As Long
Dim numCols As Long
numRows = 3
numCols = 4
ReDim myArray(1 To numRows, 1 To numCols)Here we can see that the code now redimension itself on its own according to the given dimensions.
4. What is Ubound in VBA 2D Array?
Ubound simply means 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 Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
In this article, we have shown how you can initialize 2 dimensional in various situations. Use them for your own needs and modify them to your preferences.
Related Articles
- Excel VBA Multidimensional Array for Assigning Values
- Excel VBA to Declare Multidimensional Array of Unknown Size
