44
votes

Ubound can return the max index value of an array, but in a multidimensional array, how would I specify WHICH dimension I want the max index of?

For example

Dim arr(1 to 4, 1 to 3) As Variant

In this 4x3 array, how would I have Ubound return 4, and how would I have Ubound return 3?

6

6 Answers

75
votes
ubound(arr, 1) 

and

ubound(arr, 2) 
21
votes

You need to deal with the optional Rank parameter of UBound.

Dim arr(1 To 4, 1 To 3) As Variant
Debug.Print UBound(arr, 1)  '◄ returns 4
Debug.Print UBound(arr, 2)  '◄ returns 3

More at: UBound Function (Visual Basic)

8
votes

[This is a late answer addressing the title of the question (since that is what people would encounter when searching) rather than the specifics of OP's question which has already been answered adequately]

Ubound is a bit fragile in that it provides no way to know how many dimensions an array has. You can use error trapping to determine the full layout of an array. The following returns a collection of arrays, one for each dimension. The count property can be used to determine the number of dimensions and their lower and upper bounds can be extracted as needed:

Function Bounds(A As Variant) As Collection
    Dim C As New Collection
    Dim v As Variant, i As Long

    On Error GoTo exit_function
    i = 1
    Do While True
        v = Array(LBound(A, i), UBound(A, i))
        C.Add v
        i = i + 1
    Loop
exit_function:
    Set Bounds = C
End Function

Used like this:

Sub test()
    Dim i As Long
    Dim A(1 To 10, 1 To 5, 4 To 10) As Integer
    Dim B(1 To 5) As Variant
    Dim C As Variant
    Dim sizes As Collection

    Set sizes = Bounds(A)
    Debug.Print "A has " & sizes.Count & " dimensions:"
    For i = 1 To sizes.Count
        Debug.Print sizes(i)(0) & " to " & sizes(i)(1)
    Next i

    Set sizes = Bounds(B)
    Debug.Print vbCrLf & "B has " & sizes.Count & " dimensions:"
    For i = 1 To sizes.Count
        Debug.Print sizes(i)(0) & " to " & sizes(i)(1)
    Next i

    Set sizes = Bounds(C)
    Debug.Print vbCrLf & "C has " & sizes.Count & " dimensions:"
    For i = 1 To sizes.Count
        Debug.Print sizes(i)(0) & " to " & sizes(i)(1)
    Next i
End Sub

Output:

A has 3 dimensions:
1 to 10
1 to 5
4 to 10

B has 1 dimensions:
1 to 5

C has 0 dimensions:
2
votes
  • UBound(myArray, 1) returns the number of rows in 2d array
  • UBound(myArray, 2) returns the number of columns in 2d array

However, let's go 1 step further and assume that you need the last row and last column of range, that has been written as a 2d array. That row (or column) should be converted to a 1d array. E.g. if our 2d array looks like this:

enter image description here

Then running the code below, will give you 2 1D arrays, that are the last column and last row: enter image description here

Sub PrintMultidimensionalArrayExample()    
    Dim myRange As Range: Set myRange = Range("a1").CurrentRegion        
    Dim myArray As Variant: myArray = myRange        
    Dim lastRowArray As Variant: lastRowArray = GetRowFromMdArray(myArray, UBound(myArray, 1))
    Dim lastColumnArray As Variant
    lastColumnArray = GetColumnFromMdArray(myArray, UBound(myArray, 2))        
End Sub

Function GetColumnFromMdArray(myArray As Variant, myCol As Long) As Variant        
    'returning a column from multidimensional array
    'the returned array is 0-based, but the 0th element is Empty.        
    Dim i As Long
    Dim result As Variant
    Dim size As Long: size = UBound(myArray, 1)
    ReDim result(size)        
    For i = LBound(myArray, 1) To UBound(myArray, 1)
        result(i) = myArray(i, myCol)
    Next        
    GetColumnFromMdArray = result        
End Function

Function GetRowFromMdArray(myArray As Variant, myRow As Long) As Variant        
    'returning a row from multidimensional array
    'the returned array is 0-based, but the 0th element is Empty.        
    Dim i As Long
    Dim result As Variant
    Dim size As Long: size = UBound(myArray, 2)
    ReDim result(size)        
    For i = LBound(myArray, 2) To UBound(myArray, 2)
        result(i) = myArray(myRow, i)
    Next        
    GetRowFromMdArray = result        
End Function
0
votes

In addition to the already excellent answers, also consider this function to retrieve both the number of dimensions and their bounds, which is similar to John's answer, but works and looks a little differently:

Function sizeOfArray(arr As Variant) As String
    Dim str As String
    Dim numDim As Integer

    numDim = NumberOfArrayDimensions(arr)
    str = "Array"

    For i = 1 To numDim
        str = str & "(" & LBound(arr, i) & " To " & UBound(arr, i)
        If Not i = numDim Then
            str = str & ", "
        Else
            str = str & ")"
        End If
    Next i

    sizeOfArray = str
End Function


Private Function NumberOfArrayDimensions(arr As Variant) As Integer
' By Chip Pearson
' http://www.cpearson.com/excel/vbaarrays.htm
Dim Ndx As Integer
Dim Res As Integer
On Error Resume Next
' Loop, increasing the dimension index Ndx, until an error occurs.
' An error will occur when Ndx exceeds the number of dimension
' in the array. Return Ndx - 1.
    Do
        Ndx = Ndx + 1
        Res = UBound(arr, Ndx)
    Loop Until Err.Number <> 0
NumberOfArrayDimensions = Ndx - 1
End Function

Example usage:

Sub arrSizeTester()
    Dim arr(1 To 2, 3 To 22, 2 To 9, 12 To 18) As Variant
    Debug.Print sizeOfArray(arr())
End Sub

And its output:

Array(1 To 2, 3 To 22, 2 To 9, 12 To 18)
0
votes

Looping D3 ways;

Sub SearchArray()
    Dim arr(3, 2) As Variant
    arr(0, 0) = "A"
    arr(0, 1) = "1"
    arr(0, 2) = "w"

    arr(1, 0) = "B"
    arr(1, 1) = "2"
    arr(1, 2) = "x"

    arr(2, 0) = "C"
    arr(2, 1) = "3"
    arr(2, 2) = "y"

    arr(3, 0) = "D"
    arr(3, 1) = "4"
    arr(3, 2) = "z"

    Debug.Print "Loop Dimension 1"
    For i = 0 To UBound(arr, 1)
        Debug.Print "arr(" & i & ", 0) is " & arr(i, 0)
    Next i
    Debug.Print ""

    Debug.Print "Loop Dimension 2"
    For j = 0 To UBound(arr, 2)
        Debug.Print "arr(0, " & j & ") is " & arr(0, j)
    Next j
    Debug.Print ""

    Debug.Print "Loop Dimension 1 and 2"
    For i = 0 To UBound(arr, 1)
        For j = 0 To UBound(arr, 2)
            Debug.Print "arr(" & i & ", " & j & ") is " & arr(i, j)
        Next j
    Next i
    Debug.Print ""

End Sub