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

Question

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?

Aleksandra Glesaaen · Accepted Answer · 2018-10-31 15:50:17Z

3

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.

edited Oct 31, 2018 at 15:50

answered Oct 31, 2018 at 15:28

Aleksandra Glesaaen

5945 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

BigBen Over a year ago

Seems like overkill, unless you wanted absolute references in each row. Range("A2:A10000").Formula = "=A1"

Aleksandra Glesaaen Over a year ago

@BigBen Interesting, I didn't know it would handle relative references like that. Anyway, for my use case I have some very complicated formulas I need to fill out for a large number of cells and sheets and therefore plan to abstract out the range in the end and save the actual saving for last to minimise run time.

Aleksandra Glesaaen Over a year ago

@Comintern Well, I guess, however I also assumed that .Formula and .Value had slightly different logic as a .Formula naturally is a string, at least on some level. For .Value I totally agree, but what would you say a formula is if not a string? Edit: Also, as I noted in my answer, VBA will gladly accept a String variable as a formula as long as you only set the formula for a single cell. E.g. [A2].Formula = formulae(1) in the example code of the question works "as expected".

YasserKhalil Over a year ago

Instead of using fixed number in loop and in last line, you can use UBound(formulae)

Comintern Over a year ago

It has to handle this: Sheet1.[A1].Formula = "Foo". Note that it adds the explicit ' string "tick" to the start of it. It is definitely type aware - Sheet1.[A1].Formula = 42 is treated as a number. I'm guessing it performs the type check first (because, well, it kind of has to), and then just passes it to Value internally if it didn't get a Variant().

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related