CODEWITHSUNDEEP
excelvba
Aleksandra Glesaaen
Aleksandra Glesaaen

Reputation: 594

Passing an array of strings to Range().Formula in Excel VBA

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:

Sub Fill_Using_Arrays()
  Dim i As Long
  Dim formulae(1 To 10000) As String

  For i = 1 To 10000
    formulae(i) = "=$A$" & i
  Next i

  [A2].Resize(10000).Formula = formulae
End Sub

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 .Calculate on the range as well, but that did nothing. Is it possible to achieve this behaviour?

Upvotes: 3

Views: 1390

Answers (1)

Aleksandra Glesaaen
Aleksandra Glesaaen

Reputation: 594

While writing this question I found the solution:

The issue was the string array. I generally stay away from Variant due to the slight overhead. As someone coming from C++ declaring everything as variant feels very dirty, but anyway, in this case it is apparently necessary as passing a String will make it paste the value as a literal string.

This is somewhat counter-intuitive to me as passing a single string (not an array) to the .Formula property works. If anyone knows why I would be grateful for that information.

Anyway, working code would be:

Sub Fill_Using_Arrays()
  Dim i As Long
  Dim formulae(1 To 10000) As Variant ' Only change here

  For i = 1 To 10000
    formulae(i) = "=$A$" & i
  Next i

  [A2].Resize(10000).Formula = formulae
End Sub

Edit: To be clear, as a follow up I am curious why this works:

  [A2].Formula = formulae(1)

And fills this as an actual formula, not as a string.

Upvotes: 3

Related Questions