Making some tabs calculate manually & others automatically?

Hi Moe,

Try this in the ThisWorkbook module:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name Like "*Calc*" Then Exit Sub
End Sub

Originally Posted by Big.Moe

Is it possible to have some tabs in a workbook set on manual calculate mode and others on automatic? [....| I have a few large helper tables on three "Calc" tabs that involve some volatile formulas. When the workbook is set to calculate automatically, the "Calc" tabs are causing a huge slow down.

Have you considered ways to avoid or minimize the impact of the volatile functions? See my comments below.

Be that as it may, the VBA procedures below demonstrate the operations that you are looking for.

Sub disableSheet()
ActiveSheet.EnableCalculation = False
MsgBox ActiveSheet.Name & " False"
End Sub

Sub enableSheet()
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
MsgBox ActiveSheet.Name & " True"
End Sub

Sub sheetStatus()
MsgBox ActiveSheet.Name & " " & ActiveSheet.EnableCalculation
End Sub

If you experiment with those macros, you will find:

1. When you run enableSheet() -- in Excel (not VBA), press alt+f8, select enableSheet, click Run -- the active worksheet is recalculated only if the application calculation mode is Automatic. Other worksheets are also recalculated, unless their calculation mode is disabled.

2. When you run disableSheet(), the active worksheet is not recalculated.

3. Note that enableSheet() first disables, then enables the worksheet calculation mode. This is prudent if you want to ensure that the worksheet is recalculated every time enableSheet() is run. enableSheet() triggers a recalculation cycle only when EnableCalculation transitions from False to True. If EnableCalculation is already True, simply setting EnableCalculation to True in enableSheet() does not trigger a recalculaion cycle.

4. If you disable worksheet calculation, save the file, then re-open the file, EnableCalculation is True and the worksheet recalculates automatically by default.

Originally Posted by Big.Moe

Here are the three calculation sheets name: "REF CALCS", "ACCOUNTS CALCS", "OTHER CALCS".

You might implement the following design....

In the ThisWorkbook object, add the following procedure:

Private Sub Workbook_Open()
disableCalcSheets
End Sub

That ensures that EnableCalculation is False for the chosen worksheets and they are not recalculated when the workbook is opened.

In a public module (not a worksheet object), add the following procedures:

Const calcSheetNames As String = "REF CALCS,ACCOUNTS CALCS,OTHER CALCS"

Sub disableCalcSheets()
Dim nam As Variant
For Each nam In Split(calcSheetNames, ",")
    Sheets(nam).EnableCalculation = False
Next
End Sub

Sub enableCalcSheets()
Dim nam As Variant
For Each nam In Split(calcSheetNames, ",")
    Sheets(nam).EnableCalculation = False
    Sheets(nam).EnableCalculation = True
Next
End Sub

You can manually run enableCalcSheets and disableCalcSheets while the workbook is open.

-----

PS.... Sorry, I forgot to add some comments about avoiding or minimizing the impact of volatile calculations, in the first place.

A common mistake is to use whole-column ranges like A:A. That can have a major performance impact in both volatile and non-volatile formulas, especially in Excel 2007 and later. With rare exceptions, that causes Excel to process 1+ million rows.

Unless you truly might have 1+ million rows of data, it is prudent to change such references to a reasonable shorter range, e.g. $A$1:$A$10000.

Another common dubious design choice is the use of OFFSET and INDIRECT, which are volatile.

Alternatively, consider using a range expression of the form INDEX(...):INDEX(...). Often, it makes the formula longer and messier. But it might be worthwhile to improve recalculation performance, since INDEX is not volatile.

In some cases, CHOOSE might be a non-volatile alternative to INDIRECT.

Those comments are just the tip of the iceberg. Refer to http://decisionmodels.com/calcsecrets.htm (click here).

Well maybe:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name Like "*Calc*" Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If: End Sub