How to enable / disable re-calculation option?

[Eric]

I would like to disable re-calculation option during running my macro.
Does anyone have any suggestions on how to add code to enable / disable
re-calculation option?
Thanks in advance for any suggestions
Eric

For example
Sub mymacro

' I would like to disable re-calculation option here

Macro's coding

' I would like to enable re-calculation option here
End Sub

 

[Dave Peterson]

Dim CalcMode as long

calcmode = application.calculation
application.calculation = xlCalculationManual
'your code here
application.calculation = calcmode

This actually saves the current calculation mode, changes (or keeps) it to
manual, then changes it back to what it was when you started.

 

[Eric]

When I open a worksheet, it will updated all the external link for each cell,
which is connected to another worksheet. I would like to know if the link on
cell A1 is updated, will Excel re-calculate all cells? and
for the next link on cell B1 is updated, will Excel re-calculate all cells
again?
for the next link on cell C1 is updated, will Excel re-calculate all cells
again?
....
does excel work this way?
so I would like to disable the re-calculation option until all links are
updated, then enable the re-calculation option.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Eric]

When I run a macro as shown below, how does excel define the application
area?

calcmode = application.calculation
application.calculation = xlCalculationManual
'open worksheet2.xls by using worksheet1's macro
application.calculation = calcmode

Case 1
If I open an excel worksheet1.xls, then I open worksheet2.xls within this
open worksheet1, do I open 1 excel application? If I set calcmode, will it
apply to both worksheets at this moment?

Case 2
If I open an excel worksheet1.xls, then I open another excel application and
open worksheet2.xls, do I open 2 excel application? If I set calcmode for
worksheet2, will it apply to worksheet1.xls at this moment?

Do you have any suggestions?
Thanks in advance for any suggestions
Eric