Using VBA to Get the Array Dimensions in Excel - 4 Examples

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.

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.

Get Array Dimensions.xlsm