How to change Excel calculation options with command buttons or macros. Tricks to see which calculation mode is active, and prevent calculation problems.
You can end up with serious problems, if an Excel workbook opens up in Manual Calculation mode, and you don't notice. That can happen if someone sends you a file that was saved in Manual mode, and it's the first workbook that you open during an Excel session.
To see how that can happen, watch this short video. There are written steps below the video.
The default setting for Excel calculation mode is Automatic. When that setting is selected:
However, when calculation mode is set to Manual:
So, if your Excel workbooks have formulas that take more than a few seconds to calculate, you can save time by setting switching to Manual calculation.
That way, you're not wasting time, waiting for one or more workbooks to recalculate, after every change that you make.
But, in most cases, it's best to switch back to Automatic calucation mode, before you save and close the workbook. That will help prevent calculation problems, if you, or someone else, opens that workbook at the start of another Excel session
The calculation mode is an application-level setting.
So, if the first workbook opened has a Manual calculation setting, any other workbooks that you open will be in Manual calculation mode too.
You don't want Excel workbooks in Manual calculation mode, if you aren't aware of it.
So, if you switch to Manual calculations sometimes, help protect yourself, and your co-workers, by switching back to Automatic before closing and saving the workbook.
The built-in Excel calculation settings are on the Excel Ribbon's Formula tab, at the far right.
To see which calculation option is active:
To help avoid calculation setting problems, you can add the Automatic and Manual commands to the Quick Access Toolbar (QAT) in Excel, or to a custom tab in the Ribbon.
Here are 2 benefits from adding the Automatic and Manual commands to the Ribbon or QAT:
In the screen shot above, the manual calculation mode is active.
To switch to Automatic calculation:
If you need to fix the Calculation settings frequently, get Andrew Engwirda's free Application Settings add-in.
With the Application Settings add-in, you can quickly see and change the Excel calculation settings
Here's another way to keep track of which calculation mode is active. Thanks to UniMord, who recommends this trick that he uses, to make it obvious when calculation mode is set to Manual:
In this screen shot, the gridlines are red, so calculation mode was switched to Manual. The numbers in column B were changed, but the total in B7 did NOT recalculate automatically
UniMord didn't send his macros, so I created the 2 calculation toggle macros below.
Copy one or both macros to a regular code module in your workbook. There are instructions on the Copy macros to workbook page. Both macros are in the sample workbook too, that you can download below.
Then, to run a macro:
Or, make the macros available in every open workbook:
This macro switches calculation mode from its current setting, and changes gridline colour on active sheet only.
Sub GridCalcToggle01() 'toggle calculation mode 'red gridlines if manual '--active sheet only 'inspired by UniMord With Application If .Calculation = xlAutomatic Then .Calculation = xlManual ActiveWindow _ .GridlineColorIndex = 3 'red Else .Calculation = xlAutomatic ActiveWindow _ .GridlineColorIndex = -4105 'automatic End If End With End Sub
This macro switches calculation mode from its current setting, and changes gridline colour on all sheets in the active workbook.
Sub GridCalcToggleALL() 'toggle calculation mode 'red gridlines if manual '--on all sheets 'inspired by UniMord Dim wbA As Workbook Dim wsA As Worksheet Dim ws As Worksheet Dim GridCI As Long Set wbA = ActiveWorkbook Set wsA = ActiveSheet With Application If .Calculation = xlAutomatic Then .Calculation = xlManual GridCI = 3 'red Else .Calculation = xlAutomatic GridCI = -4105 'automatic End If End With For Each ws In wbA.Worksheets ws.Activate ActiveWindow _ .GridlineColorIndex = GridCI Next ws wsA.Activate End Sub
For more tips and research on Excel calculation, visit Charles Williams' site - Decision Models. There are several pages of calculation secrets that can help you improve your workbook.
Charles is an reknowned Excel calculation expert, and his FastExcel products (affiliate link) can help you speed up your workbooks. Here is a screen shot of the commands on the SpeedTools tab.
To see the calculation setting macro from this page, get the Calculation Setting Macro workbook.
The zipped file is in xlsm format, and it contains the macros shown on this page.
Count Criteria in Other Column
Last updated: July 12, 2021 7:06 PM