Excel VBA - Add 1-dimensional array to multi-dimen

Question

I have a question regarding "creating a matrix" out of single arrays wihtout having to loop through:

From a function I get back one array with data (Return_Calc, rows = n-1). I am looking for something like

    Output(n-1, j-1) = Return_Calc(Nav_Range)

At the moment, I am doing this:

    Temp = Return_Calc(Nav_range)

    For i = 1 To n - 1

        Output(i - 1, j - 1) = Temp(i - 1)

    Next i 

The current option works. I was just wondering if there is another possibility without looping. Thanks for your help!

Answer 1

I'm not sure if you would be happy with that proposal. It presents possibility of creating Array-of-Arrays which, in some situation, would work similar to Multidimensional Array. You could consider solving your problems this way.

Here is a sample code how to create and which way you could retrieve data from final array.

Sub Array_Workaround()

    Dim oneDimArrA, oneDimArrB
        oneDimArrA = Array(1, 2, 3, 4)
        oneDimArrB = Array("A", "B", "C", "D")
    Dim multiDimArr

    'creating multidemmnsional array
    multiDimArr = Array(oneDimArrA, oneDimArrB)

    'get element- different to standard syntax
    Debug.Print multiDimArr(0)(0)   '--> 1
    Debug.Print multiDimArr(0)(1)   '--> 2
    Debug.Print multiDimArr(1)(1)   '--> B

End Sub

There is one important benefit of presented solution- each internal array can have different dimension.