Re: Question, re RTD, addins and excel DNA

[Govert van Drimmelen]

Hi,

Indeed - RTD is exactly the right way to do this.

The Excel RTD FAQ here: http://msdn.microsoft.com/en-us/library/aa140060(v=office.10).aspx
is a great introduction, and the Kenny Kerr articles are also very
useful for this - here's one http://weblogs.asp.net/kennykerr/archive/2008/11/13/Rtd3.aspx.

You can make an RTD server without Excel-DNA, and it will work
perfectly well. Integrating your RTD server as part of your Excel-DNA
add-in has three advantages:
- You can deploy your RTD server without COM registration in the
registry, which normally needs some installation step and admin
rights.
- Your RTD server function can easily be wrapper in friendly functions
exposed to the user, instead of the raw =RTD("servername"...)
functions.
- Your RTD server will be loaded into the same AppDomain as the rest
of your add-in, so can shared static variables that might cache some
information, hold login state etc.

THe Excel-DNA distribution has some small samples under Distribution
\Samples\RTD, and you'll find a lot of posts on the group too.

Recent check-ins of Excel-DNA have added two higher-level wrappers
that make the correct implementation of an RTD server and push-based
data sources much easier. (You can get the most recent check-in here:
http://exceldna.codeplex.com/SourceControl/list/changesets).

- An ExcelRtdServer base class which provides a somewhat friendlier
interface to implementing an RTD server, and most importantly also
wraps thread-safe update notification.
- Something I'm called 'Reactive Extensions for Excel -
RxExcel).Support for using the Reactive Extensions observables as the
source of your push data. This requires that you use .NET 4, but will
hopefully become the preferred way of implementing push data for
Excel. Some first snippets that will get you started if you already
know a bit about Rx can be found here:
http://exceldna.codeplex.com/wikipage?title=Reactive%20Extensions%20for%20Excel
, and a comprehensive introduction to Rx is here: http://www.introtorx.com.

Both of these are still a bit experimental, so it has not been well
tested and the API will still change a little bit. But if you want to
give it a try then now is a very good time.

Regards,
Govert


On Jun 29, 7:53 am, Connor Strategies <connorstrateg...@gmail.com>
wrote:
> I've been using ExcelDNA for normal UD functions for a while now and it's
> been great.
>
> I do, however, have a new need that has arisen. My usual use for excelDNS
> is returning a value after calculating it -- one time. Now I need for my
> DLL to subscribe to outside data and continuously update a cell based on
> new data.
>
> I know how to get the data fine -- my issue is updating the cell.
> Obviously, using the standard practice in exceldna DLL, ie, a c# function
> that returns a value won't work, right? Because I cant return a value
> multiple times from a c# function, i assume.
>
> I've read tonight of RTD servers. Do you think I'll need to create on of
> these?
>
> My project basically revolves around stock price updates. I get my stock
> data through an API for a subscription service I use. I just would love to
> know the best way these days to update a cell in real time.
>
> I know one can use VS to make an excel addin, and I know I can update a
> cell whenever I want via this method, but I'd like to let users use the old
> function style method of using my functions, ie, *myFunction(input1, input2)
> * in the cell and it returns the value.
>
> Any ideas? Thanks a bunch.

[Govert van Drimmelen]

Hi John,

All those sample files are independent.

The RealTimeManager.dna concerns a setting Excel has, called the
ThrottleInterval. It controls how often Excel reads updates from the
RTD servers. It's a bit problematic, as it is a global setting for the
whole of Excel and all RTD servers, it is persistent between Excel
sessions, and your Excel might become unstable it you set it too
short. This example lets you explore it a bit.

I can't remember exactly (it was a contributed example) but I guess
the RTDObjectHandles.dna is about how you can use RTD to manage
behind-the-sheet objects. The point is that the lifetime of the object
can be correctly managed because of the RTD server, which is pretty
cool.

Even TestRTD.dna has two examples interwoven. The class TimeServer is
the simplest example. The class TestServer is a complicated one that
monitors an xml file for changes.

If I had to implement this now, I would do it based on the simpler,
thread-safe ExcelRtdServer base class. Or even better, on top of the
new ExcelAsyncUtil.Observe(...) support coming in v0.30, which should
be flexible enough for anything RTD-related.

Regards,
Govert


On Jul 11, 9:50 am, Connor Strategies <connorstrateg...@gmail.com>
wrote:
> Govert,
>
> Thank you, as usual you have given a great and helpful reply.
>
> Could you please tell me, in the example RTD folder in excelDNA
> distribution folder, what are the files RealTimeManager.dna and
> RTDObjectWrapper.dna?
>
> It seems to be that technically, for the example, one just needs
> TestRTD.dna for the example to work properly. Or is TestRTD.dna using these
> two files above in some capacity?
>
> Thank you!
>
> John

>
>
>
>
>
>
>
> On Friday, June 29, 2012 1:53:18 AM UTC-4, Connor Strategies wrote:
>
> > I've been using ExcelDNA for normal UD functions for a while now and it's
> > been great.
>
> > I do, however, have a new need that has arisen. My usual use for excelDNS
> > is returning a value after calculating it -- one time. Now I need for my
> > DLL to subscribe to outside data and continuously update a cell based on
> > new data.
>
> > I know how to get the data fine -- my issue is updating the cell.
> > Obviously, using the standard practice in exceldna DLL, ie, a c# function
> > that returns a value won't work, right? Because I cant return a value
> > multiple times from a c# function, i assume.
>
> > I've read tonight of RTD servers. Do you think I'll need to create on of
> > these?
>
> > My project basically revolves around stock price updates. I get my stock
> > data through an API for a subscription service I use. I just would love to
> > know the best way these days to update a cell in real time.
>
> > I know one can use VS to make an excel addin, and I know I can update a
> > cell whenever I want via this method, but I'd like to let users use the old
> > function style method of using my functions, ie, *myFunction(input1,

> > input2)* in the cell and it returns the value.

[Govert van Drimmelen]

Hi,

That's an interesting example, but you'd need some care to make it
reliable.

In particular, your COM call (in the timer callback) might fail if
Excel is 'busy', even though it is being run on the main thread (which
is due to the DispatcherTimer). In some contexts Excel continues to
pump messages, but the COM calls fail because the object model is
'suspended'. So you'd need some error handling there, and not have the
assumption that the recalc actually happens every time.

I believe RTD is the Excel-friendliest approach. But I've also seen
some claims that a tuned DDE implementation can perform quite a bit
better than RTD. But by that time you're seriously abusing Excel...

-Govert


On Jul 11, 6:54 pm, JM Pironneau <jmpironn...@gmail.com> wrote:
> If you dont want to use a RTD, there is something I did some times ago as a
> test to see if cells could be updated in real time, and it worked fine:
>
> All the Excel cells containing my UDF where incremented every 200 ms. But
> as I said, it was just a test and I did not tried it on large scale.
>
> //The UDF
> [ExcelFunction(Description = "Get Data", Category = "Test", IsVolatile =
> true)]
> public static object GetData(object p1, object p2, object p3, object p4) {
>          return _count;}
>
> //The Calculation Timer
> _recalculateTimer = new DispatcherTimer(); //Thats because I use WPF
> _recalculateTimer.Tick += RecalculateTick;
> _recalculateTimer.Interval = TimeSpan.FromMilliseconds(200);
>
> //The timer tick method
> private void RecalculateTick(object sender, EventArgs e) {
>      _count += 1;
>      Excel.ActiveWindow.VisibleRange.Calculate();
>
>
>
>
>
>
>
> }

[Naju Mancheril]

What are the errors? The wrong numbers appearing? #NA appearing? Excel crash?

I don't suppose you can post a small sample that reproduces? Doesn't need to access real data. Can just use incrementing prices or something.

[Govert van Drimmelen]

Hi John,

You write:

"I call the RTD from excel every 7 seconds."

What does this mean? The RTD would feed into Excel, and Excel will be
calling the UDFs automatically. Do you have some other mechanism
involved?

The other RTD aspect that one needs to understand and take care of is
the threading issues. You need to be sure that UpdateNotify() is being
called from the main Excel thread, which called the ServerStart
initially. If you don't do this, but use some ThreadPool thread like
you'd have if using System.Threading.Timer or something, this is known
to be problematic. It might help you to trace the ThreadID in your
ServerStart and your UpdateNotify calls, and check that this is always
the same.

I have also done some work in the latest check-ins to provide a thread-
safe RTD base class, called ExcelRtdServer, which adds some additional
safety, and allows you to update topics from any thread. This is still
experimental, but if you get the latest check-in from CodePlex you can
try it to see whether your porblems are ameliorated.

From your post it's not really clear whether the error is a COM error
in your interaction with Excel, or and exception in your UDF wrapper
code. If it is the latter, you might wrap the UDF in a try-catch and
return or log the exception information - this might give you the clue
you need.


Please let us know if you find out more.

Regards,
Govert




On Aug 3, 7:14 pm, Connor Strategies <connorstrateg...@gmail.com>
wrote:
> NGM,
>
> If need be I can try to post something, but that's just it -- its not one
> particular function or time. It'd pretty random. It reminds me of sync
> issues when working with a database or an in-memory db like DataTable. But
> I am locking my internal datatable every time I access it.
>
> The errors actually show as unhandled exceptions in excel, and they show me
> which functions get the error -- they are functions that are pretty damn
> solid (usually third party DLL), and it could be the data I am feeding the
> 3rd party function, I don't know.
>
> I just wish I could see the snapshot of the variables etc when the
> error occurred, like when you debug in Visual Studio.
>
> I made a console app that (kind of) mimics the excel app.....and I cant
> replicate the errors ever. It's solid as a rock.