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.

**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.

**Read More: **Excel VBA: Determine Number of Elements in Array

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

Here, we will apply the **Ubound **and **LBound **functions to ge**t “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.

**Read More: **VBA Array Size Limit in Excel

**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 instructed 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.

**Read More: **Excel VBA Multidimensional Arrays

**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 use 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 use 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 use 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.

**Frequently Asked Questions**

**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.

**Download Practice Workbook**

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

**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. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

## Related Articles

- How to Find Lookup Value in Array in Excel VBA
- How to Check If Array Is Empty with VBA in Excel
- Excel VBA to Populate Array with Cell Values