如何在 Excel 2007 VBA 中“ReDim Preserve”一个二维数组，以便我可以向数组添加行而不是列？

How can I "ReDim Preserve" a 2D Array in Excel 2007 VBA so that I can add rows, not columns, to the array?

提问by user392520

I'm working with a dynamic array in Excel VBA. The number of columns (m) is fixed, however, I do not know how many rows (n) will be required.

我正在 Excel VBA 中使用动态数组。列数 (m) 是固定的，但是，我不知道需要多少行 (n)。

The help documents state that ReDim Preserve myArray(n, m) allows me to make m larger, but not n. However, I need to increase the number of rows (n) while preserving my data, not columns (m)!

帮助文档指出 ReDim Preserve myArray(n, m) 允许我使 m 更大，但不能使 n 更大。但是，我需要在保留数据的同时增加行数 (n)，而不是列数 (m)！

For example, I may have a (5,20) array that I would like to expand to (10,20) while preserving my data.

例如，我可能有一个 (5,20) 数组，我想在保留数据的同时将其扩展为 (10,20)。

It seems that if there were some way to transpose my array, do a ReDim Preserve to expand the number of "columns", then re-transpose my array, I could accomplish what I want.

似乎如果有某种方法可以转置我的数组，请执行 ReDim Preserve 以扩展“列”的数量，然后重新转置我的数组，我可以完成我想要的。

Is this the correct way to do this? If so, how can I do that?

这是正确的方法吗？如果是这样，我该怎么做？

Is there a better way to accomplish what I want?

有没有更好的方法来完成我想要的？

采纳答案by user392520

Solved my own question; here's how I got around my problem. I created a temporary array, copied the contents of myArray to the temporary Array, resized myArray, then copied the contents back from the temp array to myArray.

解决了我自己的问题；这是我解决问题的方法。我创建了一个临时数组，将 myArray 的内容复制到临时数组，调整 myArray 的大小，然后将内容从临时数组复制回 myArray。

tempArray = myArray
ReDim myArray(1 To (UBound(myArray()) * 2), 1 To m)
For i = 1 To n
     For j = 1 To m
          myArray(i, j) = tempArray(i, j)
     Next j
Next i

If anyone can suggest a more efficient way to do this, I'd love to hear it.

如果有人可以建议一种更有效的方法来做到这一点，我很想听听。

回答by jtolle

One way to do what you want is to use a 1-D array that contains 1-D arrays instead of a 2-D array. Then you can ReDim Preserve the outer array all you want. If you're returning the outer array from a function, Excel will do the right thing and coerce it to a 2-D array.

做你想做的一种方法是使用包含一维数组而不是二维数组的一维数组。然后你可以 ReDim 保留所有你想要的外部数组。如果您从函数返回外部数组，Excel 会做正确的事情并将其强制转换为二维数组。

For example, the function below will return a 3x2 array to the cells it's called from:

例如，下面的函数将向调用它的单元格返回一个 3x2 数组：

Public Function nested()
    Dim outer
    outer = Array(Array(1, 2), Array(3, 4))

    ReDim Preserve outer(1 To 3)

    outer(3) = Array(5, 6)

    nested = outer
End Function

My answer to these questions might also be useful to you: Pass multidimensional array into Excel UDF in VBAand VBA pasting 3 dimensional array into sheet

我对这些问题的回答可能对您也有用：Pass multidimensional array into Excel UDF in VBAand VBA pasteing 3 dimensional array into sheet

Of course, if you're not returning this from a UDF, you'll have to coerce it yourself. An easy way to do that without writing looping code is to do this:

当然，如果你不是从 UDF 返回它，你就必须自己强制它。不编写循环代码的一种简单方法是这样做：

Dim coerced
coerced = Application.Index(outer, 0, 0)

This is just calling Excel's built-in INDEX function, and the zeros mean that you want back all of your rows and all of your columns. Excel will coerce your 1-D array of 1-D arrays to a 2-D array automatically. (Caveat: there are some size limitations, but they are much bigger than 10x20.)

这只是调用 Excel 的内置 INDEX 函数，零表示您想要返回所有行和所有列。Excel 将自动将一维数组的一维数组强制转换为二维数组。（警告：有一些尺寸限制，但它们比 10x20 大得多。）

回答by user2307527

One way how you could sove it is indeed by a double transpose with a change on the number of columns in between. This will however only work for two-dimensional arrays. It is done as follows:

解决它的一种方法确实是通过双重转置，并改变其间的列数。然而，这仅适用于二维数组。操作如下：

' Adding one row is done by a double transposing and adding a column in between.
' (Excel VBA does not allow to change the size of the non-last dimension of a
' multidimensional array.)
myArray = Application.Transpose(myArray)
ReDim Preserve myArray(1 To m, 1 To n + 1)
myArray= Application.Transpose(myArray)

Of course mand ncan be deduced as follows:

当然m，n可以推论如下：

m = UBound(myArray, 1)
n = UBound(myArray, 2)

So you use the built-in transpose functionality of Excel itself. As mentioned in the code comments, this will not work for higher order matrices.

因此，您可以使用 Excel 本身的内置转置功能。如代码注释中所述，这不适用于高阶矩阵。

回答by Nikolay Ivanov

If you are developer - what is the difference between rows and columns? Using array(N, 2) (if you have 2 columns) is the same as array(2, N) - for which you can

如果您是开发人员 - 行和列之间有什么区别？使用 array(N, 2)（如果您有 2 列）与 array(2, N) 相同 - 您可以

ReDim Preserve arr(1 to 2, 1 to N+1). 

And the difference for you (as developer) will be to put the variable from the cycle in second place, instead of the first one:

您（作为开发人员）的不同之处在于将循环中的变量放在第二位，而不是第一个：

N = ubound(arr)
FOR i=1 to N
    GetColumn1Value = arr(1, i)
    GetColumn2Value = arr(2, i)
NEXT i

Or you want this:

或者你想要这个：

N = ubound(arr)
FOR i=1 to N
    GetColumn1Value = arr(i, 1)
    GetColumn2Value = arr(i, 2)
NEXT i

What is the difference?

有什么不同？

回答by Earl

The word 'transpose' immediately leaps to mind. You could simply enter data into the 2D array by flipping the columns and rows (i.e. transpose), effectively allowing you to make n (now the number of columns, but storing row values) larger when you require.

“转置”这个词立即跃入脑海。您可以简单地通过翻转列和行（即转置）将数据输入到 2D 数组中，有效地允许您在需要时使 n（现在是列数，但存储行值）更大。

To reference the values, say in a double loop, swap the indices around. E.g. rather go from i = 1 to n and j = 1 to m where you reference value(i, j) , use i = 1 to m and j = 1 to n.

要引用这些值，例如在双循环中，交换索引。例如，宁可从 i = 1 到 n 和 j = 1 到 m 引用 value(i, j) ，使用 i = 1 到 m 和 j = 1 到 n。

回答by Stan Rogers

No way to determine the number of elements in the first dimension? Bummer. For a two-dimensional array with a fixed second dimension, you might want to consider making it an array of Types ("structs" in other languages) instead. That will allow you to use Redim Preserve, and still leaves you with a reasonable way to add and access values, though you'll now be accessing the second dimension as named members of the Type rather than is index values.

没办法确定第一维的元素个数？无赖。对于具有固定第二维的二维数组，您可能需要考虑将其设为类型数组（其他语言中的“结构”）。这将允许您使用 Redim Preserve，并且仍然为您提供添加和访问值的合理方法，尽管您现在将作为 Type 的命名成员而不是索引值访问第二个维度。

回答by sifar

coercing or Slicing doesnt seem to work with Index( or Match(Index( when i want to filter array (w/o loops) based on multiple criteria, when the size of data spans greater than 2^16 rows (~ 92000 rows).

当数据大小超过 2^16 行（~ 92000 行）时，强制或切片似乎不适用于 Index( 或 Match(Index( 当我想根据多个条件过滤数组（无循环）时）。

Run-Time error '13':

Type Mismatch

Transpose doesnt work with large recordsets and so also double Transpose does not work. isn't there anyway to filter an array and grab data without resorting to multiple loops?

Transpose 不适用于大型记录集，因此双 Transpose 也不起作用。无论如何都不能过滤数组并获取数据而不诉诸多个循环吗？

I am thinking of trying the dictionary way or ADO with Excel.

我正在考虑使用 Excel 尝试字典方式或 ADO。

回答by Dyrner

An array with 2 dimensions, where the number of columns are fixed and the number of rows are dynamic, can be created like this:

一个二维数组，其中列数是固定的，行数是动态的，可以这样创建：

Sub test2DimArray()
Dim Arr2D() As String
Dim NumberOfCol As Long
Dim I As Long, J As Long, x As Long
Dim tmpValue As String, tmpValue2 As String, tmpValue3 As String

NumberOfCol = 3
J = 1
Debug.Print "Run " & Now()
Debug.Print "Sheet content"
Debug.Print "Row   col1     col2     col3"

For I = 1 To 10
tmpValue = Cells(I, 1).Value
tmpValue2 = Cells(I, 2).Value
tmpValue3 = Cells(I, 3).Value
Debug.Print I & " =    " & tmpValue & "     " & tmpValue2 & "     " & tmpValue3
    If Len(tmpValue) > 0 Then
        ReDim Preserve Arr2D(NumberOfCol, 1 To J)
        Arr2D(1, J) = tmpValue
        Arr2D(2, J) = tmpValue2
        Arr2D(3, J) = tmpValue3
        J = J + 1
    End If
Next

'check array values
Debug.Print vbLf; "arr2d content"
Debug.Print "Row   col1     col2     col3"

For x = LBound(Arr2D, 2) To UBound(Arr2D, 2)
Debug.Print x & " =   " & Arr2D(1, x) & "        " & Arr2D(2, x) & "        " & Arr2D(3, x)
Next

Debug.Print "========================="
End Sub

TempValue read from cells A1:A10, if there is a value in cell Ax, it redim the array with +1, and add Tempvalue to array col1, add contents in Bx to array col2 and contents in Cx to array col3. If length of Ax-value is 0, it does not add anything to the array.

TempValue 从单元格 A1:A10 中读取，如果单元格 Ax 中有值，则用 +1 重新调整数组，并将 Tempvalue 添加到数组 col1，将 Bx 中的内容添加到数组 col2，将 Cx 中的内容添加到数组 col3。如果 Ax 值的长度为 0，则不会向数组添加任何内容。

Debug.print show results in the "immediate window" in the VB editor.

Debug.print 在 VB 编辑器的“立即窗口”中显示结果。

Without the testing lines, and adding a dynamic data-range the code can be:

如果没有测试行，并添加动态数据范围，代码可以是：

Sub my2DimArray()
Dim Arr2D() As String
Dim NumberOfCol As Long, NumberOfRow As Long
Dim FirstCol As Long, FirstRow As Long, LastCol As Long, LastRow As Long
Dim I As Long, J As Long, X As Long
Dim tmpValue As String, tmpValue2 As String, tmpValue3 As String

'if cells with values start in A1
With ActiveSheet.UsedRange
    NumberOfCol = .Columns.Count
    NumberOfRow = .Rows.Count
End With

'if cells with values starts elsewhere
With ActiveSheet.UsedRange
    FirstCol = .Column
    FirstRow = .Row
    LastCol = .Column + .Columns.Count - 1
    LastRow = .Row + .Rows.Count - 1
End With

J = 1

For I = 1 To NumberOfRow 'or For I = FirstRow to LastRow
tmpValue = Cells(I, 1).Value 'or tmpValue = Cells(I, FirstCol).Value
    If Len(tmpValue) > 0 Then
        ReDim Preserve Arr2D(NumberOfCol, 1 To J)
            For X = 1 To NumberOfCol 'or For X = FirstCol to LastCol
                Arr2D(X, J) = Cells(I, X).Value
            Next X
        J = J + 1
    End If
Next I

End Sub