# VBA to Get Array Dimensions in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

You must give the appropriate index for the desired element in order to access a specific element of an array. Based on the dimensions of the element, the indices show where it is located within the array. For instance, to access a specific element in a two-dimensional array that represents a matrix of numbers, you would need to give both the row and column indexes. This article will show you how to use VBA to get Array Dimensions in Excel.

You may download the following Excel workbook for better understanding and practice it yourself.

## VBA to Get Array Dimensions in Excel: 4 Suitable Examples

This article will demonstrate how to get array dimensions in Excel VBA by giving 4 practical examples with explanations. Here, we will apply the VBA UBOUND and LBOUND functions, then create a user-defined function for showing array dimensions,  and finally, show the number of rows and columns based on user input. So, you can go through a sample video of our work on how to get array dimensions in Excel VBA.

### Example 1: Get Array Dimensions with Elements Numbers in Excel

In this section, you will learn how to get array dimensions with its elements’ numbers using VBA in Excel. • Press F5 or click the Run button to execute the VBA macro.
``````Sub Array_Elements()
Dim Myarray(1 To 6, 1 To 3) As String, ArrayDimension1 As Integer, ArrayDimension2 As Integer
ArrayDimension1 = UBound(Myarray, 1) - LBound(Myarray, 1) + 1
ArrayDimension2 = UBound(Myarray, 2) - LBound(Myarray, 2) + 1
MsgBox "This array has " & ArrayDimension1 * ArrayDimension2 & " elements"
End Sub``````

VBA Breakdown:

``Sub Array_Elements()``

This line declares the beginning of the “Array_Elements” subroutine. A section of code known as a subroutine executes a particular task or group of duties.

``Dim Myarray(1 to 6, 1 to 3), ArrayDimension1, and ArrayDimension2, as String, Integer, and Integer, respectively``

This line defines three variables: ArrayDimension1 as an integer, ArrayDimension2 as an integer, and Myarray as a two-dimensional array of strings with dimensions 1 to 6 and 1 to 3. VBA uses the Dim keyword to declare variables.

``ArrayDimension1 = UBound(Myarray, 1) - LBound(Myarray, 1) + 1``

The lower bound of the first dimension is subtracted from the upper bound of the first dimension, and then 1 is added to determine the number of items in the first dimension of Myarray. The ArrayDimension1 variable holds the outcome.

``ArrayDimension2 = UBound(Myarray, 2) - LBound(Myarray, 2) + 1``

The lower bound of the second dimension is subtracted from the upper bound of the second dimension, and then one is added to get the number of elements in the second dimension of Myarray. The ArrayDimension2 variable holds the outcome.

``MsgBox "This array has " & ArrayDimension1 * ArrayDimension2 & " elements"``

The number of elements in Myarray, as determined by multiplying ArrayDimension1 and ArrayDimension2, is displayed in a message box by this line.

``End Sub:``

The “Array_Elements” subroutine ends with this line. In a nutshell, this VBA code establishes the Myarray two-dimensional string array with the declared dimensions, counts the number of entries in each dimension, and shows the overall number of elements in a message box.

### Example 2: Using UBound and LBound Functions to Get Array Dimensions

Here, we will apply the Ubound and LBound functions to get “upperbound” and “lowerbound” dimensions. • Press F5 or click the Run button to execute the VBA macro.
``````Sub UBound_LBound_Dimension()
Dim Myarray(3 To 7) As String
MsgBox "Upperbound is " & UBound(Myarray)
MsgBox "Lowerbound is " & LBound(Myarray)
End Sub``````  Finally, you will see the result of the Upperbound and Lowerbound dimensions in the above image.

### Example 3: Creating User-Defined Function to Get Array Dimensions

Here, we will create a User-Defined function to get array dimensions for various types of arrays, such as 1-dimensional, 2-dimensional, and 3-dimensional arrays, in Excel VBA. • Press F5 or click the Run button to execute the VBA macro.
``````Sub Get_ArrayDimensions()
Dim Dimension1 As Long
Dim Dimension2 As Long
Dim Dimension3 As Long
Dim Myarray1(1 To 5) As Long
Dim Myarray2(1 To 3, 1 To 4) As Double
Dim Myarray3(1 To 2, 1 To 3, 1 To 4) As String
Dimension1 = GetArrayDimensions(Myarray1) ' Returns 1
Dimension2 = GetArrayDimensions(Myarray2) ' Returns 2
Dimension3 = GetArrayDimensions(Myarray3) ' Returns 3
MsgBox "Number of dimensions for Myarray1: " & Dimension1 & vbCrLf & _
"Number of dimensions for Myarray2: " & Dimension2 & vbCrLf & _
"Number of dimensions for Myarray3: " & Dimension3
End Sub
Function GetArrayDimensions(var As Variant) As Long
On Error GoTo Err
Dim p As Long
Dim q As Long
p = 0
Do While True
p = p + 1
q = UBound(var, p)
Loop
Err:
GetArrayDimensions = p - 1
End Function``````

VBA Breakdown:

``Sub Get_ArrayDimensions()``

This line introduces the beginning of the “Get_ArrayDimensions” function. A section of code known as a subroutine executes a particular task or group of duties.

``Dim Dimension1 As Long, Dim Dimension2 As Long, Dim Dimension3 As Long``

These three variables, Dimension1, Dimension2, and Dimension3, are declared as having the Long data type in the following three lines. The number of dimensions of various arrays will be stored in these variables.

``Dim Myarray1(1 To 5) As Long, Dim Myarray2(1 To 3, 1 To 4) As Double, Dim Myarray3(1 To 2, 1 To 3, 1 To 4) As String``

Three arrays with various dimensions and data types are declared in these lines. Myarray1 is an array of length one, Myarray2 is an array of length two, and Myarray3 is an array of length three of string.

``Dimension1 = GetArrayDimensions(Myarray1), Dimension2 = GetArrayDimensions(Myarray2), Dimension3 = GetArrayDimensions(Myarray3):.``

The GetArrayDimensions method is called in these lines with each array’s parameters, and the resultant values are saved in the Dimension1, Dimension2, and Dimension3 variables. The number of an array’s dimensions can be found using the GetArrayDimensions function.

``MsgBox "Number of dimensions for Myarray1: " & Dimension1 & vbCrLf & _ "Number of dimensions for Myarray2: " & Dimension2 & vbCrLf & _ "Number of dimensions for Myarray3: " & Dimension3``

This line shows a message box with the GetArrayDimensions function results for the number of dimensions for each array.

``End Sub``

The “Get_ArrayDimensions” subroutine ends with this line.

``GetArrayDimensions function(var As Variant) As Long``

This line marks the beginning of the “GetArrayDimensions” function, which accepts the Variant parameter var and returns a Long result. A unit of code known as a function carries out a defined operation and returns a value.

``On Error GoTo Err``

In the event of an error, we intructed the code to go to the label “Err” by using the On Error GoTo Err line.

``Dim p As Long and Dim q As Long``

These lines declare the two variables, p, and q, as having the Long data type. We will iterate over the array’s dimensions using these variables.

``p = 0``

We give the variable p a value of 0 in this line.

``Do While True``

Initiates an infinite loop that runs until it encounters an error or an exit circumstance.

``p = p + 1``

This line increases the value of p, which stands for the current dimension being checked, by 1 for each iteration of the loop.

``q = UBound(var, p)``

Uses the UBound function to extract the upper bound of the p-th dimension of the var array and stores it in the variable q. We did this to see if the array has additional dimensions.

``Loop``

This line signifies the conclusion of the loop.

``Err:``

The code jumps to this line, labeled “Err,” in the event of an error.

``GetArrayDimensions = p - 1``

We set the GetArrayDimensions method to the value p – 1, which denotes the number of dimensions in the input array.

``End Function``

After reaching the error handler, the code exits the method and returns the value of GetArrayDimensions as the number of dimensions in the input array. Therefore, the above image has demonstrated the number of dimensions Myarray1, Myarray2, and Myarray3 are 1-Dimensional, 2-Dimensional, and 3-Dimensional respectively.

### Example 4: Showing Array Dimensions as Row and Column Number Based on User Input

Now, we will demonstrate how to get array dimensions as row and column numbers based on user input by selecting a range from the Excel worksheet. • Press F5 or click the Run button to execute the VBA macro.
``````Sub Array_Dimensions_ForRange()
Dim Myarray As Variant
Dim RowNum As Long
Dim ColNum As Long
Dim Rng As Range
'Display range selection dialog box and get selected range
On Error Resume Next
Set Rng = Application.InputBox("Select a range", Type:=8)
On Error GoTo 0
'Check if a range was selected
If Rng Is Nothing Then
MsgBox "No range selected.", vbInformation
Exit Sub
End If
'Read data from Excel sheet into an array
Myarray = Rng.Value
'Check if array is empty or not
If IsEmpty(Myarray) Then
MsgBox "No data found in the selected range.", vbInformation
Exit Sub
End If
'Get the dimensions of the array
RowNum = UBound(Myarray, 1)
ColNum = UBound(Myarray, 2)
'Display the dimensions in a message box
MsgBox "Number of rows: " & RowNum & vbCrLf & "Number of columns: " & ColNum
End Sub``````

VBA Breakdown:

``Dim Myarray As Variant,  Dim RowNum As Long,  Dim ColNum As Long,  Dim Rng As Range``

This line will keep the values of the chosen range in an array by the Variant type Myarray. We stored the array’s row and column counts as Long types with RowNum and  ColNum, respectively. Rng is a range that we used to store the user’s chosen range.

``On Error Resume Next``

The Mistaken Resume The next statement enables error handling in the event that the user closes the range selection dialog box.

``Set Rng = Application.InputBox("Select a range", Type:=8``

The Application.InputBox function allows the user to choose a range on the active Excel sheet displayed using the inputBox function. To indicate that the user should choose a range, we set the Type argument to 8 in this case.

``On Error GoTo 0``

To return the error handling to its default state.

``If Rng Is Nothing Then``

The program checks to see if the Rng variable is Nothing, which denotes that the user closed the dialog box for range selection.

``MsgBox "No range selected.", vbInformation``

Using the MsgBox function and the vbInformation icon, this line shows a message box with the message “No range selected.” if Rng is Nothing.

``Exit Sub``

If you choose no range, we used the Exit Sub command to end the subroutine.

``Myarray = Rng.Value``

This property, which returns a 2-dimensional array containing the values of the cells in the range, we used it to read the values of the selected range into the Myarray array.

``If IsEmpty(Myarray) Then``

We used this method in the code to determine whether the Myarray array is empty.

``MsgBox "No data found in the selected range.", vbInformation``

Using the MsgBox function and vbInformation as the message box’s icon, this line shows a message box with the message “No data found in the selected range.” if Myarray is empty.

``Exit Sub``

If the array is empty, we used the Exit Sub command to end the subroutine.

``RowNum = UBound(Myarray, 1), ColNum = UBound(Myarray, 2)``

Using inputs 1 and 2, we used the UBound function to determine the upper bound of the Myarray array in the first dimension (rows) and second dimension (columns), respectively. Then, we give the RowNum and ColNum variables the upper boundaries’ values, respectively.

``MsgBox "Number of rows: " & RowNum & vbCrLf & "Number of columns: " & ColNum``

Using the values kept in the RowNum and ColNum variables, we used the MsgBox function to show a message box with the number of rows and columns in the array. In order to make the message box’s rows and columns more readable, we added a new line using the vbCrLf.

Here, we have determined the number of rows and columns by using a simple VBA macro by selecting a range. So, you can go through the above for a better understanding of how to get array dimensions with Excel VBA.

• Does a built-in VBA function to determine an array’s size or dimension exist?

Yes, you may use the UBound function in VBA to determine an array’s size or dimension in any dimension. It provides an array’s upper bound in the chosen dimension.

• How can I use Excel VBA to measure the length or size of an array?

To determine the length or size of an array in any dimension, use the UBound function in VBA. In a two-dimensional array, for instance, UBound(Myarray, 1) + 1 will give you the number of rows, and UBound(Myarray, 2) + 1 will give you the number of columns.

## Conclusion

In this article, we’ve covered 4 examples of how to use VBA to get array dimensions in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below. Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  