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.
A more complicated way would be to get to the SAFEARRAY that VBA creates internally for the actual array and
then use RTLMoveMemory API function to determine the number of dimensions. A safer and easier way is to use
this code.
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
