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.

 

[broro183]

hi Eric,

To help learn what the necessary code looks like (in many situations)
you can record a macro of manually completing your actions & then review
the recorded code. Give it a try & see what code you get...

As a bonus, here are a couple of wee macros that I use to help speed
many of my macros up.


VBA Code:
--------------------


Option Explicit
Public glb_origCalculationMode As Long
Sub RefreshApp()
With Application
.EnableEvents = True
On Error Resume Next
.Calculation = xlCalculationAutomatic
On Error GoTo 0
.ScreenUpdating = True
.StatusBar = False
End With
End Sub
Sub ToggleRefreshApp(RefreshAppSettings As Boolean)
'this can be called the start of your macro ("call ToggleRefreshApp(false)") & again at the end of your macro ("call ToggleRefreshApp(true)")
With Application
If RefreshAppSettings Then
glb_origCalculationMode = .Calculation
End If
.EnableEvents = RefreshAppSettings
On Error Resume Next
' .Calculation = IIf(RefreshAppSettings, glb_origCalculationMode, xlCalculationManual)
.Calculation = IIf(RefreshAppSettings, xlCalculationAutomatic, xlCalculationManual)
On Error GoTo 0
.ScreenUpdating = RefreshAppSettings
.StatusBar = False 'this should really be stored as a glb variable & restored, but impact in this file is minimal
End With
End Sub

--------------------




hth
Rob

 

[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

 

[broro183]

hi Eric


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


The "application" can be considered a container which holds a group o
open workbooks within it. You can open multiple "instances" of Excel b
clicking on the Excel icon in the Start Menu (or desktop etc) & eac
instance becomes a separate independent container (ie anothe
"application area") for another group of workbooks.To see whic
workbooks are in the same instance (including hidden workbooks
addins), in Excel press [alt + F11] to open the VBE, press [ctrl + r] t
show the list of projects as these correspond to a project pe
workbook.

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

If I understand correctly... Yes, it is one instance of the applicatio
& yes, calcmode will apply to both workbooks (ie "worksheet1.xls"
"worksheet2.xls").

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

If I understand correctly... Yes, you will have two instances of th
application & no, calcmode will only apply to workbooks in the sam
instance as "worksheet2.xls".

There are much better explanations of "application.calculation" so I'l
provide links rather than repeating the words of others. Charle
Williams has a lot of useful info on his site & I recommend you read al
the frames (listed across the top) on the following link: 'How the Exce
Smart Recalculation Engine works - Decision Models
(http://www.decisionmodels.com/calcsecrets.htm). Charles' site is als
mentioned by DonkeyOte in '[Solved] How to always open spreadsheets wit
auto-calc disabled - Excel Help Forum' (http://tinyurl.com/y88yrcu)

Hopefully the links will clarify this enough for you, but if not, as
more questions & we'll see what we can come up with...

hth
Rob