RTD Servers in Excel Stop Refreshing when Focus Changes
lo.ma...@gmail.com
tested in Excel 2002/2003, Excel 2007 and Excel 2010.
1. When doing this, it seems that messages are queued by the RTD
server when the message frequency exceeds Excel's refresh rate. How
can I reduce this queue to 0 stored messages?
2. When using the addin in Excel 2007 and Excel 2010, the refreshes
stop once I change focus away from Excel and do not return when I
return focus. This does not happen in Excel 2002/2003
Any ideas on how to handle this situation?
Govert van Drimmelen
Both of these sound like issues particular to your implementation of
the RTD servers.
Regarding your point 2.
If I take the sample RTD server, in the Excel-DNA distribution under
Distribution\Samples\RTD\TestRTD.dna and load in Excel 2010, the
formula =WhatTimeIsIt() updates happily even when Excel loses focus.
Could you try this and compare the behaviour with that of your RTD
server?
Regarding your point 1.
Overloading Excel by setting the ThrottleInterval too small, or by
calling UpdateNotify excessively often, can certainly interfere with
Excel's recalculation and cause problems. Under normal circumstances
Excel is not supposed to call your RefreshData function for every call
to UpdateNotify though. You should be able to call UpdateNotify (on
the right thread - see below) many times a second, and Excel will call
your RefreshData only once in every ThrottleInterval. So in that sense
you should no see that the messages are queued up by Excel. If this is
not your experience, I'd certainly be interested in an example.
These issues may depend critically on how you implement the call to
UpdateNotify in your RTD server. UpdateNotify should be called from
the thread that creates your RTD server, or via a properly cross-
thread marshalled IRTDUpdateEvent interface. Calling UpdateNotify from
other threads violate the COM rules, since the RTD server is created
on an STA thread. More details can be found in this article by Kenny
Kerr: http://weblogs.asp.net/kennykerr/archive/2008/12/17/Rtd8.aspx.
In the sample RTD server in TestRTD.dna, the threading is managed by
using a System.Windows.Forms.Timer object that is created on the
thread creating the RTD server. The timer events will fire on the same
thread giving a context where the UpdateNotify can safely be called.
For reference, there is a genereal FAQ about RTD servers here:
http://msdn.microsoft.com/en-us/library/aa140060(v=office.10).aspx
If you find that this does not address your issue, I suggest you post
a minimal example that exhibits the issues you raise. I'm happy to
look closer from there.
-Govert
On Dec 12, 12:31 am, "lo.maxim...@gmail.com" <lo.maxim...@gmail.com>
wrote:
Chezky
properly cross-
thread marshalled IRTDUpdateEvent interface" that would allow a
general way of calling updateNotify from another thread?
Thanks,
Chezky
Govert van Drimmelen
It's a bit of a minefield, I suspect.
There are a few ways:
1. Use a System.Windows.Forms.Timer, which will run on the main Excel
thread.
2. Create a (hidden) control on which you can call Invoke.
3. Marshal the interface across using
CoMarshalInterThreadInterfaceInStream / CoGetInterfaceAndReleaseStream
or CoMarshalInterface / CoUnmarshalInterface.
4. Marshal the interface across using the Global Interface Table.
The last two options are discussed here: http://support.microsoft.com/kb/206076
And more on the GIT here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms693729(v=vs.85).aspx.
The easiest and safest approach is probably based on the
System.Windows.Forms.Timer, since that also makes it easy to throttle
the UpdateNotify calls to Excel. Just have the timer poll a static
variable at a rate of 500ms or so, and call UpdateNotify when needed.
Doing it with a properly marshalled interface has a certain technical
elegance, so you might like to try it, but it's unlikely to work any
better.
Cheers,
Govert