How to create a custom event when Excel calculation mode changes
You should be aware that no such event exists. Let’s create it.
In Excel 2007 – 2010, you can change the calculation mode using the buttons shown in the screenshot below.
As a developer, you can intercept clicking on these buttons and cancel the default action. This requires that you know their identifiers. The identifiers can be found in 2007 Office System Document: Lists of Control IDs or in Office 2010 Help Files: Office Fluent User Interface Control Identifiers. These downloads provide a set of Excel files. You need to open ExcelRibboncontrols.xlsx or ExcelControls.xlsx. Then, in the column “Control Name”, you’ll find the following identifiers:
- CalculationOptionsAutomatically
- CalculationOptionsAutomaticallyExceptDataTables
- CalculationOptionsManuallly (note tripled “l”!)
To intercept a built-in Ribbon control, you use a Ribbon Command component; it generates the part of the Ribbon XML which specifies built-in Ribbon controls to repurpose.
You need to specify the identifier of a Ribbon control in the IdMso property of the Ribbon Command component and add an event handler to its OnAction event.
However, just calling CheckCalculationMode() in the OnAction callback of a Ribbon command component will not work: the OnAction callback of a repurposed Ribbon control is invoked BEFORE the click is processed by the control itself.
There is a feature of Add-in Express that allows running your code AFTER the click is processed. The SendMessage method lets you send a custom Windows message to an internal window that Add-in Express creates for your add-in; when Windows delivers this message, Add-in Express raises the event called OnSendMessage. All this resembles using a timer.
That is, the solution includes repurposing a Ribbon control that modifies the calculation mode, cancelling the user action and sending a message to the internal window. When the OnSendMessage event occurs, you filter out “your” message and execute the Ribbon command once again; to avoid getting into a loop, you use a flag.
So far, so good. But you can also change the calculation mode in the Options dialog accessible via menu Tools | Options in Excel 2000-2003, {Office button} | Excel Options in Excel 2007 and File | Options in Excel 2010. Accordingly, you need to check the calculation mode after the dialog is closed; you use the same SendMessage() / OnSendMessage machinery to achieve this.
The sample project mentioned below uses the following constants:
- The ID of the Ribbon control that opens the Options dialog in Excel 2007 – 2010 is “ApplicationOptionsDialog”.
- The ID of the commandbar button that opens the Options dialog in Excel 2000 – 2003 is 522; use our free Built-in Controls Scanner to get that sort of IDs.
And the only tiny moment requiring special attention is how to get the calculation mode for the very first time. According to Description of how Excel determines the current mode of calculation, Excel sets the calculation mode from the first workbook opened. That’s why the add-in intercepts the WorkbookOpen event. Still, there’s a scenario when your add-in is loaded after the first workbook is opened in Excel. To handle this, the add-in initialize the variable that holds the current calculation mode in the AddinIntialize event.
That is, the workaround for the missing event is to intercept the user’s clicking the corresponding controls. Then you cancel the clicks and use a simple trick to run your code AFTER those clicks are processed by Excel.
Good luck!
Available downloads:
This sample COM Add-in was developed using Add-in Express for Office and .net: