Home » Blogs » Vishesh's blog

Count Array Dimensions

Vishesh's picture
Submitted by Vishesh on 6 August, 2011 - 19:31
The following piece of code gives the number of dimensions an array has. In the TestRun procedure a range is assigned to variant and that variant is passed to GetArrayDimensions function as a parameter. GetArrayDimensions function returns the number of dimensions in an array. If the range has only one cell then it returns 0 as its not an array; if it has more than one cell its an array in which case it returns 2. 
Sub TestRun()
    Dim arr
    arr = Sheet1.Range("A1:A2")
    MsgBox "No of Dimensions in Array: " & GetArrayDimensions(arr), vbInformation, "Excel Experts"
End Sub
 
Function GetArrayDimensions(arrVariant) As Integer
    Dim lngDimCnt       As Long
    Dim lngChkErr       As Long
 
On Error GoTo ExitF
    For lngDimCnt = 1 To 60000 'max 60000 dimensions allowed
       lngChkErr = LBound(arrVariant, lngDimCnt)
    Next lngDimCnt
    Exit Function
ExitF:
    GetArrayDimensions = lngDimCnt - 1
End Function
»