Today, we are going to discuss how to use the UBound function on a multidimensional array with VBA. This function is a very commonly used Array function in VBA. This allows users to get the maximum dimension and indexes of an array. In this article, we will look at some of the usage of this function with VBA codes, along with a breakdown.
The above overview video shows the use of UBound in the code to display the array dimensions.
Download Practice Workbook
Download this practice workbook
How to Launch VBA Macro Editor in Excel
In order to run any VBA code, we first need to write or edit the code in the VBA Macro Editor. Follow the simple steps below to open up the VBA Macro Editor.
- Go to Developer Tab >> Visual Basic.
- This will open the Visual Basic window.
- Select Insert >> Module in the macro editor.
- As a result, an empty module will appear on the screen where you can write the code.
UBound on Multidimensional Array with VBA in Excel: 4 Examples
In this article, we will show four uses of the UBound function on a multidimensional array. First, we will get the dimensions of a multidimensional array with this function. Then, we will get the maximum index of an array using this function. We will also write VBA code to show an entire array in a MsgBox using this function. Finally, we will transpose an array with the help of the UBound function.
Example 1. Applying UBound Function in VBA to Get Array Dimensions
The dimension of an array usually refers to the number of directions or axes an array can store its value. If an array stores the values of the names of some students, then it is a 1-D array. If it stores the marks of the students in different subjects, then it is a 2-D array, and so on. So, it is important to know the dimensions of an array. The following code defines a multidimensional array and then displays its dimensions in the Immediate Window.
Sub ArrDimension()
'declaring variable and type
Dim ArrCollection As New Collection
'declaring a 3D array
Dim Arr(1 To 3, 2 To 8, 3 To 10) As Variant
'error handling
On Error GoTo Output
i = 1
'running while loop to add array dimension to a collection
Do While True
ArrDim = Array(LBound(Arr, i), UBound(Arr, i))
ArrCollection.Add ArrDim
i = i + 1
Loop
'error handling
Output:
'showing output in immediate window
Debug.Print "The ""Arr"" Array has " & ArrCollection.Count & " dimensions"
End SubVBA Breakdown:
This VBA code declares and initializes a 3-dimensional array named Arr and then adds each dimension of the array to a Collection object named ArrCollection. Finally, it displays the number of dimensions in the array in the Immediate Window.
Dim ArrCollection As New CollectionDeclares a new collection named ArrCollection.
Dim Arr(1 To 3, 2 To 8, 3 To 10) As VariantDeclares a 3-dimensional array named Arr with the following dimensions: 1 to 3, 2 to 8, and 3 to 10.
On Error GoTo OutputSets the error handling routine to the Output label.
i = 1
Do While True
ArrDim = Array(LBound(Arr, i), UBound(Arr, i))
ArrCollection.Add ArrDim
i = i + 1
LoopHere, we start a While Loop with variable i and initial value of i is 1. Inside the while loop we create a new array variable Arrdim and store the upper and lower indexes of each dimension of the Arr array inside it. Then, we add the array inside the ArrCollection variable which is a Collection object.
Output:
Debug.Print "The ""Arr"" Array has " & ArrCollection.Count & " dimensions"
End SubThe error handling routine jumps to the Output label when the While Loop ends and prints the output in the Immediate Window.
- As the output image suggests, as soon as we press the run button, the code will show the number of dimensions of the Arr array in the Immediate Window.
Read More: Excel VBA to Declare Multidimensional Array of Unknown Size
Example 2. Using UBound Function to Extract Maximum Index of Multidimensional Array
The index of an array is a value that will be used to access a particular element of the array. The maximum index of an array is the value by which we can access the last value of the array. It also refers to the size of the array. In a 2-D array, we will get two maximum indexes: one for the rows and another for the columns. By multiplying these indexes, we can get the total number of elements that the array holds. The following code replicates that task.
Sub ArrIndx()
'defining a range variable
Set Rng = Range("C5:E12")
'defining array and redim array indexes
Dim Arr() As Variant
ReDim Arr(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)
'running for loop to add the values from the range into the array
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Arr(i, j) = Rng.Cells(i, j)
Next j
Next i
'extracting maximum row and column values
max_row = UBound(Arr, 1)
max_col = UBound(Arr, 2)
'calculating total values inside the array
marks_num = max_row * max_col
'output in a MsgBox
MsgBox "The Dataset has " & marks_num & " marks"
End SubVBA Breakdown:
This VBA code extracts the marks of some students from a range of cells in a worksheet and stores them in a 2-dimensional array named Arr. It then calculates the total number of data points in the array and displays the result in a message box, which is the total number of marks in different subjects in that worksheet.
Sub ArrIndx()
Set Rng = Range("C5:E12")
Dim Arr() As Variant
ReDim Arr(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)In this case, we define a subroutine called ArrIndx to get the maximum indexes of a multidimensional array. Then, set the C5:E12 range of the Index worksheet to a variable Rng. We also declare an array variable Arr and set its row index from 1 to the number of rows that the range has and its column index from 1 to the number of columns that the range possesses.
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Arr(i, j) = Rng.Cells(i, j)
Next j
Next iIn this section, we run a For Loop to add each value of the range into our Arr array.
max_row = UBound(Arr, 1)
max_col = UBound(Arr, 2)
marks_num = max_row * max_colIn this section of the code, we get the maximum row and column numbers of the Arr array by using the UBound function. Then, if we multiply them, we will get the total number of elements inside the Arr array, which is also the total number of marks that the sheet contains.
MsgBox "The Dataset has " & marks_num & " marks"This line shows the total number of marks that the sheet holds in a MsgBox.
- As seen from the image above, as soon as we run the code,a MsgBox will display the total number of marks that the range contains (marked by a red rectangle).
Read More: Excel VBA Multidimensional Array for Assigning Values (6 Ways)
Similar Readings
- How to Sort Array with Excel VBA (Both Ascending and Descending Order)
- Excel VBA to Create Data Validation List from Array
- How to Convert Range to Array in Excel VBA (3 Ways)
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- How to Split a String into an Array in VBA (3 Effective Ways)
Example 3. Utilizing UBound Function to Show Multidimensional Array in MsgBox
The following code takes the value of a range in an array and displays the entire array in a MsgBox.
Sub Array_in_MsgBox()
'declaring variables and their types
Dim Rng As Range
Dim nRow As Integer
Dim Arr() As Variant
Dim ArrMsg As String
'asking users to choose range to display into a MsgBox
Set Rng = Application.InputBox("Select any range from the table", Type:=8)
'finding last row and column of the range
nRow = Rng.Rows.Count
nCol = Rng.Columns.Count
'redimensioning array
ReDim Arr(1 To nRow, 1 To nCol)
'running for loop to take the values of the range into the array
For i = 1 To nRow
For j = 1 To nCol
Arr(i, j) = Rng.Cells(i, j)
Next j
Next i
'running two for loops to show the selected range value into a MsgBox
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
myMsg = myMsg & Arr(i, j) & vbTab
Next j
myMsg = myMsg & vbCrLf
Next i
'showing output in a MsgBox
MsgBox myMsg
End SubVBA Breakdown:
In this code, we prompt the users to select a range from a worksheet, and then we take that range and put it in an array called Arr and display it in a MsgBox.
Dim Rng As Range
Dim nRow As Integer
Dim Arr() As Variant
Dim ArrMsg As StringDeclaring variables with their data type.
Set Rng = Application.InputBox("Select any range from the table", Type:=8)
nRow = Rng.Rows.Count
nCol = Rng.Columns.Count
ReDim Arr(1 To nRow, 1 To nCol)Prompting users to select a data range from the worksheet and set the value as a Range variable with the Type:=8 command. Then, set the value of the nRow and nCol variables to the number of rows and columns that the range selection has, respectively. Finally, we redimensionalize the Arr array according to the values of the nRow and nCol variables.
For i = 1 To nRow
For j = 1 To nCol
Arr(i, j) = Rng.Cells(i, j)
Next j
Next iRunning a For Loop to take the values of the range selection inside the Arr array.
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
myMsg = myMsg & Arr(i, j) & vbTab
Next j
myMsg = myMsg & vbCrLf
Next iThis code is a nested for loop that iterates through each row and column of the 2D array Arr. The outer loop uses the LBound and UBound functions to determine the lower and upper bounds of the first dimension of Arr, which corresponds to the rows. The inner loop does the same for the second dimension of Arr, which corresponds to the columns. Within the inner loop, the code concatenates the value at the current row and column of Arr to the string variable myMsg, along with a horizontal tab character (vbTab). After the inner loop completes each row, the code adds a vertical tab character and a carriage return (vbCrLf) to the end of myMsg. This effectively creates a string that represents the values in Arr as a table, where each row is separated by a new line and each column is separated by a horizontal tab. The final myMsg string is then displayed in a message box.
MsgBox myMsgThe MsgBox shows the message, which is the table created from the array.
- Click on the green triangle in the VBA module.
- A MsgBox will display the values of the selected region (marked with a red rectangle).
Read More: How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
Example 4. Using UBound Function to Transpose Multidimensional Array
In this example, we will transpose an array using the UBound function. The following code takes a range inside an array and then transposes it in another cell in the sheet.
Sub Transpose_Array()
'asking users to select a range
Set Rng = Application.InputBox("Select a range of dataset", Type:=8)
'defining array and redimensioning it
Dim Arr1() As Variant
ReDim Arr1(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)
'running for loop to take the values of the range into the array
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Arr1(i, j) = Rng.Cells(i, j)
Next j
Next i
'taking a new array to store the transposed value
Dim Arr2() As Variant
ReDim Arr2(1 To Rng.Columns.Count, 1 To Rng.Rows.Count)
'running for loop to store the transposed value in the Arr2 array
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Arr2(j, i) = Arr1(i, j)
Next j
Next i
'asking users to choose a cell to insert the transposed value
Set Rng2 = Application.InputBox("Select a cell for inserting the transposed array", Type:=8)
'inserting transposed array into excel sheet
For i = LBound(Arr2, 1) To UBound(Arr2, 1)
For j = LBound(Arr2, 2) To UBound(Arr2, 2)
Rng2.Cells(i, j) = Arr2(i, j)
Next j
Next i
End SubVBA Breakdown:
Set Rng = Application.InputBox("Select a range of dataset", Type:=8)
'defining array and redimensioning it
Dim Arr1() As Variant
ReDim Arr1(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)
'running for loop to take the values of the range into the array
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Arr1(i, j) = Rng.Cells(i, j)
Next j
Next iIt asks users to select a range and store it in a variable named Rng. Then, defines an array named Arr1 and sets its indexes according to the size of the range selected. Then, the For Loop stores the values of the range inside the array.
Dim Arr2() As Variant
ReDim Arr2(1 To Rng.Columns.Count, 1 To Rng.Rows.Count)
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Arr2(j, i) = Arr1(i, j)
Next j
Next iSets a new array variable called Arr2 and sets its indexes from the same selected range as the Arr1 array, but switches the rows and columns numbers. Then, the for loop takes the values of the Arr1 array and stores them inside the Arr2 variable, where the row of Arr1 is the column of Arr2 and vice versa. This will transpose the Arr1 array and store it inside the Arr2 array.
Set Rng2 = Application.InputBox("Select a cell for inserting the transposed array", Type:=8)
'inserting transposed array into excel sheet
For i = LBound(Arr2, 1) To UBound(Arr2, 1)
For j = LBound(Arr2, 2) To UBound(Arr2, 2)
Rng2.Cells(i, j) = Arr2(i, j)
Next j
Next i
End SubThis code asks the users to select a cell from which they will paste the transposed array. Then, the For Loop inserts the values of the Arr2 array in the worksheet.
- We run the code from the VBA module.
- Select the range we want to transpose the value of.
- Select the cell where we want to keep the transposed values.
- The transposed values are shown inside a red rectangle.
Read More: Excel VBA to Transpose Array (3 Suitable Examples)
How to Resize Multidimensional Array in Excel VBA
We often need to add another row or column to our existing array. However, the size of the array does not allow users to add values. So, we need to resize it accordingly. In the following VBA code, we will show how to resize a multidimensional array.
Sub ArrResize()
'declaring an array variable
Dim Arr() As Variant
'resizing array
ReDim Arr(3, 2)
'adding values to the array
Arr(0, 0) = "Name"
Arr(0, 1) = "Physics"
Arr(0, 2) = "Math"
Arr(1, 0) = "Adam"
Arr(1, 1) = 85
Arr(1, 2) = 90
Arr(2, 0) = "Jhon"
Arr(2, 1) = 58
Arr(2, 2) = 70
Arr(3, 0) = "Mira"
Arr(3, 1) = 81
Arr(3, 2) = 65
'running for loop to show the array data in a MsgBox
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
myMsg = myMsg & Arr(i, j) & vbTab
Next j
myMsg = myMsg & vbCrLf
Next i
'Output
MsgBox myMsg
'resizing array to add another row
ReDim Arr(4, 2)
'initializing the values again with new values
Arr(0, 0) = "Name"
Arr(0, 1) = "Physics"
Arr(0, 2) = "Math"
Arr(1, 0) = "Adam"
Arr(1, 1) = 85
Arr(1, 2) = 90
Arr(2, 0) = "Jhon"
Arr(2, 1) = 58
Arr(2, 2) = 70
Arr(3, 0) = "Mira"
Arr(3, 1) = 81
Arr(3, 2) = 65
Arr(4, 0) = "Alisa"
Arr(4, 1) = 64
Arr(4, 2) = 78
'running for loop to show the array data in a MsgBox
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
NewMsg = NewMsg & Arr(i, j) & vbTab
Next j
NewMsg = NewMsg & vbCrLf
Next i
'Output
MsgBox NewMsg
End SubVBA Breakdown:
In this case, the code declares an array of a particular size and then resizes it to add an additional element to it.
Sub ArrResize()
Dim Arr() As Variant
ReDim Arr(3, 2)
Arr(0, 0) = "Name"
Arr(0, 1) = "Physics"
Arr(0, 2) = "Math"
Arr(1, 0) = "Adam"
Arr(1, 1) = 85
Arr(1, 2) = 90
Arr(2, 0) = "Jhon"
Arr(2, 1) = 58
Arr(2, 2) = 70
Arr(3, 0) = "Mira"
Arr(3, 1) = 81
Arr(3, 2) = 65
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
myMsg = myMsg & Arr(i, j) & vbTab
Next j
myMsg = myMsg & vbCrLf
Next i
MsgBox myMsgHere, we declare a 3 by 2 array named Arr. Then, we set the value of each element of that array. Finally, the For Loop shows the array in a MsgBox like one of the previous methods above.
ReDim Arr(4, 2)
Arr(0, 0) = "Name"
Arr(0, 1) = "Physics"
Arr(0, 2) = "Math"
Arr(1, 0) = "Adam"
Arr(1, 1) = 85
Arr(1, 2) = 90
Arr(2, 0) = "Jhon"
Arr(2, 1) = 58
Arr(2, 2) = 70
Arr(3, 0) = "Mira"
Arr(3, 1) = 81
Arr(3, 2) = 65
Arr(4, 0) = "Alisa"
Arr(4, 1) = 64
Arr(4, 2) = 78
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = LBound(Arr, 2) To UBound(Arr, 2)
NewMsg = NewMsg & Arr(i, j) & vbTab
Next j
NewMsg = NewMsg & vbCrLf
Next i
MsgBox NewMsg
End SubIn this section, we resize the Arr array and set it as a 4 by 2 array. Then, we reinitialize the previous value with the addition of 3 new values, which are Arr(4,0), Arr(4,1), and Arr(4,2). Then, again, we display the whole array in a MsgBox with the For Loop.
- Run the code.
- A MsgBox will display a 3 by 2 array.
- After clicking OK, a new MsgBox will add another row to the array after resizing it to a 4 by 2 array.
Similar Readings
- VBA Read Text File into Array (2 Suitable Methods)
- Excel VBA to Populate Array with Cell Values (4 Suitable Examples)
- How to Use Arrays Instead of Ranges in Excel VBA
- Excel VBA: Determine Number of Elements in Array (4 Examples)
- How to Create an Array in Excel VBA (4 Ideal Methods)
How to Use the VBA UBound Function in 2D Array in Excel
Excel Sheets only supports 2D array since we can visualize the datasets in two dimensions. In this section, we will use the UBound function in 2D arrays in two distinct examples.
Example 1: Finding Prime Numbers Within a Range Using the UBound Function
A prime number is a number that can only be divided by the number itself and one. For different mathematical operations, we often need to know if a number is prime or not prime. In this example, the following code will find all the prime numbers within a range specified by the user.
Sub primeNumbers()
'declaring variables
Dim numArray() As Integer
ReDim numArray(0)
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim isPrime As Boolean
n = -1
'prompting users to define the range
nLow = Int(InputBox("Please Enter the number from which you want to start searching for primes"))
nHigh = Int(InputBox("Please Enter the number at which you want to end searching for primes"))
'running For loop and conditional statements to find prime numbers and storing them in an array
For i = nLow To nHigh
isPrime = True
For j = 2 To (i - 1) / 2
If i Mod j = 0 Then
isPrime = False
Exit For
End If
Next j
If isPrime Then
n = n + 1
ReDim Preserve numArray(n)
numArray(n) = i
End If
Next i
'printing the values inside the numArray in a Msgbox
myMsg = ""
For i = 0 To UBound(numArray)
myMsg = myMsg & numArray(i) & ","
Next i
MsgBox "There are " & UBound(numArray) + 1 & " primes " & " between " & nLow & " and " & nHigh & ". " & " They are: " & Left(myMsg, Len(myMsg) - 1)
End SubVBA Breakdown:
For i = nLow To nHigh
isPrime = True
For j = 2 To (i - 1) / 2
If i Mod j = 0 Then
isPrime = False
Exit For
End If
Next j
If isPrime Then
n = n + 1
ReDim Preserve numArray(n)
numArray(n) = I
End If
Next iThis code is checking for prime numbers in a given range, from nLow to nHigh.
The breakdown of code inside the For Loop is as follows:
- Initialize a Boolean variable isPrime to True.
- For each value of i from nLow to nHigh: Check if i is prime by testing whether it is divisible by any integer between 2 and (i – 1) / 2.
- If i is not prime, set isPrime to False and exit the inner loop with the Exit For statement.
- If i is prime, increase the size of numArray by one and store the prime number i in the last element of the array.
- Repeat the loop for the next value of i until all numbers in the range have been tested.
myMsg = ""
For i = 0 To UBound(numArray)
myMsg = myMsg & numArray(i) & ","
Next iMsgBox “There are ” & UBound(numArray) + 1 & ” primes ” & ” between ” & nLow & ” and ” & nHigh & “. ” & ” They are: ” & Left(myMsg, Len(myMsg) – 1)
- This portion of the code runs a For Loop through all the elements of the numArray that contains all the prime numbers within the given range. Then, add each number to an empty string called myMsg separated by commas.
- Finally, it prints all the numbers in a MsgBox.
- Run the Code.
- Insert the lower bound of the range from which you want to search for prime numbers.
- Enter the upper bound.
- A MsgBox will display all the prime numbers.
Read More: How to Redim 2D Array with VBA in Excel (3 Examples)
Example 2: Finding the Maximum Value of a 2D Array Using the UBound Function
Here, we have a dataset containing the marks for three subjects for several students. We will select a range of marks from this dataset and pass them into a 2D array. Then, we will find out the maximum value of that array.
The following code will be used to do the task.
Sub Maximum_Marks()
Dim Input_Range As Range
Dim Max_Numbers() As Variant
Set Input_Range = Application.InputBox("Enter a range whose highest value you want to find", Type:=8)
nRows = Input_Range.Rows.Count
nCols = Input_Range.Columns.Count
ReDim Max_Numbers(1 To nRows, 1 To nCols)
Max_Numbers = Input_Range.Value
Highest_Marks = Max_Numbers(1, 1)
For i = 1 To UBound(Max_Numbers, 1)
For j = 1 To UBound(Max_Numbers, 2)
If Max_Numbers(i, j) > Highest_Marks Then
Highest_Marks = Max_Numbers(i, j)
Else
Highest_Marks = Highest_Marks
End If
Next j
Next i
For Each Cell In Input_Range
If Cell = Highest_Marks Then
Cell.Interior.Color = vbGreen
End If
Next Cell
MsgBox "The highest marks is " & Highest_Marks
End SubVBA Breakdown:
Dim Input_Range As Range
Dim Max_Numbers() As Variant
Set Input_Range = Application.InputBox("Enter a range whose highest value you want to find", Type:=8)
nRows = Input_Range.Rows.Count
nCols = Input_Range.Columns.Count
ReDim Max_Numbers(1 To nRows, 1 To nCols)- The code declares two variables named “Input_Range” and “Max_Numbers” with type Range and Variant respectively.
- Then, it asks users to set the range from which the code will find the maximum value.
- Finally, the variables “nRows” and “nCols” store the maximum value of the rows and columns of the selection and resizes the “Max_Numbers” array’s index accordingly.
Max_Numbers = Input_Range.Value
Highest_Marks = Max_Numbers(1, 1)
For i = 1 To UBound(Max_Numbers, 1)
For j = 1 To UBound(Max_Numbers, 2)
If Max_Numbers(i, j) > Highest_Marks Then
Highest_Marks = Max_Numbers(i, j)
Else
Highest_Marks = Highest_Marks
End If
Next j
Next i- The first line sets the Max_Numbers array equal to the value of the Input_Range. This copies the values of each cell in the Input_Range into the Max_Numbers array.
- The second line initializes the Highest_Marks variable to the value of the first cell in the Max_Numbers array (i.e., the cell in the first row and first column).
- The nested For loops iterate through each cell in the Max_Numbers array. The outer loop iterates over the rows of the array (i.e., the first dimension), and the inner loop iterates over the columns of the array (i.e., the second dimension).
- The If statement inside the inner loop checks if the current cell’s value is greater than the current value of Highest_Marks. If it is, then the current cell’s value becomes the new value of Highest_Marks.If the current cell’s value is less than or equal to the current value of Highest_Marks, then the value of Highest_Marks remains unchanged.
- Once the inner loop has finished iterating over all columns for a given row, the outer loop moves to the next row, and the inner loop starts again. This continues until the outer loop has finished iterating over all rows.
After these steps, the Highest_Marks variable contains the highest value in the Max_Numbers array.
For Each Cell In Input_Range
If Cell = Highest_Marks Then
Cell.Interior.Color = vbGreen
End If
Next Cell
MsgBox "The highest marks is " & Highest_Marks- The For loop searches through the selected range to match the value of the variable “Highest_Marks” and marks the cell with green that contains the value.
- Finally, a MsgBox displays the value.
- Run the code from the Macros tab.
- Choose a range whose highest value you want to find.
- The code will highlight and display the highest value in the range.
Read More: Excel VBA 2 Dimensional Array Initialization
Frequently Asked Questions
- What is a Multidimensional Array?
As in the rows and columns of a spreadsheet or the cube in three-dimensional space, a multidimensional array is a type of array data structure that may hold values arranged in various dimensions or levels. It is, in other terms, a matrix of values or an array of arrays.
For instance, a two-dimensional array can be seen as a grid or table, with each element being identified by a pair of coordinates or indices that show where it is in the rows and columns. A three-dimensional array is analogous to a cube in that each element is identified by a set of three coordinates corresponding to its location along the x, y, and z axes.
- How to Declare a Multidimensional Array?
To declare a multidimensional array in VBA, you can use the following syntax:
Dim arrayName(lowerbound1 To upperbound1, lowerbound2 To upperbound2, ..., lowerboundN To upperboundN) As dataTypeHere, arrayName is the name you want to give to the array, lowerbound1 and upperbound1 are the lower and upper bounds of the first dimension, lowerbound2, and upperbound2 are the lower and upper bounds of the second dimension, and so on for each additional dimension up to N. dataType is the data type of the elements stored in the array.
For example, to declare a two-dimensional array named myArray with three rows and four columns, you can use the following code:
Dim myArray(1 To 3, 1 To 4) As IntegerThis creates an array with three rows and four columns, where each element is an integer value.
Similarly, to declare a three-dimensional array named my3DArray with two rows, three columns, and four levels, you can use the following code:
Dim my3DArray(1 To 2, 1 To 3, 1 To 4) As StringThis creates an array with two rows, three columns, and four levels, where each element is a string value.
Things to Remember
- Excel Worksheet dataset is a 2-D dataset. So, we cannot store a dataset with 3 or more dimensions in a spreadsheet.
- The UBound function returns an error if the specified array is not declared or if it is empty.
- It is important to note that the UBound function returns the index of the last element in the specified dimension of the array. Therefore, to determine the actual size of the array, you need to add 1 to the result of the UBound function.
Conclusion
In this tutorial, we came across multiple usages of the UBound function on multidimensional array with VBA. These methods will allow users to understand the UBound function properly. This will lead them to use the function in a creative way in a different context.
Related Articles
- Excel VBA Array of Strings (4 Examples)
- How to Find Lookup Value in Array in Excel VBA (2 Easy Ways)
- Excel VBA Array of Arrays (3 Examples)
- How to Declare Array in Excel VBA (3 Easy Ways)
- Excel VBA to Read CSV File into Array (4 Ideal Examples)
- How to Check If Array Is Empty with VBA in Excel (3 Variants)
- Excel VBA: Remove Duplicates from an Array (2 Examples)
