Excel & VBA: Skip calculating formula in Macro

excel, vba

Solution

- place the formula in the cell with a prefix character

- continue the macro

- "activate" the formula

for example:

Sub dural()
    With Range("A1")
        .Value = "'=1+2"
        MsgBox " "
        .Value = .Value
    End With
End Sub

Problem

I'm having a rather simple issue with Macro. In it, I assign a formula to a cell. I would like it not to calculate it at first and do calculation only after some other part of Macro is finished. I thought I would do something like this: ``` Application.Calculation = xlCalculationManual Cells(StartRow, 4 + i).Formula = "FORMULA" ... Application.Calculation = xlCalculationAutomatic ``` But that doesn't work. It stops automatic calculations, but not for that cell - it still performs the calculation right after I assign the formula. Is there a way to skip it? To clarify the exact prupose of this: in my actual code I'm assigning a formula to a group of cells in a cycle. Everytime I assign it to one cell - it calculates it. I figured if I will first assign them all and then do the calculation - it would be faster. As a matter of fact it is. So instead of assigning it in a cycle, I assign it to the first cell and then do autofill. Autofilled formulas wait until I enable automatic calculation and I get a much faster macro. However, the initial assignemnt is still calculated which makes macro almost twice as slow.

Original source