QueueAsMacro multiple requests issue?

[aberglas]

Hello Govert,

Thanks for your suggestion to use QueueAsMacro in order to pass Windows Message events back to Excel VBA macros.  It worked, code below.  If Excel is not ready the queued macro is simply not executed until Excel becomes ready.

However, to prevent occasional exceptions I had to add the AreOutstanding flag which ensures that no new calls to QueueAsMacro are made until the previous queued "macro" has been processed.  For me that is fine as it is OK to miss events, I test the current state from scratch rather than looking at the events themselves.

However, it does suggest a concurrency bug somewhere, perhaps in ExcelDna.  I would suggest you have a look at the code to see if that could happen.

More importantly, I would appreciate a bit of information as to how this actually works.  I'm guessing that this is all essentially single threaded, and that the QueueAsMacro call is eventually executed by some sort of call back from Excel or a Post message.  Is that correct?  (If not, is my code wrong, does the Outstanding flag need to be locked?!)

Thanks again for your excellent support,

Anthony

        protected override int WndProc(IntPtr hWnd, int msg, int wParam, int lParam) {

            if ( msg != (int)PInvoke.WM.NCHITTEST && msg != (int)PInvoke.WM.MOUSEMOVE && msg != (int)PInvoke.WM.SETCURSOR && msg != (int)PInvoke.WM.GETTEXT ) {

                var enm = Enum.IsDefined(typeof(PInvoke.WM), (PInvoke.WM)msg) ? ((PInvoke.WM)msg).ToString() : "?";

                //Trace.WriteLine("Message " + msg.ToString("x") + " " + enm + " " + hWnd + " " + lParam + " " + wParam);

                switch ( msg ) {

                    case (int)PInvoke.WM.VSCROLL: // Many of these as we drag tool bar.

                    case (int)PInvoke.WM.HSCROLL:

                    case (int)PInvoke.WM.MOUSEWHEEL: // One for each click

                    case (int)PInvoke.WM.WINDOWPOSCHANGED: // Calling NewDialog seems to trigger redundant WINDOWPOSCHANGED.

                        var now = DateTime.Now.ToString("mm:ss.fff");

                        var vis = AddInComRoot.SDetectiveComDnaInstance.DialogIsVisible();

                        Trace.WriteLine("\n===VScroll Event " + AreOutstanding +" " + vis + " " + msg.ToString("x") + " " + enm + " " + hWnd + " " + Title + " " + lParam + " " + wParam + " " + now);

                        if ( vis && !AreOutstanding ) {

                            AreOutstanding = true;

                            ExcelAsyncUtil.QueueAsMacro(delegate {

                                Trace.WriteLine("*** Calling ScrollMoved " + msg.ToString("x") + " " + enm + " " + hWnd + " " + Title + " " + lParam + " " + wParam + " " + now);

                                try {

                                    var ap = (Excel.Application)ExcelDnaUtil.Application;

                                    ap.Run("SDetective_TheAddIn_XL2007.xlam!fsVstoCallback", "ScrollMoved", "", "");

                                } catch ( Exception ex ) {

                                    Trace.WriteLine("\n???????? EXCEPTION " + ex); // dieing on a scroll event is actually not critical.

                                }

                                AreOutstanding = false;

                            });

                        }

                        break;

                }

            }

            return base.WndProc(hWnd, msg, wParam, lParam);

        }

[aberglas]

Hello Govert,

I had a bit of a poke through the code, and between all the delegates and reflection I lost the plot.  From the Microsoft docs I could not see how this could be done, you are either implementing a UDF or are responding to a Microsoft event as far as I could tell.  It would take weeks if not months to really understand what is going on.

One thing that is a little frightening about Excel DNA is that it could be very difficult to support if (heaven forbid) you disappeared or just go sick of it.  So some architectural pointers, ideally in the code, would be very much appreciated.  A brief description of how  QueueAs"Macro" works would be helpful.  (I have already updated the codeplex documentation somewhat.  I will integrate any replies you care to give.)

Incidentally, which forum should we use, this one or the one on codeplex?  Having two splits the community somewhat.

Regards,

Anthony

[Govert van Drimmelen]

Hi Anthony,

QueueAsMacro concurrency issue:

I don't expect there to be concurrency issues with the QueueAsMacro mechanism - it really should act as a queue and so the issue (and workaround) you report might indeed point to an Excel-DNA bug.

You don't state what the actual exception is that you encounter (when running without the 'AreOutstansing' guard), or which line actually gives the error. That would be a start toward trying to figure it out.

In the most recent check-in on CodePlex (https://exceldna.codeplex.com/SourceControl/list/changesets) I added an extra check to the QueueAsMacro runner, to only attempt to run the macro if the user is not currently editing a cell. That seems to eliminate one issue where the QueueAsMacro mechanism was intefering with the sheet selection.

Are you testing with the released version v 0.30, or with a more recent check-in? If not the most recent, perhaps you can confirm that the issue you report is still present.

Then I'd be happy to have a closer look if you can make some reproducible project that I can debug.

QueueAsMacro implementation:

Some overview of the QueueAsMacro mechanism (the important code is in ExcelSynchronizationContext.cs, with the public interface in ExcelAsyncUtil.cs):

* ExcelAsyncUtil.Initialize() registers an additional hidden macro with Excel called SyncMacro, and installs a message window called SynchronizationWindow on the main thread.

* The SynchronizationWindow will be used to get code to run on the main Excel thread. The window is shared between the QueueAsMacro story, and the thread-safe RTD update mechanism that underlies the async and RxExcel streaming data support.

* ExcelAsyncUtil.QueueAsMacro(...) enqueues the work to be done in an internal queue and, if there is no message posted to indicate outstanding work, will set a wrok-to-do flag and post a message to the SynchronizationWindow.

* When (on the main thread) the SynchronizationWindow receives the work to do message, it will attempt to call Application.Run to run the SyncMacro macro. It backs off if Excel is in edit more (with recent check-ins) and retries every 250ms if the Application.Run call fails. Eventually the Applicatino.Run will succeed and run the SyncMacro macro.

* I assume that the fact that Excel is happy to run a C macro means we are not in a context where botht th C API and the COM interfaces can safely be used. SyncMacro will run, in turn, every delegate that is in the queue, and then reset the work-to-do flag.

* There is some locking to ensure that the enqueueing of work does not interfere with the dequeueing and running. Any concurrency issue you found might relate to this locking.

Which forum to pick:

I prefer the Google group, but also support the CodePlex discussions.

Excel-DNA support:

Excel-DNA is published under a liberal open-source license, so your future use and modification of the library has no legal dependency on me. Some serious users, who use Excel-DNA in a mission critical setting, have entered into a formal support subscription that ensures continuity of the project and provides them with direct support from me. To help more casual users, I try to be responsive on the public forums like this one.

Regards,

Govert

[aberglas]

Hello Govert,

I upgraded to the latest build (79914) and could not reproduce the bug.  When it did happen it was a Com Automation error, with no stack trace, I assume calling the QueueAsMacro code.  I have discovered a very weird sheet selection bug but I do not think it has anything to do with ExcelDna.  

I worked through the code again, based on you description.  I missed that the SyncMacro would need to be in C for the callback, odd that Excel uses reflection rather than just a function pointer.  There are also issues with NativeWindow, as described in  http://blogs.msdn.com/b/anandgeorge/archive/2010/04/11/usage-of-nativewindow-assignhandle-releasehandle-when-unmanaged-code-is-involved.aspx  I avoided it in my code, but I'm guessing CreateHandle(cp); produces a new handle each time somehow -- can a windows window have more than one handle?

I have added a couple of paragraphs to your doc.  And I have made a small donation.

Thanks for your help,

Anthony

[Govert van Drimmelen]

Hi Anthony,

Thank you very much for the PayPal donation! It is much appreciated.

Are you finding the selection bug in the latest check-in? The change to this area (in particular checking whether the user is editing before trying any COM interop) was made to fix exactly such a problem. Basically a failing call to the COM interface will sometimes have side effects in the selection. QueueAsMacro should now no longer make an attempt to call the COM interface if the user is editing a formula, but maybe there are other cases which are still problematic.

Excel uses the library export name for resolution of the function in the .xll.

I'm not sure whether your native window comments relate to the Excel-DNA code. Is there something you thin kI need to review?

Regards,

Govert

[aberglas]

As previously mentioned the selection issue was not an ExcelDna bug, but I found it because of your issue.  It is an Excel bug.  If you create a comment on Sheet2!B3 (say) and then make it always visible, and then drag the comment box itself to D1 (say) and then edit a formula on Sheet1, it will not be able to use the mouse to add a cell reference in the range Sheet2!B1:D3.  All those conditions are required, go figure!

I do not think that the NativeWindow comments apply to ExcelDna because it looks like you are creating a new window that only you will use.  But I do not really know what CreateHandle(cp); does.  I had used NativeWindow to subclass the Excel Windows and that was a bad idea.

Regards,

Anthony