title: Best workaround for VBA Transpose array length limit?
body: <p>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:</p>
  
  <pre><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" &amp; iter) = Application.WorksheetFunction.Transpose(totalgoals)
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>This throws a Type Mismatch error on the next to last line because <code>Transpose</code> can only handle arrays of length up to 2^16 (~64,000). So, how should I workaround this? What is my most efficient option?</p>
  
  <p>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?</p>
  
  <p>Or better yet, is there anyway to do this <em>without</em> having to loop through the values again?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I provided a bad example because the <code>ReDim Preserve</code> calls were unnecessary. So, consider the following instead where they are necessary.</p>
  
  <pre><code>ReDim totalgoals(1 To 1, 1 To 1) As Variant
  For i = 1 To iter
      ko.Calculate
      If ko.Range("F23") &gt; 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)
  </code></pre>