**Example 1 – Get the Array Dimensions with the Number of Elements in Excel**

- Press
**F5**or click**Run**to run 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()`

declares the beginning of the “Array_Elements” subroutine.

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

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. The Dim keyword declares 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 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 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, determined by multiplying ArrayDimension1 by ArrayDimension2, is displayed in a message box.

`End Sub:`

The “Array_Elements” subroutine ends.

The 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 the UBound and LBound Functions to Get theArray Dimensions **

Use the **Ubound **and **LBound **functions to get the **“upperbound” **and **“lowerbound” **dimensions.

- Press
**F5**or click**Run**to run 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
```

This is the output.

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

**Example 3 – Creating a User-Defined Function to Get the Array Dimensions **

Create a User-Defined function to get the array dimensions for different types of arrays (1-dimensional, 2-dimensional, and 3-dimensional).

- Press
**F5**or click**Run**to run 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()`

introduces the beginning of the **“Get_ArrayDimensions”** function.

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

The three variables, **Dimension1, Dimension2, and Dimension3**, are declared as Long data type. The number of the dimensions of the 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 different dimensions and data types are declared. **Myarray1** is an array of length one, **Myarray2** is an array of length two, and **Myarray3** is an array of length three.

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

The **GetArrayDimensions** method is called with the parameters of each array, and the resultant values are saved in the **Dimension1, Dimension2,** and** Dimension3** variables. The number of dimensions in the array is 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`

shows a message box with the **GetArrayDimensions** function results: the number of dimensions in each array.

`End Sub`

The “Get_ArrayDimensions” subroutine ends.

`GetArrayDimensions function(var As Variant) As Long`

marks the beginning of the **“GetArrayDimensions”** function, which accepts the Variant parameter var and returns a **Long** result.

`On Error GoTo Err`

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

`Dim p As Long and Dim q As Long`

declare the two variables, **p**, and **q**, as **Long** data type.

`p = 0`

gives the variable **p** a value of 0.

`Do While True`

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

`p = p + 1`

increases the value of** p** 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**.

`Loop`

ends the **loop**.

`Err:`

The code goes to **“Err,”** in the event of an error.

`GetArrayDimensions = p - 1`

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

**Myarray1, Myarray2, and Myarray3** are 1-Dimensional, 2-Dimensional, and 3-Dimensional, respectively.

**Read More: **Excel VBA Multidimensional Arrays

**Example 4 – Showing the Array Dimensions as Row and Column Number Based on the User Input**

- Press
**F5**or click**Run**to run 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`

keeps the values of the chosen range in an array with the Variant type **Myarray**; stores the row and column counts of the array as **Long** types with **RowNum** and **ColNum**. **Rng** stores the user’s chosen range.

`On Error Resume Next`

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. The Type argument is set to 8 to indicate that the user must choose a range.

`On Error GoTo 0`

returns the error handling to its default state.

`If Rng Is Nothing Then`

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`

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

`Exit Sub`

If no range is chosen, **Exit Sub** ends the subroutine.

`Myarray = Rng.Value`

returns a 2-dimensional array containing the values of the cells in the range; is used to read the values of the selected range into the **Myarray** array.

`If IsEmpty(Myarray) Then`

determines whether the **Myarray** array is empty.

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

shows a message box with the message **“No data found in the selected range.”** if **Myarray** is empty, using the **MsgBox** function and **vbInformation** .

`Exit Sub`

If the array is empty, the **Exit Sub** ends the **subroutine**.

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

Using inputs 1 and 2, the **UBound** function determines the upper bound of Myarray in the first dimension (rows) and second dimension (columns). The **RowNum** and **ColNum** variables are given the values of the upper boundaries.

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

Using the values in the **RowNum** and **ColNum** variables, the **MsgBox** function shows a message box with the number of rows and columns in the array, using **vbCrLf**.

**Download Practice Workbook**

Download the following Excel workbook.

## 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