VBA using ubound on a multidimensional array

Question

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

User · Answer

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)

User · Answer

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

User · Answer

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    amp  sizes Count  amp    dimensions       For i   1 To sizes Count         Debug Print sizes i  0   amp    to    amp  sizes i  1      Next i      Set sizes   Bounds B      Debug Print vbCrLf  amp   B has    amp  sizes Count  amp    dimensions       For i   1 To sizes Count         Debug Print sizes i  0   amp    to    amp  sizes i  1      Next i      Set sizes   Bounds C      Debug Print vbCrLf  amp   C has    amp  sizes Count  amp    dimensions       For i   1 To sizes Count         Debug Print sizes i  0   amp    to    amp  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

User · Answer

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    amp  i  amp     0  is    amp  arr i  0      Next i     Debug Print         Debug Print  Loop Dimension 2      For j   0 To UBound arr  2          Debug Print  arr 0     amp  j  amp     is    amp  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    amp  i  amp        amp  j  amp     is    amp  arr i  j          Next j     Next i     Debug Print     End Sub

User · Answer

ubound arr  1     and   ubound arr  2

[arrays] VBA using ubound on a multidimensional array

Examples related to arrays

Examples related to excel

Examples related to vba