VBA – Find number of Dimensions in an Array

Find how many dimensions an Array has?

We use array to store multiple values under same variable name. This array can be a simple one dimensional array or multi dimensional.

For example:

1. 1 dimensional or 1D – arrNumber(0) =0,arrNumber(1) =2,arrNumber(2) =5
2. 2 dimensional or 2D – arrTab(0,0) = 1, arrTab(0,1) = 2, arrTab(1,0) =3,arrTab(1,1) =4

The number of dimensions can increase based on the implementation logic.

We can find the length of an array using UBOUND function. But how can we find the number of dimensions in an array.

VBA Code to get Array Dimension

There is no built in function available to find this. But we can tweak the same UBOUND function to get this value.

Here is the code to find the number of dimensions present in an array.

Function getArrayDimension(vArray As Variant) As Double
    'Init
    Dim iDim As Double
    Dim iRet As Double
    Dim bFlag As Boolean
 
    'Loop to get dimension
    On Error Resume Next
        For iDim = 1 To 99
            bFlag = IsNumeric(UBound(vArray, iDim))
            If Err.Number = 9 Then Exit For
        Next iDim
        iRet = iDim
    On Error GoTo 0
 
    'Return array dimension
    getArrayDimension = iRet
End Function

This code uses the a loop which will exits when there is no ubound available for a dimension. So, we take the maximum number of dimension that has a ubound as the actual size of the array.