Excel locks up when writing large numbers of UDFs

[danny...@gmail.com]

Hello Govert.

I have been using Excel-DNA for a few months now and it is a really great API. Thank you very much for writing it!

Recently, I have started to notice a particular problem that is telling me that I might be doing things the wrong way and I would greatly appreciate your advice.

My Excel-DNA addin has a UDF which will then write a number of other UDFs to various cells based on the input parameters to the first UDF. These secondary UDFs are being written from within a ExcelAsyncUtil.QueueAsMacro() delegate that is called from a worker thread. For small worksheets, this works beautifully but, when I increase the number of UDFs on the sheet to several thousand on a slow machine, Excel sometimes locks up.

My code spends at most 250ms in each QueueAsMacro() call before exiting and waits for at least a further 200ms before invoking another call to do the next "batch" of writes. On a relatively slow machine, after a period of about six seconds of processing twelve distinct QueueAsMacro() calls to write a total of about 580 formulae, Excel hangs at writing the 20th formula in the following iteration.

The full code path to the "deadlock" is somewhat complex (aren't they all!), but I shall attempt to give what I think are the key points.
  
My macro-delegate MyQueueAsMacroFunction() uses this code:

    XlCall.Excel(XlCall.xlcFormula, newFormula, excelAddress);

where newFormula is the string:

    =MySlaveUDF("Hello","World","ABC","123")

The implementation of MySlaveUDF creates an embedded RTD server like this:

   result = XlCall.RTD("my.prog.id", null, p1, p2, p3);

and that is the call that appears to lock up occasionally.

The stack trace that I see when this happens looks like this:

[Managed to Native Transition]  
    ExcelDna.Loader!ExcelDna.Loader.XlCallImpl.TryExcelImpl12(int xlFunction, out object result = null, object[] parameters) + 0xa0 bytes  
    ExcelDna.Loader!ExcelDna.Loader.XlCallImpl.TryExcelImpl(int xlFunction, out object result, object[] parameters) + 0x57 bytes  
    ExcelDna.Integration!ExcelDna.Integration.ExcelIntegration.TryExcelImpl(int xlFunction, out object result, object[] parameters) + 0x1c bytes
    ExcelDna.Integration!ExcelDna.Integration.XlCall.TryExcel(int xlFunction, out object result, object[] parameters) + 0x1d bytes  
    ExcelDna.Integration!ExcelDna.Integration.Rtd.RtdRegistration.TryCallRTD(out object result = null, string progId, string server, string[] topics) + 0x4c bytes
    ExcelDna.Integration!ExcelDna.Integration.Rtd.RtdRegistration.TryRTD(out object result, string progId, string server, string[] topics) + 0x63 bytes  
 >  MySlaveUDF(string p1, string p2, string p2, string p4) Line 929 + 0x5c bytes C#
    [Lightweight Function]  
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    ExcelDna.Loader!ExcelDna.Loader.XlCallImpl.TryExcelImpl12(int xlFunction, out object result = null, object[] parameters) + 0xa0 bytes  
    ExcelDna.Loader!ExcelDna.Loader.XlCallImpl.TryExcelImpl(int xlFunction, out object result, object[] parameters) + 0x57 bytes  
    ExcelDna.Integration!ExcelDna.Integration.ExcelIntegration.TryExcelImpl(int xlFunction, out object result, object[] parameters) + 0x1c bytes
    ExcelDna.Integration!ExcelDna.Integration.XlCall.TryExcel(int xlFunction, out object result, object[] parameters) + 0x1d bytes  
    ExcelDna.Integration!ExcelDna.Integration.XlCall.Excel(int xlFunction, object[] parameters) + 0x16 bytes
    MyQueueAsMacroFunction3(MyClass1 p1, MyClass2 p2) Line 671  C#
    MyQueueAsMacroFunction2() Line 548 + 0xf bytes  C#
    MyQueueAsMacroFunction() Line 445 C#
    ExcelDna.Integration!ExcelDna.Integration.ExcelAsyncUtil.QueueAsMacro.AnonymousMethod__0(object param0) + 0xb bytes  
    ExcelDna.Integration!ExcelDna.Integration.RunMacroSynchronization.SyncMacro(double _unused_) + 0xf8 bytes  
    ExcelDna.Integration!ExcelDna.Integration.ExcelIntegration.SyncMacro(double dValue = 0.0) + 0x1c bytes  
    [Native to Managed Transition]  
    ExcelDna.Loader!ExcelDna.Loader.IntegrationHelpers.SyncMacro(double dValue) + 0x56 bytes  
    ExcelDna.Loader!ExcelDna.Loader.XlAddIn.SyncMacro(double dValue = 0.0) + 0x19 bytes
    [Native to Managed Transition]  
    [Managed to Native Transition]  
    ExcelDna.Integration!ExcelDna.Integration.RunMacroSynchronization.COMRunMacro(string macroName) + 0x99 bytes  
    ExcelDna.Integration!ExcelDna.Integration.RunMacroSynchronization.ProcessRunSyncMacroMessage() + 0x21 bytes
    ExcelDna.Integration!ExcelDna.Integration.SynchronizationWindow.WndProc(ref System.Windows.Forms.Message m) + 0x36 bytes  
    System.Windows.Forms.dll!System.Windows.Forms.NativeWindow.Callback(System.IntPtr hWnd, int msg = 1026, System.IntPtr wparam, System.IntPtr lparam) + 0x80 bytes  
    [Appdomain Transition]
 

Have you seen anything like this before? Are there circumstances under which I should not be writing UDFs from within an ExcelAsyncUtil.QueueAsMacro() delegate?

Yours,

Dan

[Govert van Drimmelen]

Hi Danny,

I don't know of a specific reason why your call sequence would cause Excel to hang.

When you say that Excel 'hangs', do you mean that Excel stops responding completely, even if you wait a long time?

Is the CPU of the Excel process running high (e.g. pegged at 25%) when it is hanging, or is there no activity from the process in the Task Manager?

I can make some general comments and suggest a path to understanding things a bit better.

Firstly, writing to the sheet from inside a UDF is not allowed by Excel. By using the QueueAsMacro approach you get around this limitation, but you're still introducing a sequence of events that Excel might not be expecting.

If at all possible, I'd strongly suggest you move the whole formula write routine into a macro that is triggered by a user action like a context menu or ribbon callback.

In your case, perhaps Excel triggers a recalculation of the original formula for some reason while you are busy doing the writes - I'm not sure.

At least as a test, I suggest you try to start whole write routine from a macro, without the UDF as the start and without the QueueAsMacro(...). That would at least eliminate one potential source of trouble.

Secondly, you are interacting with an RTD server inside your MySlaveUDF. I'd suggest you test everything without the RTD part - maybe just return a dummy value from MySlaveUDF. It would help us to know whether the RTD part is an essential ingredient in the problem (I would expect it to be).

How is the RTD server implemented? 

Is it your own RTD server or from a third-party? 

If it's your own, is it using the thread-safe ExcelRtdServer base class in Excel-DNA?

The deadlock seems to be in a COM call from Excel to the RTD server. That's a point where the threading is quite important - everything on the Excel side is happening on the main thread, which might at this point be running the message loop waiting for a COM call to complete. If the RTD COM server is trying to notify Excel of an update at the same time, maybe there is a problem.

However, you message suggests that the failure is always exactly at the same place. If so, it might help as a test to add some short delays in your MySlaveUDF function, to see whether the change in timing would change the deadlock behaviour.

As a next step, you might replace the RTD server with a simple one - maybe the example in Distribution\Samples\RTD\ExcelRtdServer.dna in the Excel-DNA distribution. That might distinguish between an issue with the RTD server and something internal to Excel.

Finally, you might take a different approach when writing the formulae. For example, you could set calculation to manual during the write. That should prevent the recalculations triggering the RTD calls from being interleaved with your code, and should be faster.

A small but complete example that exhibits the problem could also be a great help to debugging the problem.

Regards,

Govert

...

[danny...@gmail.com]

Dear Govert,

Thank you for your detailed response. It's given me a few ideas to try out.

Excel is freezing with 0% CPU, so it's not an infinite loop or anything like that. It is, as you suggest, probably waiting to receive some sort of COM response or other acknowledgement message while it is also stuck inside the RTD call. This system is sufficiently complex that (a) I cannot think of an easy way to describe it and (b) I am sure that I am doing something that the Excel developers did not anticipate!

I shall let you know if I find the solution or if anything else crops up that might indicate what it is that I am doing that is a "bad" thing to do.

Yours,

Dan