How do I find the number of dimensions of an array in VBA?

[JoeBobJenkins]

So if I have

dim varArray1() as Variant
dim varArray2() as Variant

redim varArray1(1,1,1,1)
redim varArray2(10,10,10)

How do I get something like

GetDimensions(varArray1)=4
GetDimensions(varArray2)=3

 

[John_w]

Based on an old MS page which no longer exists.

Sub Array_Dimensions()

    Dim array1(10) As String
    Dim array2(10, 10) As String
    
    MsgBox NumberOfDimensions(array1)
    MsgBox NumberOfDimensions(array2)
    
End Sub


'Code below based on http://support.microsoft.com/kb/152288
'There is no built-in function to return the number of dimensions in an array variable
'in Microsoft Visual Basic for Applications. The easiest way to do this is by looping through
'the dimensions and using an error handler to return the number of dimensions.

Private Function NumberOfDimensions(theArray As Variant) As Long

    Dim DimNum As Long, ErrorCheck As Integer
    
    'Sets up the error handler
    On Error GoTo FinalDimension
    
    'Visual Basic for Applications arrays can have up to 60000 dimensions; this allows for that
    For DimNum = 1 To 60000
    
        'It is necessary to do something with the LBound to force it to generate an error
        ErrorCheck = LBound(theArray, DimNum)
        
    Next
    
    'The error routine
FinalDimension:
    
    NumberOfDimensions = DimNum - 1
    
End Function

 

[steve the fish]

Heres a UDF:

Function arrDim(arr As Variant) As Long

Dim a As Long, val As Long
    
On Error GoTo Err
Do
    a = a + 1
    val = UBound(arr, a)
Loop

Err:
arrDim = a - 1

End Function