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

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.

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.

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.