Manual Calculation

[Neil Jimack]

I have a large spreadsheet (30+ MB) which has lookups, if
statements, links etc. So whenever I have it open, I set
calculation to manual, from the Tools>Options menu.

However, this affects any other spreadsheets I have open
at the time. Is there any way to ensure that this one
spreadsheet always has calculation set to manual without
affecting any other files I have open?

Thanks in advance

 

[Niek Otten]

AFAIK, No.

The calculation mode is taken from the first workbook opened and any mode
applies to all open workbooks.
Of course you could introduce a macro which runs on opening this specific
workbook, but again, any calculation mode would apply to all open workbooks.
What I'm not sure about (some of my far more knowledgeable colleague-MVPs
may know) is what you could do with a second instance of Excel.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Bob Phillips]

Neil, Niek,

You could have that workbook's open event to set calculation mode to manual
as Niek suggests, and set it back to Auto on close. You could even have the
workbook deactivate to trigger it, but it would then be effected by any
recalculation.

Difficulty with a second instance is that the code to create this instance
and open the mega-workbook would not be in that workbook, so it would
probably be best as a toolbar macro

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

You can set calculation status for each of the sheets in that file alone
programmatically using VBA. You could determine which sheets you want to turn
off, and then use a button with a macro that toggles calculation status for each
of the predetermined sheets, eg:-

Sub ToggleCalc()

Dim m As String
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet

Set sht1 = Sheets("Sheet abc")
Set sht2 = Sheets("Sheet def")
Set sht3 = Sheets("Sheet ghi")

sht2.EnableCalculation = Not sht1.EnableCalculation
sht3.EnableCalculation = Not sht1.EnableCalculation
sht1.EnableCalculation = Not sht1.EnableCalculation

If sht1.EnableCalculation = True Then
m = "ON"
Else: m = "OFF"
End If

MsgBox "You have turned Calculation Status <" & m & "> for the following
sheets:-" _
& vbCrLf & vbCrLf & sht1.Name & vbCrLf & sht2.Name & vbCrLf & sht3.Name

End Sub

This will also give you a message telling what sheets you have just changed and
what their status is.

The sht2.EnableCalculation / sht3 / sht1 with sht1 at the end, does it in that
order to ensure that they all synchronised.

This will then not affect any other files that are open.