将VBA中的多维数组的整行返回到一维数组

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

有没有办法将多维数组的整行的值返回到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.

例如，arr_1dim = arr_2dim（3，:)是一个matlab表达式，用于在一个拉伸中将arr_2dim数组的第3行分配给arr_1dim。

Is there any similar less expensive method in Excel VBA?

在Excel VBA中是否有类似的较便宜的方法？

5 个解决方案

#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

没有VBA函数来获取行或列。你只能自己写，或者看看这里：http：//www.cpearson.com/excel/vbaarrays.htm

#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?

请参见此处：如何在Excel VBA中切片数组？

#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.

基本上，我定义了一个新的1D数组，并用更大数组的值填充它。

Example (3D to 1D to Printout):

示例（3D到1D到打印输出）：

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.

我就是这样做的。我希望对于那些阅读它的人来说这是有道理的。我认为这是一种非常简单的方法。

#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.

当涉及矩阵，数组，向量时，Matlab是一个非常棒的应用程序......;）但是Excel也不是那么糟糕，它也是基于矩阵的。

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

所以假设你不想循环。您可以使用Transpose函数将多D数组输出到工作表中。

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

然后使用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 :)

要绝对动态，您可以使用多D数组行上限来调整动态行数的范围A2：G2：

#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

#1