Will change in calculation setting affect the macro results

[ThrottleWorks]

Hi,


I am using following loop.


The following loop takes around 20 minutes to run if I do not change calculation to manual.


But if I change the calculation to Automatic the loop get completd in less than 1 minute.


Application.Calculation = xlManual

Do Until Cells(J, 1) = ""

Cells(J, 21).Resize(1, 2).Value = Cells(J, 23)


Cells(J, 52) = Cells(J, 52) + Cells(J, 48)

Cells(J, 56) = Cells(J, 56) + Cells(J, 48)

Cells(J, 49) = 0


J = J + 1

Loop

Application.Calculation = xlAutomatic


I want to confirm with the experts, is it right to use loop with calculation on manual mode.


I am checking the results for both Auto and Manual, till now both are same.

But I have to check it more to make sure I am not doing any mistake.


I am not able to understand why one code takes 20 minutes & another 1 minutes for same process.


Till now I have checked the results for 4 pairs but results are same.


Can anyone help me in this please.

 

[DaveTurton]

As described by CPearson:


Calculation Mode


Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed. This may cause your workbook to recalculate too often, which will slow down performance. You can prevent Excel from recalculating the workbook by using the statement:


Application.Calculation = xlCalculationManual


At the end of your code, you can set the calculation mode back to automatic with the statement:


Application.Calculation = xlCalculationAutomatic


Remember, though, that when the calculation mode is xlCalculationManual, Excel doesn't update values in cells. If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).

 

[DaveTurton]

Basically when on automatic, excel is constantly updating and recalculating, cells, ranges etc.


If the cells you are referring to in your code have no impact on other cells (via formulas etc) then you will be fine with manual, although always make sure it is switched back to auto at the end of the macro.


If you can post a copy of your spreadsheet we may be able to optimise your code

 

[ThrottleWorks]

Hi Dave, thanks a lot for the help.


You are correct, I have to set the calc on Auto mode again.

I am doing it in the loop.


We are not allowed to upload, I am still reconciling the results.


Have a nice day.