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

JoeBobJenkins

New Member
Joined
Feb 15, 2019
Messages
4
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,351
Based on an old MS page which no longer exists.

Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
Heres a UDF:

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,626
Messages
5,838,436
Members
430,547
Latest member
jopshio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top