VBA Insert Formulas into Table Very Slow

Question

I have a workbook that on open, turns the used cells into a table, and then injects different formulas into each column. I'm injecting the formulas to save file size from exploding by pre-dragging formulas down farther than needed. The VBA I have to do this works fine, but is incredibly slow. I've already run performance checks and can confirm that the slowness is caused by the formula injection (only 141 rows can be injected per second). I have already done the basic calculation/screen update related optimizations. What else can be done to speed up the following code? (Note I have reduced to the relevant portion of the code):

Sub OptimizeVBA(isOn As Boolean)
  Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
  Application.EnableEvents = Not(isOn)
  Application.ScreenUpdating = Not(isOn)
  ' ActiveSheet.DisplayPageBreaks = Not(isOn)
End Sub

Private Sub Workbook_Open()
    OptimizeVBA True

    Dim ws As Worksheet
    Set ws = Worksheets("Book1")
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Table1")

    tbl.ListColumns("Dollar Share ").DataBodyRange.Formula = "=IFERROR(([@[Dollar Share  ]] - MEDIAN([[Dollar Share  ]])) / STDEV.P([[Dollar Share  ]]), """")"
    tbl.ListColumns("Unit Share ").DataBodyRange.Formula = "=IFERROR(([@[Unit Share  ]] - MEDIAN([[Unit Share  ]])) / STDEV.P([[Unit Share  ]]), """")"
    tbl.ListColumns("Units PSPW ").DataBodyRange.Formula = "=IFERROR(([@[Units PSPW  ]] - MEDIAN([[Units PSPW  ]])) / STDEV.P([[Units PSPW  ]]), """")"
    tbl.ListColumns("Dollar Growth ").DataBodyRange.Formula = "=IFERROR(IF(OR([@[Dollar Growth  ]] = """", [@[Dollars, Yago]] < New_Item_Floor), """", ([@[Dollar Growth  ]] - MEDIAN([[Dollar Growth  ]])) / STDEV.P([[Dollar Growth  ]])), """")"
    tbl.ListColumns("Unit Growth ").DataBodyRange.Formula = "=IFERROR(IF(OR([@[Unit Growth  ]] = """", [@[Dollars, Yago]] < New_Item_Floor), """", ([@[Unit Growth  ]] - MEDIAN([[Unit Growth  ]])) / STDEV.P([[Unit Growth  ]])), """")"
    tbl.ListColumns("Comp Avg % ACV ").DataBodyRange.Formula = "=IFERROR(([@[Comp Avg % ACV  ]] - MEDIAN([[Comp Avg % ACV  ]])) / STDEV.P([[Comp Avg % ACV  ]]), """")"

    OptimizeVBA False
End Sub

I'm aware that selects/other interactions with Excel objects are expensive, but given that the above formulas are different, I couldn't figure out an easy way to reduce the separate interactions for each column.

Notes:

There are actually more injections than shown above, with different formulas of roughly equal string length.
On average tbl will have 16k rows.

Thanks in advance for the help!

Have you tried setting cell values directly? Maybe refer to whole Range, maybe fill one row and then autofill the rest? Not sure, but it's possible that the table does some calculations behind the scenes despite xlCalculationManual? It may be worth a try. — SnowGroomer
Thanks for the response. I haven't tried setting cell values one at a time - I had generally read that would be slower. Re: the idea to fill the first row and then autofill the rest, are you able to share a snippet of how I could accomplish that? I would guess selecting the range is something like tbl.ListRows(1).Range, but how could I set each cell to the different formula and then autofill? — ZaxR
Not one at a time. One row at a time but direclty without reference to the table. Like this: Range(A2:A200000).Value="test" — SnowGroomer
if you set the first cell in a table to a formula all the rows of that table will also have that formula. that said, i am not sure this fits your exact need since you seem to be making the table on the fly after opening the table. however, what if you add the formulas, then make the table? just a thought.... — Scott Holtzman
@ScottHoltzman - I originally created the table first to avoid having complexity from identifying the cell ranges and referencing the columns by hardcoded column letters for the calculations, but if it turns out the table is the issue, that might be the way I have to go I guess — ZaxR

SnowGroomer SnowGroomer · Accepted Answer · 2020-01-08T21:14:38

I'll provide you with my tests here, as this is too much code for comments.

I changed your code in order to paste formulas directly. I tested as strings (due to lack of your table setup) and it is basically immediate. So there must be some table-related shenanigans happening. Please try in your table. You will just need to adjust ranges/worksheet to your needs:

Private Sub Workbook_Open()
    OptimizeVBA True

    Dim ws As Worksheet
    Set ws = ActiveSheet


    ws.Range("A2:A200000").Formula = "=IFERROR(([@[Dollar Share  ]] - MEDIAN([[Dollar Share  ]])) / STDEV.P([[Dollar Share  ]]), """")"
    ws.Range("B2:B200000").Formula = "=IFERROR(([@[Unit Share  ]] - MEDIAN([[Unit Share  ]])) / STDEV.P([[Unit Share  ]]), """")"
    ws.Range("C2:C200000").Formula = "=IFERROR(([@[Units PSPW  ]] - MEDIAN([[Units PSPW  ]])) / STDEV.P([[Units PSPW  ]]), """")"
    ws.Range("D2:D200000").Formula = "=IFERROR(IF(OR([@[Dollar Growth  ]] = """", [@[Dollars, Yago]] < New_Item_Floor), """", ([@[Dollar Growth  ]] - MEDIAN([[Dollar Growth  ]])) / STDEV.P([[Dollar Growth  ]])), """")"
    ws.Range("E2:E200000").Formula = "=IFERROR(IF(OR([@[Unit Growth  ]] = """", [@[Dollars, Yago]] < New_Item_Floor), """", ([@[Unit Growth  ]] - MEDIAN([[Unit Growth  ]])) / STDEV.P([[Unit Growth  ]])), """")"
    ws.Range("F2:F200000").Formula = "=IFERROR(([@[Comp Avg % ACV  ]] - MEDIAN([[Comp Avg % ACV  ]])) / STDEV.P([[Comp Avg % ACV  ]]), """")"

    OptimizeVBA False
End Sub

Your question from comments: how you could use autofill for such a task:

Sub autof()
    Cells(1, 1).Value = 1
    Cells(1, 2).Value = 2
    Range(Cells(1, 1), Cells(1, 2)).AutoFill Range(Cells(1, 1), Cells(10, 2)), xlFillCopy
End Sub

VBA Insert Formulas into Table Very Slow

3 Answers