vba 在VBA中从二维数组分配一维数组？

Assign 1D array from 2D array in VBA?

提问by user1759942

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

无论如何，我有一个这样定义的数组： dim myArr(2,4) as variant

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

我用值 1 - 15 填充它，所以它看起来像：

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 sheetand 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 variantthe transpose works, but not the way I have it now.

我还翻了一遍以前的东西，发现了这个问题：How to compare two entire rows in a sheetand the answer by tim thomas 显示了转置的使用，并提到如果你在比较列，你只使用转置一次，但它不起作用..如果我这样做：dim myArr(2,0) as variant转置有效，但不是我现在拥有的方式。

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.

我在 C++ 和 Python 等其他语言中找到了无数关于这个问题的答案，但我对它们都不熟悉，所以我无法解释它们。

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

有没有更好的方法来做到这一点？还是我被这个我不喜欢的双循环困住了？

Thanks!

谢谢！

回答by Tim Williams

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.

Index() 为您提供一个二维数组 - (x,1) 或 (1,x) - Transpose() 会将其转换为一维数组。

回答by dathanm

Based on Tim Williams' answer I created a self contained function that will slice out one index of a two-dimensional array and return a one-dimensional array.

根据 Tim Williams 的回答，我创建了一个自包含函数，它将切出二维数组的一个索引并返回一个一维数组。

Function Slice_String_Array(arr As Variant, dir As Boolean, index As Long) As Variant()
'This function recieves a two-dimensional array, slices one element of it (either a 'column' or a 'row'), and
'returns a one dimensional array with the values.  Both the array passed in the arguments (arr) and the
'returned array are of type Variant.
'############################################################################################
'arr is the two-dimensional array being sliced
'"dir" is used for direction, 0 (False) is vertical (column) and 1 (True) is horizontal (column)
'"index" is the element number (column number or row number) you want returned
'############################################################################################

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

If dir = 0 Then 'slice a column
    slice = a.Transpose(a.index(arr, 0, index))
Else    'slice a row
    slice = a.Transpose(a.Transpose(a.index(arr, index, 0)))
End If

Slice_String_Array = slice

End Function

回答by pstraton

@dathanm: A good initial approach but there are a couple of points that you seem to be unclear about:

@dathanm：一个很好的初始方法，但有几点您似乎不清楚：

1. In VB, there is no need to explicitly compare Boolean values with zero. The VBA interpreter automatically takes care of that (comparing <> 0, actually, in order to test for a True condition).
2. (@ Tim Williams too): in your row-slice design, calling the Transpose method is unnecessary because Index returns a 1D array of row values, ready to use as-is. As designed, the two nested Transpose calls are essentially a No-Op. For a full explanation of this, see technical note #3, in the header commentary of my version, below.
3. The Index method assumes one-based indexing, so your code should check for a passed VB array that is not one-based (very common) and adjust for that case.
4. A further technicality is that, if the calling code passes a jagged array-of-arrays (not uncommon in VB), the Index method will fail on column operations, so your code should check for that case.
5. Lastly, it should ensure that the returned sliced-array's base is the same as the corresponding base of the original 2D array, in the sliced dimension, since that isn't necessarily the case by default when the Index or Transpose methods are involved.

1. 在 VB 中，不需要显式地将布尔值与零进行比较。VBA 解释器会自动处理（比较 <> 0，实际上是为了测试 True 条件）。
2. （@ Tim Williams 也是）：在您的行切片设计中，不需要调用 Transpose 方法，因为 Index 返回行值的一维数组，可以按原样使用。按照设计，两个嵌套的 Transpose 调用本质上是一个 No-Op。有关此问题的完整解释，请参阅下面我的版本的标题评论中的技术说明 #3。
3. Index 方法假定索引是基于 1 的，因此您的代码应该检查传递的不是基于 1（非常常见）的 VB 数组并针对这种情况进行调整。
4. 进一步的技术性是，如果调用代码传递一个锯齿状的数组数组（在 VB 中并不少见），Index 方法将在列操作上失败，因此您的代码应该检查这种情况。
5. 最后，它应该确保返回的切片数组的基数与原始二维数组的相应基数在切片维度上相同，因为当涉及 Index 或 Transpose 方法时，默认情况下不一定是这种情况。

Here's my version, for anyone to use as needed:

这是我的版本，任何人都可以根据需要使用：

Function ArraySlice(Arr2D As Variant, ByCol As Boolean, SliceIdx As Long) As Variant
    '
    'Returns a 1D row or column array "slice" of the specified 2D array.
    '
    'PARAMETERS:
    '
    '   Arr2D       The 2D array from which a row/column slice is to be extracted.  The passed array may be of
    '               any data type.
    '
    '   ByCol       True = slice a column; False = slice a row.
    '
    '   SliceIdx    The array-index of the row or column to be sliced from the 2D array.  Note that the meaning
    '               of SliceIdx value depends on the base of the 2D array's dimensions: a SliceIdx of 1 will be
    '               the first row/column of a one-based array but the second row/column of a zero-based array.
    '
    'RETURN VALUES: If successful, returns a sliced 1D row or column (Variant) array.  If unsuccessful, returns
    '               an Empty Variant status.  Note that, though an array of any data type may be passed to this
    '               routine, the variable receiving the returned array must be a Variant because whole-array
    '               assignment is implemented for Variants only in VB/VBA.
    '
    'TECHNICAL NOTES:
    '
    '   1. IMPORTANT: Though arrays-of-arrays are supported by this routine, jagged arrays-of-arrays (which are
    '      possible in VBA) would be very complex to handle for column-slice operations, so this routine does not
    '      support them for that case and will throw an error message accordingly.  Also, for column-slice
    '      operations on rectangular arrays-of-arrays, this routine assumes that each row-array has the same
    '      LBound (0 or 1 or whatever), which is a condition that can be violated in VB but is extremely unusual.
    '      This routine will throw an error in that case as well.
    '
    '   2. The Application.Index method, used by this routine has two forms, an Array form and a Reference form.
    '      When used by this routine in its Array form, an array (as opposed to a worksheet cell-range reference)
    '      is passed to it and it returns an array as its return value.  In this usage, it doesn't actually
    '      operate on an Excel worksheet range and is therefore very fast, actually faster than any VBA-coded
    '      equivalent algorithm, because its functionality is implemented by Excel's underlying compiled-code
    '      engine.
    '
    '   3. The Index method and the Transpose method are "orientation-aware" with regard to the row/column
    '      orientation of the passed array argument.  For a multi-row and multi-column rectangular array, the
    '      orientation question is moot but for a single-row or single-column array it isn't.  Without those
    '      methods' orientation-awareness, they would require an additional optional parameter that the calling
    '      code would have to set (but only for single-row/column arrays) to inform the methods of the otherwise-
    '      ambiguous orientation of the passed array.  Such a design would further complicate the call interface
    '      as well as the calling code.
    '
    '      Microsoft's solution was to implement the required orientation-awareness by defining single-row arrays
    '      as a simple 1D array containing that row's elements (i.e. RowValues(col-num)), and defining single-
    '      column arrays as a degenerate 2D array containing a single column of each row's value for that column
    '      (i.e. ColValues(row-num, 1)).  That is, those methods determine the orientation of a passed single-
    '      row/column array by simply checking whether it is a 1D array (row) or 2D array (column).  And any
    '      single-row/column array returned by them also conforms to that scheme.  (Technically, it treats the
    '      passed array as an array-of-arrays, regardless of its implementation in VBA, which is consistent with
    '      how simple 2D arrays are implemented in C/C++, the language of Excel's compiled-code engine.)
    '
    '      Consequently, to get a true 1D array of column values, the Index method's returned (degenerate) 2D
    '      array may be passed to the Transpose method, to convert its column-orientation to row-orientation in
    '      the Transpose method's context.  But in the calling-code's context, it becomes just an independent
    '      1D array containing the specified column's values.
    '
    'AUTHOR: Peter Straton
    '
    '*************************************************************************************************************

    Const NA As Long = 0

    Dim Arr1DSlice As Variant 'NOTE: Some slice operations require a subsequent shift of the sliced array's
                              'base in order to make it match the corresponding base of the original 2D array,
                              'in the sliced dimension.  But, because this function's ArraySlice variable is a
                              'Variant that's also the function's return value, you can't just use Redim Preserve
                              'to change its base.  Instead, you must us a local (shiftable) Variant and then
                              'assign that array to the function's return value before returning.
    Dim BaseOffset As Long
    Dim ColIdxLBound As Long
    Dim ColIdxUBound As Long
    Dim i As Long
    Dim IndexFncOffset As Long
    Dim IsArrayOfArrays As Variant  'Variant: can be Empty (undefined) or Boolean
    Dim RowIdxLBound As Long
    Dim RowIdxUBound As Long


    Arr1DSlice = Empty  'Default: failure status

    'First, determine whether Arr2D is a 2D array or array-of-arrays because they are processed differently.

    On Error Resume Next
    RowIdxLBound = LBound(Arr2D)
    If Err <> 0 Then Exit Function  'Not an array, so exit with failure status

    RowIdxUBound = UBound(Arr2D)
    IsArrayOfArrays = IsArray(Arr2D(RowIdxLBound, RowIdxLBound))
    If IsEmpty(IsArrayOfArrays) Then IsArrayOfArrays = IsArray(Arr2D(RowIdxLBound))
    On Error GoTo 0

    'Do the slice operation

    With Application
    If ByCol Then
        If IsArrayOfArrays Then
            ColIdxLBound = LBound(Arr2D(RowIdxLBound))  'Assumes consistent column-index LBounds and UBounds for
            ColIdxUBound = UBound(Arr2D(RowIdxLBound))  'each row in the array-of-arrays, but...

            'Ensure that it doesn't have inconsistent column-index LBounds and isn't a jagged array-of-arrays
            '(neither of which are supported) by checking each row's column-index bounds.

            For i = RowIdxLBound To RowIdxUBound
                If LBound(Arr2D(i)) <> ColIdxLBound Then
                    MsgBox "Arr1DSlice: Arrays-of-arrays with inconsistent column-index LBounds are not " & _
                           "supported for column operations.", vbOKOnly + vbCritical, "PROGRAMMING ERROR"
                    Exit Function
                End If
                If UBound(Arr2D(i)) <> ColIdxUBound Then
                    MsgBox "Arr1DSlice: Jagged arrays-of-arrays are not supported for column operations.", _
                            vbOKOnly + vbCritical, "PROGRAMMING ERROR"
                    Exit Function
                End If
            Next i

        Else 'Standard 2D array

            ColIdxLBound = LBound(Arr2D, 2)
            ColIdxUBound = UBound(Arr2D, 2)
        End If

        If ColIdxLBound > SliceIdx Then 'If the specified slice-index isn't in-bounds, clip it accordingly
            SliceIdx = ColIdxLBound
        ElseIf ColIdxUBound < SliceIdx Then
            SliceIdx = ColIdxUBound
        End If

        IndexFncOffset = 1 - ColIdxLBound 'The Index method assumes one-based indexing, so must adjust for
                                          'non-one-based arrays when that is the case.

        Arr1DSlice = .index(Arr2D, NA, SliceIdx + IndexFncOffset) 'Returns a degenerate 2D array of a single
                                            'column's corresponding row values (see TECHNICAL NOTE #3, above),
                                            'so must...
        Arr1DSlice = .Transpose(Arr1DSlice) '...use Transpose to convert it to a 1D array (technically a "row"
                                            'array in the Transpose method's context but is actually just an
                                            'independent 1D array in this context).

        'Determine whether the row-dimension base of the original 2D array is different from the base of the
        'resulting sliced array (which is necessarily one-based when generated by the Index method), in order to
        'fix it if necessary, below.  NOTE: the column being sliced from the original 2D array is indexed by its
        'row-position index values, therefore it is the 2D array's row-dimension that must be checked for possible
        'adjustment of the column-sliced 1D array.

        BaseOffset = 1 - RowIdxLBound

    Else 'ByRow

        If RowIdxLBound > SliceIdx Then 'If the specified slice-index isn't in-bounds, clip it accordingly
            SliceIdx = RowIdxLBound
        ElseIf RowIdxUBound < SliceIdx Then
            SliceIdx = RowIdxUBound
        End If

        If IsArrayOfArrays Then

            Arr1DSlice = Arr2D(SliceIdx) 'For array-of-arrays, just return the SliceIdx row of the 2D array

            'NOTE: The Index method is not used here so there is no need to check for an array-base adjustment.

        Else 'Standard 2D array

            IndexFncOffset = 1 - RowIdxLBound 'The Index method assumes one-based indexing, so must adjust for
                                              'non-one-based arrays when that is the case.

            Arr1DSlice = .index(Arr2D, SliceIdx + IndexFncOffset, NA) 'Slice out the SliceIdx row

            'NOTE: in the row-slice case, there is no need to transpose (from column array to row array).

            'Determine whether the column-dimension base of the original 2D array is different from the base of
            'the resulting sliced array (which is necessarily one-based when generated by the Index method), in
            'order to fix it if necessary (below).  NOTE: the row being sliced from the original 2D array is
            'indexed by its column-position index values, therefore it is the 2D array's column-dimension that
            'must be checked for possible adjustment of the row-sliced 1D array.

            BaseOffset = 1 - LBound(Arr2D, 2)   '(Is never an array-of-arrays here!)
        End If
    End If

    If BaseOffset <> 0 Then
        'The base of the original 2D array is different from the base of the resulting sliced array, so fix the
        'sliced array to match the original.

        ReDim Preserve Arr1DSlice(LBound(Arr1DSlice) - BaseOffset To UBound(Arr1DSlice) - BaseOffset)
    End If
    End With 'Application

    ArraySlice = Arr1DSlice '(See the technical note at the Arr1DSlice variable's declaration)
End Function

And, here's a handy test routine with which to play with it:

而且，这是一个方便的测试例程，可以使用它：

Sub ArraySliceTest()
    Dim ByCol As Boolean
    Dim ColIdxLBound As Long
    Dim ColIdxUBound As Long
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim m As Long
    Dim PadTabs As String
    Dim RowIdxLBound As Long
    Dim RowIdxUBound As Long
    Dim Sliced As Variant
    Dim SliceIdx As Long
    Dim TempBuf1 As Variant
    Dim TempBuf2 As String
    Dim TestArr() As Variant

    #Const Std2DArray = True    'For array-of-arrays, set to False
    #Const JaggedArray = False  'For jagged array-of-arrays, set to True

'    ByCol = True    'Uncomment for column slice
    ByCol = False   'Uncomment for row slice

'    SliceIdx = -1  'Uncomment slice-index value to be tested...
'    SliceIdx = 0
'    SliceIdx = 1
    SliceIdx = 2
'    SliceIdx = 3
'    SliceIdx = 4

    #If Std2DArray Then
    '    ReDim TestArr(0 To 2, 0 To 3)  'Uncomment test-array dimensions to be tested...
    '    ReDim TestArr(1 To 3, 1 To 4)
        ReDim TestArr(-1 To 1, -1 To 2)
    '    ReDim TestArr(0 To 2, 1 To 4)
    '    ReDim TestArr(0 To 2, -1 To 2)
    '    ReDim TestArr(1 To 3, 0 To 3)
    '    ReDim TestArr(-1 To 1, 0 To 3)

        RowIdxLBound = LBound(TestArr, 1)
        RowIdxUBound = UBound(TestArr, 1)
        ColIdxLBound = LBound(TestArr, 2)
        ColIdxUBound = UBound(TestArr, 2)

        'To demonstrate Variant flexibility, use integers for 2D array

        For i = RowIdxLBound To RowIdxUBound
            n = n + 1
            m = 0   '(Re)init
            TempBuf1 = vbNullString
            For j = ColIdxLBound To ColIdxUBound
                m = m + 1
                TestArr(i, j) = n * 10 + m
            Next j
        Next i

    #Else   'For array-of-arrays:

        'To demonstrate Variant flexibility, use strings for array-of-arrays

        #If Not JaggedArray Then
            TestArr = Array(Array("11", "12", "13", "14"), _
                            Array("21", "22", "23", "24"), _
                            Array("31", "32", "33", "34"))      'Creates an array of arrays.

        #Else
            TestArr = Array(Array("11", "12", "13", "14"), _
                            Array("21", "22"), _
                            Array("31", "32", "33", "34"))   'Creates a jagged array of arrays.

        #End If

        'Test inconsistent col-index LBounds for all rows in an array-of-arrays (unsupported for col slice)

'            Dim X As Variant
'            #If JaggedArray Then
'                X = Array("21", "22")
'            #Else
'                X = Array("21", "22", "23", "24")
'            #End If
'
'            ReDim Preserve X(LBound(X) - 1 To UBound(X) - 1)
'            TestArr(2) = X

        'Test array-of-arrays col-index LBounds other than the default (supported for row & col slice)

'            Dim X As Variant
'            Dim Y As Variant
'            Dim Z As Variant
'            X = Array("11", "12", "13", "14")
'            ReDim Preserve X(LBound(X) + 1 To UBound(X) + 1)
'            Y = Array("21", "22", "23", "24")
'            ReDim Preserve Y(LBound(Y) + 1 To UBound(Y) + 1)
'            Z = Array("31", "32", "33", "34")
'            ReDim Preserve Z(LBound(Z) + 1 To UBound(Z) + 1)
'            ReDim TestArr(0 To 2)
'            TestArr(0) = X
'            TestArr(1) = Y
'            TestArr(2) = Z

        RowIdxLBound = LBound(TestArr)
        RowIdxUBound = UBound(TestArr)
        ColIdxLBound = LBound(TestArr(RowIdxLBound))    'Assumes consistent column-index LBounds and UBounds for
        ColIdxUBound = UBound(TestArr(RowIdxLBound))    'each row in the array-of-arrays (and is used accordingly
                                                        'below).
    #End If 'Std2DArray

    'Print the 2D test array

    Debug.Print vbLf & "+--- " & IIf(ByCol, "Col", "Row") & " Slice ---+"
    TempBuf1 = vbTab
    TempBuf2 = vbTab
    For j = ColIdxLBound To ColIdxUBound
        TempBuf1 = TempBuf1 & Format(j, IIf(j >= 0, " 0", "#0")) & vbTab
        TempBuf2 = TempBuf2 & ".." & vbTab
    Next j
    Debug.Print Trim$(TempBuf1)
    Debug.Print Trim$(TempBuf2)

    For i = RowIdxLBound To RowIdxUBound
        TempBuf1 = vbNullString
        #If Std2DArray Then
            For j = ColIdxLBound To ColIdxUBound
                TempBuf1 = TempBuf1 & TestArr(i, j) & vbTab
            Next j
        #Else
            For j = LBound(TestArr(i)) To UBound(TestArr(i))    'Handles jagged array-of-arrays
                TempBuf1 = TempBuf1 & TestArr(i)(j) & vbTab
            Next j
        #End If
        Debug.Print Format(i, IIf(i >= 0, " 0", "#0")) & ":" & vbTab & Trim$(TempBuf1)
    Next i

    'Get the slice

    Sliced = ArraySlice(Arr2D:=TestArr, ByCol:=ByCol, SliceIdx:=SliceIdx)

    If Not IsEmpty(Sliced) Then
        'Succeeded, so print the 1D slice array

        PadTabs = String(SliceIdx - LBound(Sliced), vbTab)
        Debug.Print
        If ByCol Then
'            Debug.Print vbTab & PadTabs & Format(SliceIdx, IIf(SliceIdx >= 0, " 0", "#0"))
'            Debug.Print vbTab & PadTabs & ".."

            For i = LBound(Sliced) To UBound(Sliced)
                Debug.Print Format(i, IIf(i >= 0, " 0", "#0")) & ": " & PadTabs & Sliced(i)
            Next i
        Else
            TempBuf1 = Format(SliceIdx, IIf(SliceIdx >= 0, " 0", "#0")) & ":" & vbTab
            For i = LBound(Sliced) To UBound(Sliced)
                TempBuf1 = TempBuf1 & Sliced(i) & vbTab
            Next i

            Debug.Print TempBuf1
        End If
    Else
        MsgBox "The ArraySlice function call failed"
    End If

    Debug.Print "+-----------------+"
End Sub

回答by Serban Nicolau

    Public Function VectorizeArrayCols(Arr As Variant) As Variant
        Dim n, nn, n3, k As Long

        n = UBound(Arr, 1)
        nn = UBound(Arr, 2)
        'n = Arr.Columns.Count       'if Public Function Vectorize(Arr As Range) As String
        'nn = Arr.Rows.Count         'if Public Function Vectorize(Arr As Range) As String
        n3 = n * nn

        Dim Vx() As Variant
        ReDim Vx(n3)

        For i = 1 To n
            For j = 1 To nn
                k = i + n * (j - 1)
                Vx(k) = Arr(i, j)
            Next j
        Next i

        VectorizeArrayCols = Vx

    End Function