Detecting the Excel Function Wizard

I’m currently working on some C++ Excel addins for an investment bank. I originally wrote the addins for them back in 2001 and they’ve evolved slowly ever since. Right now we’re adding some new functionality and, whilst testing some of the new functions in the test sheets, I noticed that the functions weren’t behaving themselves properly when the Excel Function Wizard was in use…

The Function Wizard allows you to step through the creation of a call to an Excel worksheet function. Whilst in the wizard you get help on the parameters to the function and can select cells and ranges to pass as each one. Unfortunately the Function Wizard calls the addin function every time you add a new parameter; this is, presumably, to allow it to provide more feedback to the user. This functionality is less than ideal when your addin makes calls to remote servers and there’s a very old MSDN page here (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/SFEF9.asp) that tells you how to spot when the Function Wizard is active and suggests that you short circuit your addin code if you see that it’s active.

The standard detection code is a bit of a hack really, but it works. You need to search for a window with the expected window class (a string derived from “bosa_sdm_XL”) and then check that your instance of Excel is the parent of the window and you’re almost there…

Back in the mists of time we had a bit of a problem with the standard detection code as it also detected the “Edit->Replace” dialog which uses the same window class and we had users who wanted the addin functions to be correctly evaluated during an “Edit->Replace”. The fix to this was, thankfully, quite simple, once we found a “bosa_sdm_XL” window we could check to see if it was the Function Wizard since other instances, most notably the “Edit->Replace” dialog had a window caption and the Function Window did not!

Unfortunately that no longer appears to be true. In Excel 2003 the Function Wizard has the caption “Function Arguments” when you’re filling in the function arguments… Having just checked on one of my other dev boxes it seems that so does Excel 2002…

Right now I’ve put a horrible cludgy fix in to what is already fairly horrible code, we check for the expected window caption. I don’t like this fix as it currently only works with the English language version of Excel and would need to be extended to work with all the different language versions of Excel that the client uses, and it would still be cludgy and horrible!

Has anyone got any tips for how to deal with this?

Amusingly just after I hacked in the English only fix I noticed this posting…