Modules: Determining the number of dimensions for an array

   There is no direct way to determine the number of dimensions in an Array in VBA.  This is not the same as the value returned by UBound which lets us find out the largest available subscript for the indicated dimension.

    One way to determine the number of dimensions is to start with the LBound value and keep increasing  the dimension value until an error occurs.  The number of dimensions is one less than the value where the error occurred.  This technique is explained by this Knowledge Base  article.

XL: Determining the Number of Dimensions in an Array Variable Article ID: 152288

   A safer and easier way is to use this code.

'************ Code Start **********
'This code was originally written by Lyle Fairfield
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Lyle Fairfield
'
 Function ElementCount(b As Variant) As Long
    Dim v As Variant, z As Long
    For Each v In b
        z = z + 1
    Next v
    Do
        ElementCount = ElementCount + 1
        z = z / (UBound(b, ElementCount) - LBound(b, ElementCount) + 1)
    Loop Until z = 1
 End Function

Sub testArray()
 Dim a(3 To 9, 4 To 7, 0, 1 To 12) As Variant, b As Variant
 Dim varReturn As Long
    b = a
    varReturn = fDummy(b)
    MsgBox varReturn
 End Sub

 Function fDummy(b As Variant) As Long
    fDummy = ElementCount(b)
 End Function
'********** Code End ***********