Excel RTD Servers: How to use UpdateNotify Properly

UpdateNotify is the primary method provided by the IRTDUpdateEvent callback interface that an RTD server uses to let Excel know that updates are available. I have however noticed that this method tends to be used incorrectly by many developers (specifically those developers without a strong foundation in COM). I’ve also received a few emails from readers reinforcing my belief that its proper usage is not obvious to many developers.

There are two very important requirements that you must keep in mind. This applies equally to developers using C# or C++ for their implementations.

The first is that the IRTDUpdateEvent interface pointer that the RTD server receives must only be called from the apartment in which it was received. Since this interface pointer is received in a call to the RTD server’s ServerStart method it follows that this interface must only be called from the apartment that the RTD server lives in. This is where a strong foundation in COM is helpful. If you’re a .NET developer you’re probably scratching your head wondering what on earth an apartment is.

I’ll leave a discussion of apartments for another day but at a minimum you need to understand that all COM objects (such as an RTD server) are grouped into apartments. A COM object lives in exactly one apartment and methods of that object can only be called directly from within that apartment. There are two types of apartments. The single-threaded apartment consists by definition of a single thread so only code on that thread can make direct calls to objects living in that apartment. The multithreaded apartment consists of one or more threads and objects living in that apartment can be called directly from any thread that belongs to that apartment. There’s so much more to say about apartments but that should be sufficient for this discussion.

A C# RTD server running in the same process as Excel will be created in a single threaded apartment. Therefore calls to UpdateNotify must only be made from this same thread that the RTD server lives on. How do you know which thread this is? It’s the thread that calls your object’s constructor and all of the IRtdServer methods. How do you ensure that calls to UpdateNotify are only made from this thread outside of one of the IRtdServer methods? One way is to send a message to a hidden window that is also living on that thread. The message pump provided by the single threaded apartment will dispatch the message to a handler on the thread and that handler can call UpdateNotify. This is why both of the minimal C# and C++ implementations use a hidden window.

For completeness I should mention (for the sake of nitpickers) that you may receive (or construct) an interface pointer that actually refers to a proxy object that allows you to call methods on the object from a different apartment. Developers with a background in COM may lean toward this approach. This is however not as useful as it sounds since it can lead you to call UpdateNotify excessively from a worker thread.

And this leads to the second thing to keep in mind. Excel will not update RTD functions more frequently than every two seconds or so. Therefore you should not call UpdateNotify more frequently than that. Some developers assume that Excel will faithfully respond to each call to UpdateNotify with a corresponding call to RefreshData. This is not the case. Remember also that since UpdateNotify is handled by Excel’s UI thread that excessive calls may lead Excel to become unresponsive since its message pump may be overwhelmed.

If you’re looking for one of my previous articles here is a complete list of them for you to browse through.

Produce the highest quality screenshots with the least amount of effort! Use Window Clippings.

12 Comments

  • Kenny,

    You're obviously highly experienced with this, so I hope you don't mind if I ask a slightly related question.

    Why can I instantiate a COM object written in C# from VB6 but not from VBA in Excel (on the same machine)

    What's the difference in CreateObject() between VB6 and VBA?

    This occurs only on production machines, not developer machines.

    Thanks

  • The point about Excel calling back to the RTD server at a frequency of ~2seconds is not strictly true. There is a refresh interval setting which persists as a registry value. The default value is 2 seconds (2000 milliseconds) but it can be set lower or higher and will obviously impact the RTD update speed when changed.

  • rich: They’re the same. There’s likely a different reason why it works in one scenario and not the other. The way .NET assemblies are registered for COM interop is a bit problematic. Usually the problem is that the client (VB6 or VBA) can’t resolve the location of the assembly. You can usually fix this by using the /codebase flag with the regasm.exe tool. Of course /codebase introduces its own problems. For example if you use /codebase you must not use compiler-generated assembly versions. If you change your versioning policy you should also change the CLSIDs for your COM-visible types. Registering .NET assemblies for COM interop is complicated. I may write about it in future but I hope this helps.

  • > One way is to send a message to a hidden window that is also living on that thread.

    Is there an api for that call? or is there a special parameter to sendmessage?

    many thanks.

  • bg: Earlier in this series I provided complete examples in both C# and C++.

  • Thanks Kenny. Interestingly when I call CorBindToCurrentRuntime() in VBA in Excel, I get the same HRESULT (0x80131700) as when I attempt to CreateObject() the DLL which works fine in VB6.

    I am doing regasm /codebase

    My single COM class contains a single method and is derived from an interface; both have GUIds defined.

    I didn't know about not using compiler-generated versions.

  • Problem solved Kenny

    Since you confirmed it's a file-not-found problem, I discovered that by creating one of those "supportedRuntime" xml files and placing it in Excel.EXE.COnfig in the same directory as Excel.Exe, the problem went away. Thanks!

  • Rich: the config file is needed in some cases to tell the loader which version of the CLR must be loaded in order to support the .NET assembly. This introduces its own problems but is required in some cases, specifically when you need to load something other than the latest version of the CLR that is installed on the computer. This is not specific to Excel but can be applied for any executable.

  • Is there a way to control RTD's Timer from another thread?

    I have a worker thread that gathers data and then tries to do Timer.Start() so that the timer's callback would invoke UpdateNotify(), but Timer.Start() seems to have no effect unless called from the same thread that created the timer.

    I'd rather not just have the timer thread always be on and check periodically. I want to set the timer's interval to a very small value and start the timer when an update arrives to propagate it immediately.

    Is there any way to accomplish this?

    Thanks.

  • Sergey: You don’t have to use a Windows timer just to call UpdateNotify. It’s just a simple way of achieving the requirement of calling UpdateNotify on the right thread periodically. I’ll try to find some time to post some additional examples using worker threads. Keep in mind that if you call UpdateNotify (indirectly) from a worker thread “when updates arrive” that you must throttle those calls so that UpdateNotify is not call too frequently.

  • I need to use RTD in a fairly high frequency/low latency environment (financial trading). I've read elsewhere as well that excel should not be forced to update more than once every second. How can duration be improved? Can I get a more powerful computer (faster CPU or more cores) to decrease the update time to...say 100 milliseconds?

  • flacon: The update throttle is not based on the speed of your computer. It is a limit imposed to ensure that Excel remains responsive since the updates occur on Excel’s main UI thread. Anyway, I’ve seen a few trading floors use Excel RTD functions for pricing. Personally I don’t think it’s practical to have values updating every 100ms since it would be hard for a human to keep up.

Comments have been disabled for this content.