excel - Assign 1D array from 2D array in VBA?

Question

So maybe its monday, maybe I'm stupid.. But I can't for the life of me figure out a good way to get a 1D array from a one row in a 2D array. (maybe it's not a "real" 2D array?)

Anyways, I have an array that I defined like this: dim myArr(2,4) as variant

I filled it with values 1 - 15 so it looks like:

1,2,3,4,5
6,7,8,9,10
11,12,13,14,15

So now what I want is a single row of that array. the only way I can figure out is by doing this:

    dim temp() as variant
    ReDim temp(lbound(myArr,2) to ubound(myArr,2))

    For i = 0 To 0
        For j = LBound(myArr, 2) To UBound(myArr, 2)
            temp(j) = myArr(i, j)
        Next
    Next

But I don't like that because if the array got huge, then the time it took to do that might be considerably longer. I THOUGHT I should be able to do:

dim temp as variant
temp = myArr(0) 'since myArr(0) is a 1D array with 5 spots right?

but no.. I get a 'wrong number of dimentions' error.

I also dug back through previous things and found this question: How to compare two entire rows in a sheet and the answer by tim thomas shows the use of transpose, and mentions if you were comparing columns you'd use only transpose once, but it doesn't work.. if I made it like: dim myArr(2,0) as variant the transpose works, but not the way I have it now.

I found countless answers to this question in other languages like C++ and Python but I'm not familiar at all with either so I couldn't interpret them.

Is there a better way of doing this? Or am I stuck with this diouble loop that I don't like?

Thanks!

Answer 1

Here's a self-contained illustration of one way to "slice" a 2-D array:

Sub ArraySlicing()

Dim arr(1 To 5, 1 To 5)
Dim slice
Dim x, y
Dim a As Application

    'Populate a sample 2-D array with values...
    For y = 1 To 5
    For x = 1 To 5
        arr(y, x) = "R" & y & ":C" & x
    Next x
    Next y
    '...done setting up sample array

    Set a = Application 'this is just to shorten the following lines

    'Example 1: get the first "column"
    slice = a.Transpose(a.Index(arr, 0, 1))
    Debug.Print Join(slice, ", ") 'display what we got 

    'Example 2: get second "row" (note double transpose)
    slice = a.Transpose(a.Transpose(a.Index(arr, 2, 0)))
    Debug.Print Join(slice, ", ") 'display what we got

End Sub

Index() gives you a 2-d array - (x,1) or (1,x) - Transpose() will convert that to a 1-d array.