Reputation: 594
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
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