ExcelDnaUtil.IsInFunctionWizard() does not work anymore

Jun 2, 2013 at 8:33 AM
After the recent set of changes to ExcelDna the function ExcelDnaUtil.IsInFunctionWizard() stopped working. Now it always returns <true>. I am using Excel 2010, 64-bit version. Here is a little hint: the error seems to be in the function IsFunctionWizardWindow(IntPtr hWnd, StringBuilder buffer). The second if statement checks for string "Replace". In my Excel the window text that causes incorrect behavior is "Collect and Paste 2.0". It is strange that I did not open any such dialog window, but just hit F2, Enter in the Excel worksheet.

I guess, everyone should be switching to using !XlCall.Excel(XlCall.xlfGetTool, 4, "Standard", 1) instead of ExcelDnaUtil.IsInFunctionWizard() anyway, but just wanted to let you know that the old function got broken.
Coordinator
Jun 2, 2013 at 11:56 AM
Hi,

Thanks for reporting this.

Could you confirm which version of Excel-DNA you are using?
Is the "Collect and Paste" window some kind of Office add-in you have?

-Govert
Jun 2, 2013 at 3:00 PM
I am using the latest Excel-DNA code posted (revision 79078). I have 2 add-ins: one is my own, based on Excel-DNA, and the other is Bloomberg. I have a couple of standard Excel add-ins turned on as well (Analysis ToolPack and Solver Add-in). That is all. And of course there is no visible "Collect and Paste" window anywhere. Could that be some toolbar?

BTW, I tried using !XlCall.Excel(XlCall.xlfGetTool, 4, "Standard", 1) naively and it fails with an error code "Invalid Function". I must be doing something wrong. What is the right way to call that function?
Coordinator
Jun 2, 2013 at 3:18 PM
Hi,

I can't really imagine that xlfGetTool is a better way - who knows how they deal with compatibility now that the standard toolbars are gone.

I'll add a check for the "Collect and Paste" case in the next check-in.

-Govert
Jun 2, 2013 at 6:10 PM
OK, here is a bit more intelligence on this subject. I ran the Excel application with the add-in in Debug mode and I was printing out all combinations of {<Class Name> ; <Window Text> ] that the method static bool ExcelDnaUtil.IsFunctionWizardWindow(IntPtr hWnd, StringBuilder buffer) comes across in the process of searching for the function wizard window. Then I compared the outputs for the two cases: when I press F2-Enter on a cell and when I actually open the function wizard. Here are my observations:
1) the debug output for function wizard case had exactly one extra line and all other lines were matching. Surprise, surprise, function wizard appeared as {<OOCWindow> ; <OOCWindow>}. Your current implementation of the method was looking for {<bosa_sdm_XL>+ ... ; does not contain <Replace>}. So, clearly, the search is bound to fail no matter what.
2) Line {<bosa_sdm_XL9> ; <Collect and Paste 2.0>} is present in both cases
3) In total the list has 133 items, even though I had only a single spreadsheet and a couple of add-ins open. Items such as {<NUIScrollbar> ; <Horizontal>}, {<XLCTL> ; <>}, etc. are present multiple times. So, calls to IsInFunctionWizard() are very inefficient, but we knew this fact ahead of time.
4) When I was opening the function wizard, my UDF was called twice - both before and after the function wizard window appears on the screen. later, when I typed a character in any of the fields in the function wizard I observed the same behavior - the UDF was invoked twice. The debbug outputs on the first and the second calls were identical, with line item {<OOCWindow> ; <OOCWindow>} present in all cases. This behavior (2 consecutive) smells like an Excel bug to me.
5) If I just launch the Excel add-in in debug mode and open my spreadsheet (which is on manual calculation, so absolutely nothing gets recalculated), the method ExcelDnaUtil.IsFunctionWizardWindow(IntPtr hWnd, StringBuilder buffer) gets called 2074 times. It seems as if something somewhere checked IsInFunctionWizard about 16 times as Excel was opening up. As I expected, none of my UDFs were even invoked in the process (I had breakpoints set on them). If internal ExcelDna logic relies on IsInFunctionWizard, then the issue may be a bit more serious. As I said, in my system IsInFunctionWizard always returns true, and if you implement the change as you suggested in your previous post, it will always return false.
Coordinator
Jun 2, 2013 at 6:23 PM
H,

This is very interesting.
  • Might your OOCWIndow be the Bloomberg add-in that replaces the function wizard (or subclasses the window)? It's not something I've seen before.
  • I expect IsInFunctionWIzard to be inefficient - it should not be used for fast functions where the overhead is significant.
  • Nothing inside Excel-DNA calls IsInFunctionWizard. Maybe your breakpoints are not hit for another reason.
  • We expect the function wizard to evaluate your function multiple times - certainly when any of the parameters change. Whther the (Bloomberg?) window is doing so even more I can't tell.
You might like to disable the Bloomberg add-ins and the try again.

Thanks for the update,
Govert
Jun 2, 2013 at 6:27 PM
The whole idea of enumerating windows to determine whether the UDF was called from Excel Function Wizard is retarded. I understand that it is entirely due to Microsoft poor design, and by no means the preferred choice of the developer community. There are multiple versions of Excel floating around: I am still using primarily Excel 2003, I will have to migrate to the dreaded 2007 in a couple of months because Microsoft drops the extended maintenance for 2003, I tried 2010 and found issues with the way it constructs calculation dependency trees, so my spreadsheets would not work on 2010, even though they are working fine on 2003 and 2007, and Microsoft already pushed out 2013, which I am afraid to open up. Additionally, there are 32-bit and 64-bit editions, service packs, etc., etc. Keeping track of how Microsoft renamed windows so one can properly enumerate them to detect function wizard is a royal pain (if at all possible). That is why I thought the idea to have something like !XlCall.Excel(XlCall.xlfGetTool, 4, "Standard", 1) was good, and the instances when it does not work on certain versions of Excel (e.g., 2013 ?) should be reported to Microsoft as bugs. Well, somehow I cannot get it to work even on my 64-bit 2010, and the turnaround at Microsoft will be about 5 years, when they finally implement the feature in their Excel 2018 and have you pay to switch. Well, in the meantime, what do we do? Switch to Linux?
Coordinator
Jun 2, 2013 at 6:33 PM
I understand your frustration. I try to make a tool that works across all Excel and Windows versions. It's certainly not easy.

What happens when you try it without the Bloomberg add-in?

-Govert
Coordinator
Jun 2, 2013 at 6:38 PM
Edited Jun 2, 2013 at 6:42 PM
Do you have a reference for the Excel 2010 issue you mention?
If you are referring to this issue related to tables: http://fastexcel.wordpress.com/tag/excel-2010-calculation-bug/
then I can confirm that the problem is still there in Excel 2013.

-Govert
Coordinator
Jun 2, 2013 at 6:55 PM
To use xlfGetTool you probably have to mark your function as IsMacroType=true.

-Govert
Jun 2, 2013 at 8:01 PM
So, I disabled all the add-ins (including COM), with the only exception being the Bloomberg RTD, which seems to require a more invasive procedure to switch off. For now the number of iterations in window enumeration dropped by about 30%. Function wizard still shows up as {<OOCWindow> ; <OOCWindow>} in the debug output. I'll try to switch Bloomberg off completely, if I can do that without uninstalling the terminal.

The issue I encountered was also data table related. In my case the calculation seems to be performed properly in the absence of the data table, even though the formulas and the dependencies are quite complicated. When I add a data table to the worksheet, Excel 2010 freezes. I was able to reproduce the problem reliably, but in my case the issue seems to be in the very complicated dependency tree structure. As soon as I simplify my formulas by putting intermediate calculations in helper cells, the spreadsheet starts working. My understanding of the data table was that Excel merely changes values in a couple of cells in the worksheet and recalculates in a loop. So, I thought, if a worksheet can be recalculated once without errors, then there should be no issue with recalculating it in a loop. Obviously, life is not that simple. I tried many things on the spreadsheet - disabling multi-threading, replacing my functions with dummies that return all zeroes and removing certain parts of the calculation altogether - and I can still reproduce the problem. Since there is no issue with this spreadsheet both in Excel 2003 and in 2007, I think my spreadsheet is not corrupt. Even though I can make tweaks so that the calculation goes through, my assessment was that Excel 2010 is unreliable and therefore unfit for any serious use where correctness of the calculation is important. The link you shared confirms my conclusion. Thank you. It is shocking that having produced nothing but regress in its Office suite over the 10 year period from 2003 to 2013, Microsoft is still enjoying very healthy market share and profitability.

To counter-balance the gloom that I brought to this chat, let me tell you the good news: if I use IsMacroType=true attribute, the magic formula !XlCall.Excel(XlCall.xlfGetTool, 4, "Standard", 1) works as intended. May be I will stick to that approach for now. Many thanks indeed!
Coordinator
Jun 2, 2013 at 8:12 PM
Hi,

I'm not sure what the source of your OOCWindow is, but I'm pretty sure it's not part of the normal Excel. "OpenOffice Calc"? No - that makes no sense ;-)

You need to take some care with IsMacroType=true, it is not without some side-effects. For example, some functions become volatile by default.

-Govert
Jun 8, 2013 at 5:27 AM
A friend of mine had the same issue with ExcelDnaUtil.IsInFunctionWizard() function when using 32-bit version of Excel-2010 (on a different computer). He also had Bloomberg add-in installed. I ended up using the following code to replace calls to ExcelDnaUtil.IsInFunctionWizard(). Everything seems to work fine so far. I plan to do more testing next week and will let you know if I experience any issues with functions becoming volatile.
        internal static bool IsInFunctionWizard()
        {
            try { return !(bool)XlCall.Excel(XlCall.xlfGetTool, 4, "Standard", 1); }
            catch { return ExcelDnaUtil.IsInFunctionWizard(); }
        }
P.S. "OOCWindow" may stand for "Out Of Context". If it is indeed true that Bloomberg add-in hijacked the function wizard window in Excel-2010 (and hence this strange behavior), then may be Excel-DNA can do the same and solve "IsInFunctionWizard" problem once and for all? If you decide to take this route, please call it "XlDnaFnWizard", or something similar, self-explanatory, not as cryptic as "OOCWindow". :-)
Jun 14, 2013 at 11:42 AM
After encountering this same issue myself I debugged the ExcelDna library and using Spy++ I think the fault lies with the ExcelDnaUtil.SafeIsExcelVersionPre15 check.

Basically Excel 2010 (version 14) will open up the Function Wizard as a top level window (similar to Excel2013). The version check is however excluding Excel versions less than 15. I didn't want to change this property as it is being used elsewhere, so this is what I did instead:
public static bool IsInFunctionWizard()
{
    return IsInFunctionWizard15() || IsInFunctionWizardPre15(); 
}
Here is what GetVersionInfo() returns for my install of Excel 2010 (64bit) running on Win7 x64:

CompanyName: Microsoft Corporation
FileBuildPart: 6126
FileDescription: Microsoft Excel
FileMajorPart: 14
FileMinorPart: 0
FileName: C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
FilePrivatePart: 5003
FileVersion: 14.0.6126.5003

Hope this helps
Coordinator
Jun 14, 2013 at 11:50 AM
That's very helpful - thank you for investigating!

I presume I broke it in my attempt to support Excel 2013, where it was very difficult getting hold of the right Application object, so I made a lot of changes which affected this too. It's a very painful part of the Excel interaction - I'll have to go back to check the older versions again...

Thanks,
Govert