In Excel VBA, multidimensional arrays are a powerful tool for handling complex data structures. They allow you to store and manipulate data in a way that is easy to read and understand. However, sometimes you may not know the exact size of the array you need. In this case, you can declare a multidimensional array of unknown size. In this essay, we will discuss how to use Excel VBA to declare multidimensional array of unknown size.
We can use different statements to declare a multidimensional array of unknown sizes. We can also display the multidimensional array of unknown size in a MsgBox as shown in this video.
Download Practice Workbook
You can download this practice book while going through this article.
How to Open VBA Macro Editor in Excel
You need to follow the steps below to get into the VBA Macro Editor in your worksheet:
- You will see the Developer tab on top of the worksheet. Click on the Developer tab and select Visual Basic. Alternatively, you can press Alt+F11.
- A new window will appear. It is the Visual Basic Editor Window. To write a new code, go to Insert > Module.
- In the module, write the code and click on the Run button to run the code. You can also press the F5 key to run the code.
Excel VBA to Declare Multidimensional Array of Unknown Size: 3 Examples
In this article, we will discuss in detail how to declare a multidimensional array of unknown size. You will show three methods to make such multidimensional arrays. We can use the Redim statement and the ReDim Preserve statement to do so. Moreover, We can create a UserForm to declare multidimensional arrays of unknown size.
Here in the dataset, each row represents a transaction that took place at a coffee shop. The columns provide information about the date and time of the transaction, the name of the item, the price of the item, and the number of items purchased. This dataset could be used to analyze sales trends over time, the popularity of different menu items, and customer buying habits.
1. Use ReDim Statement to Declare Multidimensional Array of Unknown Size
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub ReDim_Statement()
'variable declaration
Dim myArr() As Variant
Dim myRng1, myRng2 As Range
Dim xRow, yCol, zNum As Integer
'take user input
Set myRng1 = Application.InputBox("Select one Column of the table", Type:=8)
Set myRng2 = Application.InputBox("Select one Row of the table", Type:=8)
zNum = InputBox("Insert the number of Tables")
xRow = myRng1.Count
yCol = myRng2.Count
'resize the array with a proper dimension
ReDim myArr(1 To xRow, 1 To yCol, 1 To zNum)
'show the array size
MsgBox "An array has been created" & vbNewLine & _
"Size: " & UBound(myArr, 1) & " by " & _
UBound(myArr, 2) & " by " & UBound(myArr, 3)
End SubVBA BreakDown
Sub ReDim_Statement()
Dim myArr() As Variant
Dim myRng1, myRng2 As Range
Dim xRow, yCol, zNum As Integer- This Excel VBA code defines a subroutine called ReDim_Statement, which takes user input to create a new three-dimensional array of variants. The first few lines of the code declare the variables used in the sub-routine, including the new array, myArr, and two ranges, myRng1, and myRng2.
Set myRng1 = Application.InputBox("Select one Column of the table", Type:=8)
Set myRng2 = Application.InputBox("Select one Row of the table", Type:=8)
zNum = InputBox("Insert the number of Tables")
xRow = myRng1.Count
yCol = myRng2.Count- The myRng1 variable is set to the user-selected column of a table, and myRng2 is set to the user-selected row of the same table. The zNum variable is an integer that specifies the number of tables to create. The next lines of the code calculate the number of rows and columns in the selected table column and row, respectively.
ReDim myArr(1 To xRow, 1 To yCol, 1 To zNum)- The ReDim statement then resizes the myArr array to have the proper dimensions.
MsgBox "An array has been created" & vbNewLine & _
"Size: " & UBound(myArr, 1) & " by " & _
UBound(myArr, 2) & " by " & UBound(myArr, 3)
End Sub- Finally, a MsgBox displays the size of the newly created array. Overall, this code enables users to create an array of unknown sizes and then resize it with specific dimensions based on the dimensions of a selected table.
Read More: Excel VBA Multidimensional Arrays
2. Use ReDim Preserve Statement to Declare Multidimensional Array of Unknown Size
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Option Explicit
Sub Redim_Preserve_Statement()
'variable declaration
Dim myArr() As Variant
'resize the array with a proper dimension
ReDim myArr(1 To 3, 1 To 2)
'put elements in array
myArr(1, 1) = "Wheat Muffin"
myArr(2, 1) = "Bran Muffin"
myArr(3, 1) = "Banana"
myArr(1, 2) = 4
myArr(2, 2) = 3
myArr(3, 2) = 3.5
'resize the array with a proper dimension
ReDim Preserve myArr(1 To 3, 1 To 3)
myArr(1, 3) = 100
myArr(2, 3) = 50
myArr(3, 3) = 75
'show array elements
Range("D15:F17").Value = myArr
End SubVBA BreakDown
Option Explicit
Sub Redim_Preserve_Statement()
Dim myArr() As Variant
ReDim myArr(1 To 3, 1 To 2)- This Excel VBA code defines a subroutine called Redim_Preserve_Statement, which demonstrates the use of the ReDim Preserve statement to change the size of an existing array without losing its current contents. The first few lines of the code declare a new array, myArr, which is initially sized to 3 rows and 2 columns.
myArr(1, 1) = "Wheat Muffin"
myArr(2, 1) = "Bran Muffin"
myArr(3, 1) = "Banana"
myArr(1, 2) = 4
myArr(2, 2) = 3
myArr(3, 2) = 3.5- The following lines of the code assign values to specific elements of the array.
ReDim Preserve myArr(1 To 3, 1 To 3)
myArr(1, 3) = 100
myArr(2, 3) = 50
myArr(3, 3) = 75- The code then uses the ReDim Preserve statement to resize the array to have 3 rows and 3 columns while preserving the values in the first two columns.
Range("D15:F17").Value = myArr
End Sub- The final lines of the code display the contents of the array in the Excel worksheet by assigning them to a range of cells. Overall, this code demonstrates how to use the ReDim Preserve statement to modify an array’s size while keeping its existing data.
Read More: Excel VBA Multidimensional Array for Assigning Values (6 Ways)
Similar Readings
- Excel VBA to Read CSV File into Array (4 Ideal Examples)
- How to Use Arrays Instead of Ranges in Excel VBA
- Excel VBA: Remove Duplicates from an Array (2 Examples)
- 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)
3. Create Userform to Declare Multidimensional Array of Unknown Size
Just follow the steps below to create a UserForm that declares a multidimensional array of unknown size:
- Go to Insert > UserForm.
- Create a UserForm. You can take a Label to write anything. You can write the directions here. Then, take RefEdit to take a range of cells by the user just like the InputBox. You can also take a CommandButton that works just like an InputBox button. You can change the properties of these tools from the Properties Window (press F4 to view the window).
- Click on the CommandButton twice and a new window will appear. Put the following code in the window:
Private Sub CommandButton1_Click()
'variable declaration
Dim myRng As Range
Dim nRow, nCol, mySum, multiply As Integer
Dim myArr() As Variant
mySum = 0
Set myRng = Range(RefEdit1.Text)
nRow = myRng.Rows.Count
nCol = myRng.Columns.Count
'resize the array with a proper dimension
ReDim myArr(1 To nRow, 1 To nCol)
'put elements in the array
For i = 1 To nRow
For j = 1 To nCol
myArr(i, j) = myRng.Cells(i, j)
Next j
Next i
'do calculation
For i = 1 To nRow
multiply = myArr(i, 1) * myArr(i, 2)
mySum = mySum + multiply
Next i
'show the result
MsgBox "Total Revenue: " & vbCr & mySum
End Sub- Now, run the UserForm to see the results.
VBA BreakDown
Private Sub CommandButton1_Click()
Dim myRng As Range
Dim nRow, nCol, mySum, multiply As Integer
Dim myArr() As Variant
mySum = 0- This Excel VBA code defines a subroutine called CommandButton1_Click, which is executed when a user clicks on a CommandButton in the worksheet. The code begins by declaring several variables, including a range object myRng, and integers nRow, nCol, mySum, and multiply.
Set myRng = Range(RefEdit1.Text)
nRow = myRng.Rows.Count
nCol = myRng.Columns.Count- The code then sets the myRng range object based on the contents of the text box, RefEdit1, and determines the number of rows and columns in the range.
ReDim myArr(1 To nRow, 1 To nCol)
For i = 1 To nRow
For j = 1 To nCol
myArr(i, j) = myRng.Cells(i, j)
Next j
Next i- The code uses the ReDim statement to resize the myArr array to the same size as the range, and fills the array with the values of the range.
For i = 1 To nRow
multiply = myArr(i, 1) * myArr(i, 2)
mySum = mySum + multiply
Next i
MsgBox "Total Revenue: " & vbCr & mySum
End Sub- The code then uses a loop to calculate the product of each row in the array and adds the results to the mySum variable. Finally, the code displays the total revenue in a MsgBox. Overall, this code demonstrates how to declare arrays of unknown size to perform calculations on a range of values, and how to display the results to the user.
Read More: How to Use UBound on Multidimensional Array with VBA in Excel
How to Declare One Dimensional Array of Unknown Size in Excel VBA
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub One_Dimesional()
'variable declaration
Dim myRng As Range
Dim nCell As Integer
Dim myArr() As Variant
'take user input
Set myRng = Application.InputBox("Please select the items", Type:=8)
nCell = myRng.Count
'resize the array with a proper dimension
ReDim myArr(1 To nCell)
'put elements in array
For i = 1 To myRng.Cells.Count
myArr(i) = myRng.Cells(i)
Next i
'show array elements
For j = LBound(myArr) To UBound(myArr)
MsgBox "Item number " & j & " is " & myArr(j)
Next j
End SubVBA BreakDown
Sub One_Dimesional()
Dim myRng As Range
Dim nCell As Integer
Dim myArr() As Variant
Set myRng = Application.InputBox("Please select the items", Type:=8)
nCell = myRng.Count- This VBA code declares a subroutine called One_Dimensional. It begins by declaring variables to store the user input and the array. The code then prompts the user to select a range of cells and counts the number of cells in the range.
For j = LBound(myArr) To UBound(myArr)
MsgBox "Item number " & j & " is " & myArr(j)
Next j
End Sub- Then, it resizes the array to that number. Next, it populates the array with the cell values from the selected range.
For j = LBound(myArr) To UBound(myArr)
MsgBox "Item number " & j & " is " & myArr(j)
Next j
End Sub- Finally, it uses a For Loop to display the array elements in a MsgBox, indicating the item number and its corresponding value. The code thus allows users to easily extract and display data from a range of cells in a clear and organized manner.
Read More: How to Create an Array in Excel VBA (4 Ideal Methods)
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)
How to Show a 2D Array in MsgBox with Excel VBA
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub MsgBox_Array()
'variable declaration
Dim myRng As Range
Dim nRow As Integer
Dim myArr() As Variant
Dim myMsg As String
Set myRng = Application.InputBox("Select two columns from the table", Type:=8)
nRow = myRng.Rows.Count
'resize the array with a proper dimension
ReDim myArr(1 To nRow, 1 To 2)
'put elements in array
For i = 1 To nRow
For j = 1 To 2
myArr(i, j) = myRng.Cells(i, j)
Next j
Next i
'show array elements
For i = LBound(myArr, 1) To UBound(myArr, 1)
For j = LBound(myArr, 2) To UBound(myArr, 2)
myMsg = myMsg & myArr(i, j) & vbTab
Next j
myMsg = myMsg & vbCrLf
Next i
MsgBox myMsg
End SubVBA BreakDown
Sub MsgBox_Array()
Dim myRng As Range
Dim nRow As Integer
Dim myArr() As Variant
Dim myMsg As String
Set myRng = Application.InputBox("Select two columns from the table", Type:=8)
nRow= myRng.Rows.Count- This Excel VBA code starts with a sub procedure named MsgBox_Array. It declares variables such as myRng as Range, nRow as Integer, myArr as a Variant array, and myMsg as a String. It prompts the user to select two columns from the table using the InputBox function.
ReDim myArr(1 To nRow, 1 To 2)
For i = 1 To nRow
For j = 1 To 2
myArr(i, j) = myRng.Cells(i, j)
Next j
Next i- The code resizes the array dimension to fit the selected data. The For Loop then iterates through the selected range and assigns the values to the array.
For i = LBound(myArr, 1) To UBound(myArr, 1)
For j = LBound(myArr, 2) To UBound(myArr, 2)
myMsg = myMsg & myArr(i, j) & vbTab
Next j
myMsg = myMsg & vbCrLf
Next i
MsgBox myMsg
End Sub- After this, the code concatenates the array elements to the myMsg string using the vbTab and vbCrLf constants. Finally, a MsgBox displays the concatenated string to show the array elements.
Read More: How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
How to Find the Size of an Array in Excel VBA
Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:
Sub Array_Size()
'variable declaration
Dim myArr(1 To 5, 1 To 4) As String
Dim xDim, yDim As Integer
'take array size
xDim = UBound(myArr, 1) - LBound(myArr, 1) + 1
yDim = UBound(myArr, 2) - LBound(myArr, 2) + 1
'show array size
MsgBox "This array consists of " & xDim & " by " & yDim & " elements"
End SubVBA BreakDown
Sub Array_Size()
Dim myArr(1 To 5, 1 To 4) As String
Dim xDim, yDim As Integer- This Excel VBA code defines a sub-procedure named Array_Size. The procedure initializes a two-dimensional string array named myArr with five rows and four columns. It then declares two integer variables named xDim and yDim.
xDim = UBound(myArr, 1) - LBound(myArr, 1) + 1
yDim = UBound(myArr, 2) - LBound(myArr, 2) + 1- The values of xDim and yDim are calculated based on the upper and lower bounds of myArr.
MsgBox "This array consists of " & xDim & " by " & yDim & " elements"
End Sub- Finally, a MsgBox is displayed showing the dimensions of myArr. Overall, this code demonstrates how to declare and manipulate a two-dimensional array in VBA, and how to obtain its size using the UBound and LBound functions.
Read More: Excel VBA Array of Strings (4 Examples)
Things to Remember
When declaring a multidimensional array of unknown size in Excel VBA, there are several things to keep in mind.
- The array elements are indexed from 0 positions by default unless the position is specified.
- Use the Redim statement only to resize the dimensions of the array.
- You must use the Redim Preserve statement to preserve existing values in the array.
Frequently Asked Questions
1. How do I declare a multidimensional array in VBA?
You can declare a multidimensional array in Excel VBA by using the following syntax:
Dim myArray( 1 to numRows, 1 to numColumns) as DataType. Here, numRows and numColumns are the numbers of rows and columns in the array, respectively, and DataType is the data type of the array elements.
2. Can I declare an array without specifying the size?
Yes, as shown in this article, you can declare an array without specifying its size by omitting the size specification in the Dim statement.
3. What is the maximum size of a multidimensional array in Excel VBA?
The maximum size of a multidimensional array in VBA is determined by the amount of available memory in the system. VBA arrays are limited by the amount of contiguous memory available, which is dependent on several factors, such as the operating system, the amount of RAM installed, and the number and size of other running processes. In practice, the maximum size of a multidimensional array in VBA can vary widely depending on these factors. It is generally recommended to use smaller arrays when possible to avoid memory allocation errors or slow performance.
Conclusion
In this article, we have discussed in detail how to use Excel VBA to declare multidimensional array of unknown size. There are several methods to declare arrays in such a way. We have demonstrated those methods here.
This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, Exceldemy.com, and unlock a great resource for Excel-related content.
Related Articles
- How to Redim 2D Array with VBA in Excel (3 Examples)
- Excel VBA 2 Dimensional Array Initialization
- How to Find Lookup Value in Array in Excel VBA (2 Easy Ways)
- Excel VBA Array of Arrays (3 Examples)
- How to Declare Array in Excel VBA (3 Easy Ways)
- VBA Read Text File into Array (2 Suitable Methods)
- How to Check If Array Is Empty with VBA in Excel (3 Variants)
