return an entire row of a multidimensional array in VBA to a one dimensional array

Question

Is there any way to return the value of an entire row of a multidimensional array to a one dimensional array In VBA?

Something like, arr_1dim = arr_2dim(3,:) is a matlab expression for assigning the row 3 of arr_2dim array to arr_1dim in one single stretch.

Is there any similar less expensive method in Excel VBA?

Gabriellegabrielli

Possible duplicate of How do I slice an array in Excel VBA? – Want

Answer 1

No there is no VBA-function to get a row or column. You can only write it yourself, or take a look here:
http://www.cpearson.com/excel/vbaarrays.htm

Chocolate

Specifically, GetRow (to get arr(0)) / GetColumn (to get arr(1..n,0)) – Bema

I benchmarked this GetRow (simplest loop approach) against the most voted solution (Application.WorksheetFunction.Index(array, rowyouwant, 0)) and this was 6x faster. – Aracelis

Answer 2

There is a simple way to get a column or a row of a two-dimensional array. Assign a zero to column to get the row, or assign a zero to row to get the column, thus:

Application.WorksheetFunction.Index(array, 0, columnyouwant) /* or */
Application.WorksheetFunction.Index(array, rowyouwant, 0)

See here: How do I slice an array in Excel VBA?

Edmee

Answer 3

This is what I do to easily print out one dimension of a multidimensional array.

Basically, I define a new, 1D array and stuff it with the values from the bigger array.

Example (3D to 1D to Printout):

Sub PrintItOut()
     ReDim big_array(10,5,n) as Variant, small_array(n) as Variant

     'use multidimensional array


     'place multi-dimensional values into the 1D array

     For i = 0 to n
            small_array(i) = big_array(0, 0, i)
     Next

     Range(Cells(1, 1), Cells(1, n + 1)) = small_array
End Sub

That's how I do it. I hope that makes sense to whoever may be reading it. I think it's a very simple way to do it.

Sheikh

Answer 4

Matlab is such an awesome application to work when it comes to matrices, arrays, vectors... ;) But Excel is not that bad, it too is matrix based.

So Assuming you do not want to loop through. You may simply ouput your multi-D array into a worksheet using Transpose function.

Then pull a Row to your desired range size into an array using Transpose.

Dim vArr as Variant

'--output multi-D array into worksheet
Sheets(2).Range("E2").Resize(UBound(multiDArray) + 1, _
UBound(Application.Transpose(multiDArray))) = multiDArray

'--pull back the row you need: we double transpose here to get 1D. Coz single transpose
'-- results in 2D array..
vArr = WorksheetFunctions.Transpose( _ 
       WorksheetFunctions.Transpose(Sheets(1).Range("A2:G2").Value)) 

To be absolutely dynamic, you may resize your range A2:G2 with a dynamic row count using the multi-D array row upperbound :)

Timotheus

@Surya please take a look at the edit. Initial version will only give you a 2D array for single row-multi column transpose. You need to do a double transpose in that case ;) I could give you the code, but I want you to have some fun writing the code. Please try out and comment. Happy to help you if you have more questions. – Timotheus

Answer 5

Since I had this question by myself recently, I want to share my code. I wrote a ready-to-use function where you can choose if you want a column or a row to be extracted:

'*** Modul 1, define function ***

Function getOneLine(array2D As Variant, lineIndex As Integer, choice As String) As Variant
' returning one column or row of a 2D array
' array2D: 2 dimensional Array
' lineIndex: the index of column or row, starting at 0
' choice: "c" for column or "r" for row

    Dim i, n As Integer
    Dim oneLine As Variant


    If choice = "c" Then

    n = UBound(array2D, 2)
    ReDim oneLine(n)

        For i = 0 To n
            oneLine(i) = array2D(lineIndex, i)
        Next

    getOneLine = oneLine

    End If


    If choice = "r" Then

    n = UBound(array2D, 1)
    ReDim oneLine(n)

        For i = 0 To n
            oneLine(i) = array2D(i, lineIndex)
        Next

    getOneLine = oneLine

    End If

End Function


'*** Modul 2, call function ***

Sub SomeProcess()
    ' Creating a 3x2 Matrix
    ' (In VBA-arrays the column is indexed before the rows
    '  starting at 0. So 3x2 looks like 1x2)
    Dim SomeArray(1, 2) As Variant
        SomeArray(0, 0) = 1
        SomeArray(0, 1) = 2
        SomeArray(0, 2) = 3
        SomeArray(1, 0) = 4
        SomeArray(1, 1) = 5
        SomeArray(1, 2) = 6

    Dim oneLine As Variant
        oneLine = getOneLine(SomeArray, 1, "c")

    Debug.Print oneLine(2)
        ' prints 6

End Sub

Huey

Answer 6

A way to do something close:

ReDim TwoDim(10) As Variant
TwoDim(2) = Array("a", "b")
Debug.Print TwoDim(2)(1) 'Shows "a"
Debug.Print Join(TwoDim(2), "") 'Shows "ab"

Romine

Welcome to SO Rafael! Your answer is innovative in the use of Join, however, it does not do what the question asks "Return an entire row to a ONE DIMENSIONAL ARRAY", instead it returns a string. If you want to share your knowledge you can create questions with self answeres in SO. – Count