title: Passing an array of strings to Range().Formula in Excel VBA
body: <p>As everyone knows filling cells with values one by one in VBA is much less efficient than bulk-filling them with an array of values. For the same optimisation reasons I would like to do that with an array of formulas. A very simple example would be something like:</p>
  
  <pre><code>Sub Fill_Using_Arrays()
    Dim i As Long
    Dim formulae(1 To 10000) As String
  
    For i = 1 To 10000
      formulae(i) = "=$A$" &amp; i
    Next i
  
    [A2].Resize(10000).Formula = formulae
  End Sub
  </code></pre>
  
  <p>However, If I do that it will fill the 10k cells with "=$A$1" and so on without actually evaluating the expressions. I tried running a <code>.Calculate</code> on the range as well, but that did nothing. Is it possible to achieve this behaviour?</p>