How to find the number of dimensions that an array has?

Question

Below is a piece of code where I need to store some info about a warning message by going through messages passed. The parameter passed itself is a variant which is set by an API call to SAPListOfMessages which returns an array of String. What I've noticed however is that whenever there is more than 1 warning, the list is 2D and messageList(x-1) obviously leads to an error because it's not a proper index. What's also strange is that the for each loop seems to ignore dimensions and somehow just flatten the array and loop through it as if it were 1D. The only way around this I see is checking how many dimensions the array has before doing anything else and hence my question. I wasn't able to find any info on getting the number of dimensions - I only found info about their bounds. Is it possible to find the number of dimensions of an array in VBA? If not, how would you suggest I tackle this problem?

Sub getOverlapWarnings(ByRef messageList As Variant, ByRef warnings As Dictionary)

  Dim msg As Variant
  Dim x As Integer
  x = 1
 'look for an overlap warning message in the list of messages
  For Each msg In messageList
    'look for the keyword 'overlap' in the list of messages
    
    If InStr(1, msg, "overlap") <> 0 Then
       warnings.Add messageList(x - 1), msg
    End If
   x = x + 1
  Next msg
End Sub

Answer 1

Is it possible to find the number of dimensions of an array in VBA?

This approach increments the possible dimensions count, 60 being the built in maximum (c.f. comment):

Private Function nDim(ByVal vArray As Variant) As Long
' Purpose: get array dimension (MS)
Dim dimnum     As Long
Dim ErrorCheck As Long    ' OP: As Variant
On Error GoTo FinalDimension

For dimnum = 1 To 60        ' 60 being the absolute dimensions limitation 
    ErrorCheck = LBound(vArray, dimnum)
Next
' It's good use to formally exit a procedure before error handling
' (though theoretically this wouldn't needed in this special case - see comment) 
Exit Function

FinalDimension:
nDim = dimnum - 1

End Function

Further links (thx @ChrisNeilson)

MS Using arrays

Big Array

Answer 2

An array has 2 bounds: Upper and Lower.

I think you're asking where the lower bound begins.

By default, the lower bound is zero. For example:

Sub test()
    Dim arr
    arr = Array("a", "b", "c")
    Debug.Print "Lower: " & LBound(arr), "Upper: " & UBound(arr)
End Sub

returns: Lower: 0 Upper: 2 because the 3 elements have indices of 0, 1, and 2.

---

Some functionality may begin at 1 by default but it's rare. One example is filling an array with a range:

Sub test()
    Dim arr
    arr = Range("A2:A4")
    Debug.Print "Lower: " & LBound(arr), "Upper: " & UBound(arr)
End Sub

...returns: Lower: 1 Upper: 3

---

If you fully declare the array, you can make the upper and lower bound whatever you want:

Sub test()
    Dim arr(99 To 101) As String
    arr(100) = "blah"
    Debug.Print "Lower: " & LBound(arr), "Upper: " & UBound(arr)
End Sub

...returns: Lower: 99 Upper: 101, but an array with declared bounds won't work with many functions (like the previous examples.

---

You can also set the default lower bound with an statement at the very top of each module:

Option Base 1

...but there are so many places it doens't apply it's kind of useless. (More here.)

---

See also:

• MSDN : Declaring Arrays (Fixed & Dynamic)

• MSDN : LBound Function

• MSDN : UBound Function

Answer 3

Here's a technique that doesn't require any looping through possible array dimensions. It reads the dimension count directly from the array definition itself.

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
        Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
#Else
    Private Declare Sub CopyMemory Lib "kernel32" _
        Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End If

Private Enum VariantTypes
    VTx_Empty = vbEmpty         '(0) Uninitialized
    '...
    VTx_Array = vbArray         '(8192)
    VTx_ByRef = &H4000          '(16384) Is an indirect pointer to the Variant's data
End Enum

Type VariantStruct  'NOTE - the added "X_..." prefixes force the VBE Locals window to display the elements in
                    'their correct adjacency order:
    A_VariantType      As Integer  '(2 bytes) See the VariantTypes Enum, above.
    B_Reserved(1 To 6) As Byte     '(6 bytes)
    C_Data             As LongLong '(8 bytes) NOTE: for an array-Variant, its Data is a pointer to the array.
End Type

Type ArrayStruct    'NOTE - the added "X_..." prefixes force the VBE Locals window to display the elements in
                    'their correct adjacency order:
                
    A_DimCount      As Integer  '(aka cDim) 2 bytes: The number of dimensions in the array.
    B_FeatureFlags  As Integer  '(aka fFeature) 2 bytes: See the FeatureFlags Enum, below.
    C_ElementSize   As Long     '(aka cbElements) 4 bytes: The size of each element in the array.
    D_LockCount     As Long     '(aka cLocks) 4 bytes: The count of active locks on the array.
    E_DataPtr       As Long     '(aka pvData) 4 bytes: A pointer to the first data element in the array.
    F_BoundsInfoArr As LongLong '(aka rgsabound) 8 bytes, min.: An info-array of SA_BoundInfo elements (see below)
                                ' that contains bounds data for each dimension of the safe-array.  There is one
                                ' SA_BoundInfo element for each dimension in the array.  F_BoundsInfoArr(0) holds
                                ' the information for the right-most dimension and F_BoundsInfoArr[A_DimCount - 1]
                                ' holds the information for the left-most dimension.  Each SA_BoundInfo element is
                                ' 8 bytes, structured as follows:
End Type

Function ArrayDims(SomeArray As Variant) As Long 'Cast the array argument to an array-Variant (if it isn't already)
                                                 'for a uniform reference-interface to it.
    '
    'Returns the number of dimensions of the specified array.
    '
    'AUTHOR: Peter Straton
    '
    'CREDIT: Adapted from wqw's post at:
    '   https://stackoverflow.com/questions/67016151/how-to-get-array-dimensionarray-parameter-pass-error
    '
    '*************************************************************************************************************
   
    Dim DataPtrOffset   As Integer
    Dim DimCount        As Integer  '= ArrayStruct.A_DimCount (2 bytes)
    Dim VariantType     As Integer  '= VariantStruct.A_VariantType (2 bytes)
    Dim VariantDataPtr  As LongLong '= VariantStruct.C_Data (8 bytes). See note about array-Variants' data, above.
   
    'Check the Variant's type
   
    Call CopyMemory(VariantType, SomeArray, LenB(VariantType))
    If (VariantType And VTx_Array) Then
        'It is an array-type Variant, so get its array data-pointer
       
        Dim VariantX As VariantStruct   'Unfortunately, in VB/VBA, you can't reference the size of a user-defined
                                        'data-Type element without instantiating one.
        DataPtrOffset = LenB(VariantX) - LenB(VariantX.C_Data) 'Takes advantage of C_Data being the last element
        Call CopyMemory(VariantDataPtr, ByVal VarPtr(SomeArray) + DataPtrOffset, LenB(VariantDataPtr))
       
        If VariantDataPtr <> 0 Then
            If (VariantType And VTx_ByRef) Then
                'The passed array argument was not an array-Variant, so this function-call's cast to Variant type
                'creates an indirect reference to the original array, via the Variant parameter.  So de-reference
                'that pointer.
               
                Call CopyMemory(VariantDataPtr, ByVal VariantDataPtr, LenB(VariantDataPtr))
            End If
           
            If VariantDataPtr <> 0 Then
                'Now have a legit Array reference, so get and return its dimension-count value
               
                Call CopyMemory(DimCount, ByVal VariantDataPtr, LenB(DimCount))
            End If
        End If
    End If
   
    ArrayDims = DimCount
End Function 'ArrayDims

Sub Demo_ArrayDims()
    '
    'Demonstrates the functionality of the ArrayDims function using a 1-D, 2-D and 3-D array of various types
    '
    '*************************************************************************************************************
   
    Dim Test2DArray As Variant
    Dim Test3DArray() As Long

    Debug.Print 'Blank line
    Debug.Print ArrayDims(Array(20, 30, 400)) 'Test 1D array
   
    Test2DArray = [{0, 0, 0, 0; "Apple", "Fig", "Orange", "Pear"}]
    Debug.Print ArrayDims(Test2DArray)
   
    ReDim Test3DArray(1 To 3, 0 To 1, 1 To 4)
    Debug.Print ArrayDims(Test3DArray)
End Sub

The SA_BoundInfo Type and FeatureFlags Enum aren't actually used by the ArrayDims routine but they're also included here for reference:

Private Type SA_BoundInfo
    ElementCount As Long        '(aka cElements) 4 bytes: The number of elements in the dimension.
    LBoundVal As Long           '(aka lLbound) 4 bytes: The lower bound of the dimension.
End Type

Enum FeatureFlags
    FADF_AUTO = &H1         'Array is allocated on the stack.
    FADF_STATIC = &H2       'Array is statically allocated.
    FADF_EMBEDDED = &H4     'Array is embedded in a structure.
    FADF_FIXEDSIZE = &H10   'Array may not be resized or reallocated.
    FADF_BSTR = &H100       'An array of BSTRs.
    FADF_UNKNOWN = &H200    'An array of IUnknown pointers.
    FADF_DISPATCH = &H400   'An array of IDispatch pointers.
    FADF_VARIANT = &H800    'An array of VARIANT type elements.
    FADF_RESERVED = &HF0E8  'Bits reserved for future use.
End Enum