Extra Events for Excel

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:

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: