VBA - entering a formula without calculating it

[Damian86]

Hi all,

When I enter an array formula via vba, for example something like this,...:

Range("C5").Select
For Count = 1 To MyVariable - 1
With ActiveCell
.FormulaArray = "=SUM(IF(((Płatności!C[-1])=R[" & -3 - Count & "]C[0])*((Płatności!C[1]) _
=""DokHandlowe"")*((Płatności!C[13])>=RC[-2])*((Płatności!C[13])<r[1]c[-2]),płatności!c[10]))"

.Offset(1, 0).Select
End With
Next Count<r[1]c[-2]),płatności!c[10]))"

... Excel seems to calculate each formula after inserting it. As you can see, the array formula checks 4 conditions from another sheet so it takes quite a long time when I have 6 similar formulas and MyVariable is 10. It takes about 45-60 seconds on my 2.2 x 2 processor. It even calculates the inserted formulas with the Application.Calculation method set to xlCalculationManual.

So my question is:
Is there a way to insert the formula without calculating it? I calculate the whole sheet by the Sheets("name").calculate method so calculating it beforehand is pointless.
I think about giving an apostrophe before each formula and then delete all apostrophes but something tells me it is a way around and there must be a better solutuion.</r[1]c[-2]),płatności!c[10]))"


PS. I'm sorry for breaking the code in two but the forum wouldn't show the entire code in one <code>.
</code></r[1]c[-2]),płatności!c[10]))"

 

[JackDanIce]

I can't read your formula properly so check my suggestion first before running, but you could try:

' Lines below turn off calculation mode and screen updating
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End Sub


For Count = 1 To MyVariable - 1
    Range ("C" & 4 + Count).FormulaArray = "=SUM(IF(((Platnosci!C[-1])=R(" & -3 - Count & "]C[0])*((Platnosci!C[1]) _
                                            =""DokHandlowe"")*((Platnosci!C[13]) > = RC[-2])*(({Platnosci!C[13])"
Next Count

' This line forces the sheet Range("C5") is on to do full calculation whilst the workbook is in manual calculate mode, delete it if you don't need it.
Range("C5").Parent.Calculate

' Lines below turn calculation mode and screen updating back on
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomaitc
End With

 

[mole999]

already answered

 

[Damian86]

Sorry guys, it's not what I really needed.
The formula itself is not so important. What is important is the fact that after the VB enters my formula into a cell, it tries to calculate it on the spot - as if I entered the formula manually and hit enter (or ctrl+shift+enter).
What I want to do is to enter the formula and force Excel to leave it without calculating.

 

[JackDanIce]

This command line, shown above, turns off Excel's default state to automatically calculate a cell:

[B]Application.Calculation = xlCalculationManual[/B]

I used Application as an object in a With statement to also turn off screenupdating, hence:

[B]With Application[/B]
  .ScreenUpdating = False
[B]  .Calculation = xlCalculationManual[/B]
[B]End With[/B]

Here is a link to a similar asked question previously: http://www.mrexcel.com/forum/excel-questions/81779-application-calculation-=-xlmanual.html

 

[Damian86]

No, the .Calculation = xlCalculationManual won't do the trick I want. Even though I have the .Calculation set to manual, when VBA enters my formula, it immediately calculates it (only the just-entered-formula).
It is the same if you have .Calculation set to Manual and enter a formula into a call - it will calculate it.

What I want to do is to enter a formula without calculating it at all.

 

[JackDanIce]

This seems to work the way you want. In a brand new workbook, fill A1:A50 with numbers 1 to 50, do the same for B1:B50, then run the following:

Sub test()

Dim x As Long

Application.Calculation = xlCalculationManual
ActiveSheet.EnableCalculation = False

For x = 1 To 50
    Range("C" & x).Value = "#SUM(IF($A$1:$A$50=$B$1:$B$50,$A$1:$A$50*$B$1:$B$50,0))"     
Next x

Range("C1").FormulaArray = Replace(Range("C1").Value, "#", "=")
Range("C1:C50").FillDown

ActiveSheet.EnableCalculation = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[mole999]

just a daft thought, the examples i've glanced through are vba to turn off calculate, I am sure you have already set the work book to not calculated

 

[Jonmo1]

Even with calculation set to Manual...
A new formula that is entered is still calculated 'at the time it's entered'.
Even if you enter a formula by hand with calculation set to manual, it still calculates 'at the time it's entered'.

Setting calculation to manula prevents formulas from RE-Calculating when values in the sheet change.