ExcelDnaUtil.Application in a Timer causes Excel to hang

[Grant Birchmeier]

I'm trying to write a simple RTD server that displays the current Excel ThrottleInterval.

As such, I have a simple Timer object that keeps updating the topic every few seconds.  Per some code I learned from another ExcelDNA post, the first step is calling "var app = ExcelDnaUtil.Application;".  When I call this in a System.Threading.Timer, Excel hangs on shutdown, even if I Dispose() the Timer.

Can anyone advise either an alternate implementation, or some way to work around this?

Here's a minimal version of my code that causes the hang.  I have traced it exactly to the ExcelDnaUtil.Application line.  If I comment out everything in the Timer block but that line, it still hangs.

        public ThrottleRTDServer()

        {

            _topics = new List<Topic>();

            _timer = new Timer(delegate

                {

                    var app = ExcelDnaUtil.Application; // this alone causes the hang

                }, null, 0, 2000); // updates every 2 seconds

        }

        protected override void ServerTerminate()

        {

            Logger.Instance.Log.Debug("ThrottleRTDServer ServerTerminate");

            _timer.Change(Timeout.Infinite, Timeout.Infinite);

            _timer.Dispose();

        }

[Govert van Drimmelen]

Hi Grant,

Your timer is likely firing the events on a threadpool thread. That means your call to ExcelDnaUtil.Application is from another thread, causing all kinds of troubles with the COM reference counting.

Easiest will be to wrap your access to the COM object model inside a delegate passed to ExcelAsyncUtil.QueueAsMacro(...) , which will ensure that the work runs on the main thread again and that the COM Object model will be available.

Note that you'll be clearing the user's undo stack every time the timer goes off, because of the macro call behind QueueAsMacro ...

An alternative might be to use a Windows Forms timer, and check that those events are fired on the main thread. The you need to also be very careful with the COM calls - even though you're running on the main thread, the COM calls might fails because Excel has suspended the COM object model (this happens if you hold the mouse down, or start editing a formula).

-Govert

[Grant Birchmeier]

Is there really no better way to retrieve the ThrottleInterval that doesn't cause threading trouble?

I'm not using this RTDServer to change it, just to report it in case *something else* changes it.

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/bBytc02tQ20/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--

Grant Birchmeier

Connamara Systems, LLC

Made-To-Measure Trading Solutions.

Exactly what you need. No more. No less.

http://connamara.com

[Govert van Drimmelen]

Hi Grant,

In truth, threading and Excel is a black art.

Your function can be made to work from the other thread without keeping the Excel process alive, if you respect how COM and .NET work together.

For example, if you make sure your other-thread access to the COM Automation model is in a scope that the garbage collector will understand (i.e. a separate function) and you then force a collection on that thread, things should be cleaned up properly.

Something like this:

        // NOTE: Expected to throw exceptions from time to time..., e.g. when user is editing a formula

        int GetThrottleInterval()

        {

            dynamic app = ExcelDnaUtil.Application;

            return app.RTD.ThrottleInterval;

        }

and then change your timer routing

            _timer = new Timer(delegate

                {

                    try

                    {

                        // Do the COM call in a separate function, so GC will know the COM objects are no longer alive.

                        int throttleInterval = GetThrottleInterval();

                        string now = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fff") + " : " + throttleInterval;

                        theRtdThrottleTopic.UpdateValue(now);

                    }

                    catch (Exception ex)

                    {

                        Debug.Print(ex.ToString());

                    }

                    finally

                    {

                        GC.Collect(); // Force clean-up of COM objects that are out of scope.

                    }

                }, null, 0, 2000); // updates every 2 seconds

Is this a 'better way'? I'm not sure.

Is it not good enough just to have a ribbon button that displays a dialog to control the ThrottleInterval (and allows your user to reset it)?

Regards,

Govert

[Grant Birchmeier]

I haven't done any work with Ribbons or Excel UIs.  My plugin is strictly focused on cell macros at present.

For this particular use case, my client is using a few different Add-ins.  He thinks another one may be altering/resetting the throttle without his knowledge.  (This is a trading application, so having a low throttle is very important to him.)

Thanks for the code.  I will give it a shot.