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.


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.

Using VBA to Get Array Dimensions with Elements Numbers 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.

Showing Output by Using VBA to Get Array Dimensions with Elements Numbers in Excel

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 get “upperbound” and “lowerbound” dimensions.

Using UBound and LBound Functions to Get Array 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

Showing Output by Using UBound Function

Showing Output by Using LBound Function

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.

Creating User-Defined Function to Get Array Dimensions 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.

Showing Output by Creating a User-Defined Function

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.

Showing Array Dimensions as Row and Column Number Based on User Input

  • 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo