Improve Speed by desabling Automatic Calculation

[Alex St-Pierre]

I have a Macro which update an excel sheet (by opening an other excel file
and copy-paste data) When I update the file, the macro takes about 1 sec. to
execute.

Often, I have an other sheet which is linked to the updated excel sheet; it
takes about 2 minutes to execute the Macro.

I decided to desabled automatic calculation by doing:
Application.Calculation = xlManual
'Program..
Application.Calculation = xlAutomatic
Application.Calculate ('this doesn't affect the speed.. very fast)

After doing this, the Macro takes 10 seconds to execute.
If I break down the link, it takes 1 second.

Why the Macro doesn't 1 second if I desables automatic calculation ?
Is there a way to execute my Macro in 1 second by suspending all excel
refresh ?

Thank you!

 

[Charles Williams]

Hi Alex,

When you have more than 1 workbook open Excel calculates all the open
workbooks, so if you have your linked workbook open it gets recalculated as
well and so it takes more time.

Setting Application.Calculation back to automatic from manual makes Excel
Recalculate whatever needs calculating (this presumably takes about 10
seconds). Then the next application.calculate does not do anything because
there is nothing that needs calculating.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Alex St-Pierre]

Hi Charles,
The workbook that is open to get information is closed before I use
application.calculate command. I will remove it to.

I tried removing both (Application.Calculation = xlAutomatic and
Application.Calculate) at the end of program and it takes 3 seconds to run.
What I see is that the program is faster when I make a copy-paste data from
sheet#2 because the size of excel workbook is less.
If file = 0.3 megs.. speed = 1 sec.
If file = 1.5 megs.. speed = 2 sec.
If file = 3 megs.. speed = 3 sec.
It's very strange for small files that the impact is so high.

Do you know how to refer to the mode of calculation before execution
(Automatif or Manual)
example:
a = Application.Calculation.Mode '?
Application.Calculation = xlManual
'program..
If a = automatic then
Application.Calculation = xlAutomatic
End If

 

[Charles Williams]

dim lCalcSave as long

lCalcSave=application.calculation

....

if lcalcsave=xlCalculationAutomatic then

 

[Alex St-Pierre]

What I see is that the program is faster when I make a copy-paste dataWhat I was saying that the speed of Macro depend of the file size is not true.
I have test (in the same file), the execution of two sheets (same Macro),
One sheet is linked to an other one. This takes 3 seconds to execute and
it's not the refresh time because I have removed the line
"Application.Calculation = xlAutomatic" (let to manual and erased
ActiveWorkbook.Calculate)

I have duplicated this sheet and execute the Macro on the other sheet.. this
takes 1 sec. to execute...
So, if the sheet is linked to an other, it slow down the process even if
there is no calculation done.. Can I do something for that ?