Extra Events for Excel
As a developer of Automation solutions for Microsoft Office one might feel the need of some extra events. Quoting from the MSDN VBA Object Model reference, here is the list of events from the Excel.Application object:
- AfterCalculate Event
- CalculationDone Event
- NewWorkbook Event
- SheetActivate Event
- SheetBeforeDoubleClick Event
- SheetBeforeRightClick Event
- SheetCalculate Event
- SheetChange Event
- SheetDeactivate Event
- SheetFollowHyperlink Event
- SheetPivotTableUpdate Event
- SheetSelectionChange Event
- WindowActivate Event
- WindowDeactivate Event
- WindowResize Event
- WorkbookActivate Event
- WorkbookAddinInstall Event
- WorkbookAddinUninstall Event
- WorkbookAfterXmlExport Event
- WorkbookAfterXmlImport Event
- WorkbookBeforeClose Event
- WorkbookBeforePrint Event
- WorkbookBeforeSave Event
- WorkbookBeforeXmlExport Event
- WorkbookBeforeXmlImport Event
- WorkbookDeactivate Event
- WorkbookNewSheet Event
- WorkbookOpen Event
- WorkbookPivotTableCloseConnection Event
- WorkbookPivotTableOpenConnection Event
- WorkbookRowsetComplete Event
- WorkbookSync Event
What if these events are not enough?
The best solution would obviously be if Microsoft extended the Object Model so as to include the needed events. This is pretty hard due to the large number of the possible application events (think for instance about the event “Sheet After Follow Hyperlink Success” – imaginary event triggered when a hyperlink has been clicked and the target web site can be reached)
My solution is to use Windows hooks [link to SetWindowsHookEx]. The idea is that many actions are triggered by Windows messages sent to or by windows in Office applications. I couldn’t find any public reference of the way Office applications react to windows messages but we can see this using the tool Spy++ that gets installed together with Visual Studio. Side note: the following blog post from the designer of Spy++ offers some insights about how the tool was built.
The most obvious message that can be tapped is the WM_ENABLE message sent received when a window gets enabled or disabled when a modal dialog is shown. This is not particular to Office applications, other applications have the same behavior (e.g. Notepad).
My first attempt was to intercept this message in VBA by basically adding a hook that calls a VBA function when a message is received. The problems with this approach are:
1) A hook of type WH_CALLWNDPROC is added for all messages reaching the Window’s main procedure, no only for WM_ENABLE. We’ll then have to check in the function what message was received and only care about WM_ENABLE
2) Invoking the VBA function is really slow. The approach actually works but, because the number of messages received by the window is enormous, Excel will basically hang because of the amount of VBA code it will need to execute.
As such, I decided to include this in a COM add-in, which obviously works a lot faster. Event with the Visual Studio Wizard, building a COM add-in directly from the IDTExtensibility2 interface is not really a piece of cake. Luckily there are some cool samples that helped me easily implement the Ribbon custom UI and the button click event handler:
Building a C++ Add-in for Outlook 2010
Using RibbonX with C++ and ATL
Having these samples as start point, I can go ahead and add a Window hook when the button gets pressed.
myHook = SetWindowsHookEx(WH_CALLWNDPROC,GetWinProc , (HINSTANCE)NULL, GetCurrentThreadId());
In the callback, I can simply check is a WM_ENABLE message was sent to the main Excel Application window:
LRESULT CALLBACK GetWinProc(
__in int code,
__in WPARAM wParam,
__in LPARAM lParam
){
if (code < 0) // do not process message
return CallNextHookEx(myHook, code,
wParam, lParam);
CWPSTRUCT *cwp = (CWPSTRUCT*)lParam;
if(cwp->message == WM_ENABLE){
if((long)(XLApp->Hwnd) == (long)(cwp->hwnd)){
if(cwp->wParam)
MessageBoxW(NULL,
L"Window enabled",
L"Message from ExcelHook",
MB_OK | MB_ICONINFORMATION);
else
MessageBoxW(NULL,
L"Window disabled",
L"Message from ExcelHook",
MB_OK | MB_ICONINFORMATION);
}
}
return CallNextHookEx(myHook, code,
wParam, lParam);
}
A sample usage scenario: closing the Print dialog. When I press CTRL-P the print dialog is opened but before that, I see a message from my add-in, “Window disabled”.
After closing the Print dialog, I see a message from the add-in: “Window enabled”.
Cool huh? :)
As future development, we have many possibilities as there are many other messages that can be intercepted . MSDN has the list of other windows messages we can intercept using the hooks: https://msdn.microsoft.com/en-us/library/ff468922(VS.85).aspx