VBA 转置数组长度限制的最佳解决方法？

Best workaround for VBA Transpose array length limit?

提问by Excellll

After running a simulation with 100,000 iterations, I tried to dump the values from each iteration into a column. Here is the gist of the code:

在运行了 100,000 次迭代的模拟后，我尝试将每次迭代的值转储到一列中。这是代码的要点：

Sub test()
Application.ScreenUpdating = False
Dim totalgoals() As Variant, ko As Worksheet, out As Worksheet, iter As Long
Set ko = Sheets("KO Sim")
Set out = Sheets("Monte Carlo")
iter = out.Range("P2").Value
For i = 1 To iter
    ko.Calculate
    If i = 1 Then
        ReDim totalgoals(1 To 1, 1 To 1) As Variant
        totalgoals(1, 1) = ko.Range("F23").Value
    Else
        ReDim Preserve totalgoals(1 To 1, 1 To i) As Variant
        totalgoals(1, i) = ko.Range("F23").Value
    End If
Next i
out.Range("U1:U" & iter) = Application.WorksheetFunction.Transpose(totalgoals)
Application.ScreenUpdating = True
End Sub

This throws a Type Mismatch error on the next to last line because Transposecan only handle arrays of length up to 2^16 (~64,000). So, how should I workaround this? What is my most efficient option?

这会在最后一行的下一行引发类型不匹配错误，因为Transpose只能处理长度不超过 2^16 (~64,000) 的数组。那么，我应该如何解决这个问题？我最有效的选择是什么？

I set up my code to store the values in an array just for the easy output, but it seems that's not going to work for this many values. Would I be better off sticking with arrays and just write my own transpose function (i.e., loop through the array and write the values to a new array), or would I be better off working with a different class from the start, like a collection, if I'm just going to have to loop through the results in the end anyway?

我设置了我的代码来将值存储在一个数组中，只是为了方便输出，但似乎这不适用于这么多值。我最好坚持使用数组并只编写自己的转置函数（即，遍历数组并将值写入新数组），还是最好从一开始就使用不同的类，例如集合，如果我最终只需要遍历结果呢？

Or better yet, is there anyway to do this withouthaving to loop through the values again?

或者更好的是，无论如何都要做到这一点而不必再次遍历这些值？

EDIT:

编辑：

I provided a bad example because the ReDim Preservecalls were unnecessary. So, consider the following instead where they are necessary.

我提供了一个不好的例子，因为ReDim Preserve调用是不必要的。因此，请在需要时考虑以下内容。

ReDim totalgoals(1 To 1, 1 To 1) As Variant
For i = 1 To iter
    ko.Calculate
    If ko.Range("F23") > 100 Then
        If totalgoals(1, 1) = Empty Then
            totalgoals(1, 1) = ko.Range("F23").Value
        Else
            ReDim Preserve totalgoals(1 To 1, 1 To UBound(totalgoals, 2) + 1) As Variant
            totalgoals(1, UBound(totalgoals, 2)) = ko.Range("F23").Value
        End If
    End If
Next i
out.Range("U1").Resize(UBound(totalgoals, 2),1) = Application.WorksheetFunction.Transpose(totalgoals)

采纳答案by RBarryYoung

Here's a version of your code that should work and be faster:

这是您的代码的一个版本，它应该可以工作并且速度更快：

Sub test()
Application.ScreenUpdating = False
Dim totalgoals() As Variant, ko As Worksheet, out As Worksheet, iter As Long
Set ko = Sheets("KO Sim")
Set out = Sheets("Monte Carlo")
iter = out.Range("P2").Value

' ReDim it completely first, already transposed:
ReDim totalgoals(1 To iter, 1 To 1) As Variant

For i = 1 To iter
    ko.Calculate
    totalgoals(i, 1) = ko.Range("F23").Value
Next i
out.Range("U1:U" & iter) = totalgoals
Application.ScreenUpdating = True
End Sub

Here's a version that keeps the conditional ReDims, but manually transposes the array at the end:

这是一个保留条件 ReDims 的版本，但在最后手动转置数组：

Sub test()
Application.ScreenUpdating = False
Dim totalgoals() As Variant, ko As Worksheet, out As Worksheet, iter As Long
Set ko = Sheets("KO Sim")
Set out = Sheets("Monte Carlo")
iter = out.Range("P2").Value
For i = 1 To iter
    ko.Calculate
    If i = 1 Then
        ReDim totalgoals(1 To 1, 1 To 1) As Variant
        totalgoals(1, 1) = ko.Range("F23").Value
    Else
        ReDim Preserve totalgoals(1 To 1, 1 To i) As Variant
        totalgoals(1, i) = ko.Range("F23").Value
    End If
Next i
' manually transpose it
Dim trans() As Variant
ReDim trans(1 to UBound(totalgoals), 1 to 1)
For i = 1 to UBound(totalgoals)
    trans(i, 1) = totalgoals(1, i)
Next i
out.Range("U1:U" & iter) = trans
Application.ScreenUpdating = True
End Sub

回答by Chel

Calculation is definitely going to be the bottleneck here, so (as RBarryYoung says) transposing the array entry-by-entry won't really affect the speed at which your macro runs.

计算肯定会成为这里的瓶颈，因此（如 RBarryYoung 所说）逐项转置数组不会真正影响宏运行的速度。

That said, there isa way to transpose a 2D row to a column (and vice versa) in constant time:

这就是说，有是一种方法，转置行2D在恒定时间的柱（和反之亦然）：

Private Declare Function VarPtrArray Lib "msvbvm60" Alias _
    "VarPtr" (ByRef Var() As Any) As Long
Private Declare Sub GetMem4 Lib "msvbvm60.dll" (src As Any, dest As Any)
Private Declare Sub GetMem8 Lib "msvbvm60.dll" (src As Any, dest As Any)

Sub test()
    Dim totalgoals() As Single
    Dim f As Single
    Dim i As Long, iter As Long

    'dimension totalgoals() with as many cells as we
    'could possibly need, then cut out the excess
    iter = 100000
    ReDim totalgoals(1 To 1, 1 To iter)
    For iter = iter To 1 Step -1
        f = Rnd
        If f > 0.2 Then
            i = i + 1
            totalgoals(1, i) = f
        End If
    Next iter
    ReDim Preserve totalgoals(1 To 1, 1 To i)

    'transpose by swapping array bounds in memory
    Dim u As Currency
    GetMem8 ByVal VarPtrArray(totalgoals) + 16, u
    GetMem8 ByVal VarPtrArray(totalgoals) + 24, _
            ByVal VarPtrArray(totalgoals) + 16
    GetMem8 u, ByVal VarPtrArray(totalgoals) + 24
End Sub