Automatic calculation - Excel97 SR2

[Ben]

Hi

I have a workbook that takes an eternity to calculate and therefore, have
the calculation mode set to manual. I would like the calculation mode set
to Automatic when the workbook is deactivated or closed, but do not want the
workbook to be calculated when this happens. Is that at all possible?

Thanks in advance
Ben

 

[Dave Peterson]

You could use workbook events that fire when the workbook is
activated/deactivated.

Rightclick on the excel icon (to the left of File|Edit|...) on the menubar.
Select view code and paste this in the code window.

Option Explicit
Private Sub Workbook_Activate()
Application.Calculation = xlCalculationManual
MsgBox "manual"
End Sub
Private Sub Workbook_Deactivate()
Application.Calculation = xlCalculationAutomatic
MsgBox "automatic"
End Sub

But the bad news is that this won't affect the calculation mode when you open
your workbook. Your workbook is opened before and recalculated before this type
of macro can run.

You could change the calcuation mode before you open this giant workbook or you
could open a "helper" workbook that sets the calculation mode, then opens your
giant workbook, then closes itself.

This is kind of how that macro in that helper workbook would look:

Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
workbooks.open(filename:="C:\myworkbook.xls")
thisworkbook.close savechanges:=true
End Sub

It also goes in the ThisWorkbook module of that workbook.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you're new to events, David also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm

Chip Pearson also has some notes about events at:
http://www.cpearson.com/excel/events.htm

 

[Ben]

Thanks Dave.

I have used the Workbook_Activate() to turn the calculation mode to manual.
This works quite well, as the event seems to be fired before the the
automatic calculation is performed. The problem is, some users have other
spreadsheets that need to be opened in Automatic calculation mode - When
they don't realise calculation is in manual mode, they read off out of date
data.

I don't understand why the calculation mode is at the application level and
not at the workbook, or even sheet level - The manual calculation mode is
obviously for *workbooks* that take a long time to calculate.

The only 'suitable' solution I have, is to give the user a MsgBox warning
that the calculation mode has been set to Automatic.

Ben

 

[Norman Jones]

Hi Ben,

Dave suggested using the Workbook_Activate event code to set calculation to
manual and he also suggested using the Workbook_Deactivate event code to
restore calculation to automatic.

Employing both of Dave's suggestions should obviate your problem.

---
Regards,
Norman

Thanks Dave.

I have used the Workbook_Activate() to turn the calculation mode to
manual.
This works quite well, as the event seems to be fired before the the
automatic calculation is performed. The problem is, some users have other
spreadsheets that need to be opened in Automatic calculation mode - When
they don't realise calculation is in manual mode, they read off out of
date
data.

I don't understand why the calculation mode is at the application level
and
not at the workbook, or even sheet level - The manual calculation mode is
obviously for *workbooks* that take a long time to calculate.

The only 'suitable' solution I have, is to give the user a MsgBox warning
that the calculation mode has been set to Automatic.

Ben

 

[Ben]

Hi Norman

I tried that right at the begining. Doing that, launches the automatic
calculation on the workbook that is deactivating - precisely the workbook I
don't want auto calculated..

Thanks
Ben

Hi Ben,

Dave suggested using the Workbook_Activate event code to set calculation to
manual and he also suggested using the Workbook_Deactivate event code to
restore calculation to automatic.

Employing both of Dave's suggestions should obviate your problem.

 

[Dave Peterson]

Ah, as soon as that calculation is set to automatic, your giant workbook
recalculates and that's a problem.

xl2002 added an option to turn off calculation at a worksheet level--but it
doesn't get turned back on until you turn it on. F9, shift-F9, Ctrl-Alt-F9, and
ctrl-shift-alt-f9, are all turned off.

You could run the code to toggle this setting on demand (maybe a toolbar or a
button on a worksheet), or just add it to the _activate/_deactivate events.
(Well, if the user reenters the formula, that cell will re-evaluate--but others
won't--even if they are a direct dependent.

(I put:
A1: =Rand()
B1: =A1
C1: =Now() (formatted as hh:mm:ss)

Test it with this setting disabled.

Option Explicit
Private Sub Workbook_Activate()
Dim wks As Worksheet
For Each wks In Me.Worksheets
wks.EnableCalculation = False
Next wks
Application.Calculation = xlCalculationManual
MsgBox "manual"
End Sub
Private Sub Workbook_Deactivate()
Dim wks As Worksheet
For Each wks In Me.Worksheets
wks.EnableCalculation = False
Next wks
Application.Calculation = xlCalculationAutomatic
MsgBox "automatic"
End Sub

Remember that nothing the user does will make it recalc until this setting is
enabled. And it's for xl2002 and above.

And it maybe even more scary to use than just turning calculation to manual.
There's no overt indicator that the worksheet needs to be recalculated (like a
warning in the statusbar).