Best workaround for VBA Transpose array length limit?

excel, vba

Solution

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:

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

Problem

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: ``` 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 `Transpose` can only handle arrays of length up to 2^16 (~64,000). So, how should I workaround this? What is my most efficient option? 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 without having to loop through the values again? EDIT: I provided a bad example because the `ReDim Preserve` calls were unnecessary. So, consider the following instead where they are necessary. ``` 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) ```

Original source

Related problems