Get element from multidimensional array with unknown dimensions

Question

If I have an array with n-dimensions, where n is an unknown number until runtime, how do I index into that array?

ReDim indices(1 to n) As Long = array(1,2,3)

data(1,2,3) 'n = 3

data(*indices) 'I want this

(we can work out n using this https://github.com/cristianbuse/VBA-ArrayTools/blob/c23cc6ba550e7ebaed1f26808501ea3afedf1a3b/src/LibArrayTools.bas#L730-L741)

Public Function GetArrayDimsCount(ByRef arr As Variant) As Long
    Const MAX_DIMENSION As Long = 60 'VB limit
    Dim dimension As Long
    Dim tempBound As Long
    '
    On Error GoTo FinalDimension
    For dimension = 1 To MAX_DIMENSION
        tempBound = LBound(arr, dimension)
    Next dimension
FinalDimension:
    GetArrayDimsCount = dimension - 1
End Function

The following does what I want I think but I was wondering is there an obvious way to do this in VBA (*pv void looks like a headache)

HRESULT SafeArrayGetElement(
  [in]  SAFEARRAY *psa,
  [in]  LONG      *rgIndices,
  [out] void      *pv
);

Answer 1

With a bit of memory trickery you can look at your multidimensional array as a one dimensional array. You will need LibMemory:

Option Explicit

Public Type FAKE_ARRAY
    sArr As SAFEARRAY_1D
    fakeArrPtr As LongPtr
    values As Variant
End Type

Public Sub ArrayToFakeArray(ByRef arr As Variant, ByRef fakeArray As FAKE_ARRAY)
    Dim aptr As LongPtr: aptr = ArrPtr(arr) 'Will throw if not array
    Dim i As Long
    '
    With fakeArray
        .fakeArrPtr = VarPtr(.sArr)
        MemCopy .fakeArrPtr, aptr, LenB(.sArr)
        With .sArr.rgsabound0
            .cElements = 1
            For i = 1 To fakeArray.sArr.cDims
                .cElements = .cElements * (UBound(arr, i) - LBound(arr, i) + 1)
            Next i
        End With
        .sArr.cDims = 1
        .values = VarPtr(.fakeArrPtr)
        MemInt(VarPtr(.values)) = VarType(arr) Or VT_BYREF
    End With
End Sub

Quick test:

Sub Test()
    Dim arr(2, 3, 2) As Variant
    Dim i As Long, j As Long, k As Long
    Dim m As Long
    Dim v As Variant
    '
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            For k = LBound(arr, 3) To UBound(arr, 3)
                arr(i, j, k) = m
                m = m + 1
            Next k
        Next j
    Next i
    '
    Dim temp As FAKE_ARRAY: ArrayToFakeArray arr, temp
    '
    Dim arr2(1, 1) As Double
    arr2(1, 1) = 17.55
    '
    Dim temp2 As FAKE_ARRAY: ArrayToFakeArray arr2, temp2
    '
    Debug.Print temp.values(0)
    Debug.Print temp.values(4)  '15
    Debug.Print temp.values(35)
    '
    arr(1, 1, 0) = "AAA"
    Debug.Print temp.values(4)  'AAA
    Debug.Print temp2.values(3)
End Sub

Edit #1

This is a response to a series of interesting questions asked by the OP in the comments section. Not only the response is too long but it should definitely be part of the answer.

If I'm understanding correctly, the last line sets the array type as the same as arr but all the elements of the fake one point to the original ByRef?

When copying the SAFEARRAY structure, we also copy the pvData pointer pointing to the actual data. The fake array is pointing to the same data in memory so we're fooling the array handling code to read that data directly (not ByRef). But, we need to set the ByRef flag on the values Variant to avoid releasing the same memory twice which would cause a crash. But nothing so far is ByRef - just 2 array variables pointing to the same data.

Could there be a situation where the original is already ByRef (paramarray of VARIANTARGS?) and this doesn't work?

If the original data is having ByRef members (paramarray of VARIANTARGS) that could only happen if we use something like the CloneParamArray method because otherwise VB doesn't allow to pass the param array around, at least not natively. In this case, accessing the ByRef members via the fake array can only be done correctly via an utility function that can receive such a member ByRef.

Example:

Sub Test()
    Dim t As Long: t = 5
    
    ToParam 1, 2, 3, 4, t
    
    Debug.Print t
End Sub

Public Sub ToParam(ParamArray args() As Variant)
    Dim arr() As Variant
    CloneParamArray args(0), UBound(args) + 1, arr
    
    Dim temp As FAKE_ARRAY: ArrayToFakeArray arr, temp
    
    Debug.Print arr(4)
'    Debug.Print temp.values(4) 'Err 458 - type not supported
    PrintVar temp.values(4)

    args(4) = 7
    
    Debug.Print arr(4)
    PrintVar temp.values(4)
    
    LetByRef(temp.values(4)) = 9
    
    Debug.Print arr(4)
    PrintVar temp.values(4)
End Sub

Private Function PrintVar(ByRef v As Variant)
    Debug.Print v
End Function

Private Property Let LetByRef(ByRef vLeft As Variant, ByVal vRight As Variant)
    vLeft = vRight
End Property

If one uses CloneParamArray on purpose then one should be aware anyway that the ByRef individual Variant members can only be accesed/changed via utility methods like PrintVar and LetByRef or any other method expecting a ByRef Variant as an argument.

Also, why do you take the array byref? Because it's a reference type, byval doesn't make a shallow copy so the only difference is now you can set arr to point to a different array?

Because we don't know what the array type is (e.g. Long() or Variant()) then we obviously must wrap in a Variant when we pass to the ArrayToFakeArray method. Passing the wrapped array ByVal does make a copy and we can see that by runing the below:

Option Explicit

Sub Test()
    Dim arr() As Long
    ReDim arr(0 To 1)
    arr(0) = 12
    arr(1) = 44
    '
    PassArr arr, arr
    Debug.Print
    Debug.Print arr(1) 'Prints 55
End Sub

Private Sub PassArr(ByVal arrByVal As Variant, ByRef arrByRef As Variant)
    #If Win64 Then
        Const dataOffset As Long = 16
    #Else
        Const dataOffset As Long = 12
    #End If
    Dim aPtrByVal As LongPtr: aPtrByVal = ArrPtr(arrByVal)
    Dim aPtrByRef As LongPtr: aPtrByRef = ArrPtr(arrByRef)
    Dim pvDataByVal As LongPtr: pvDataByVal = MemLongPtr(aPtrByVal + dataOffset)
    Dim pvDataByRef As LongPtr: pvDataByRef = MemLongPtr(aPtrByRef + dataOffset)
    '
    Debug.Print "ByVal SAFEARRAY address:", aPtrByVal, "ByVal data address:", pvDataByVal
    Debug.Print "ByRef SAFEARRAY address:", aPtrByRef, "ByRef data address:", pvDataByRef
    '
    Debug.Print MemLong(pvDataByVal + 4) 'Prints 44
    Debug.Print MemLong(pvDataByRef + 4) 'Prints 44
    '
    arrByRef(1) = 55
    '
    Debug.Print MemLong(pvDataByVal + 4) 'Prints 44
    Debug.Print MemLong(pvDataByRef + 4) 'Prints 55
    '
    arrByVal(1) = 77
    '
    Debug.Print MemLong(pvDataByVal + 4) 'Prints 77
    Debug.Print MemLong(pvDataByRef + 4) 'Prints 55
End Sub

So, we need to pass the wrapped array ByRef so that ArrPtr returns the correct address of the SAFEARRAY structure.