ExcelDNA function for changing parameters

[David Lee]

I have a real time data function which looks like =RTD(,,XXX). This function is dependent on the parameters and data I am receiving on the back end, hence the need for an RTD function.

Now the parameter set passed to the RTD function is uniquely identified by a topic number created by Excel. In my experience with RtdServers these topics are cached for the duration of the application, meaning that if XXX changes many times Excel will both generate a new topic for each unique value of XXX and save all previous values. So if XXX changes to a newly unique value many times, once every 100ms, for lets say 10,000 instances, that's 100,000 topics being generated every second. When this happens Excel's memory footprint grows unbounded and the application crashes.

Is there a way to create a function with ExcelDNA which handles changing parameters well while keeping up with a changing back end, without this "leak" being a concern?

I've read a little about it and it seems like I can make a volatile UDF function that just recalculates every time. I'm not worried about the extra calculations here, but I am worried about other cells depending on the volatile cells.

[Naju Mancheril]

Excel will often reuse topic Ids, so I don't think you need to worry about overflow.

ExcelDna has several RTD examples. Download the source code and check out the samples in \Distribution\Samples\RTD\.

[David Lee]

ngm, thanks for the response!

When you say Excel will reuse topic ids do you mean that two parameter sets could potentially have the same topics, albeit at different times? Through my own experimentation that is not the case, in your experience is that not the case?

I already have a few RTD functions implemented. The problem is when I require a parameter that changes a lot it creates many new topics and I have seen this cause load on the system, because I believe Excel keeps an internal list of these topics. As I said I've crashed Excel repeatedly by doing this.

[Govert van Drimmelen]

Hi David,

How are you implementing you RTD server? And what a Excel version are you testing with?

If you're using the Excel-DNA base class ExcelRtdServer, all should be well.

Excel 2010 had a bug related to topic not properly being disconnected. The Office 2010 service pack fixed it, and ExcelRtdServer works around the bug.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, 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.

[Naju Mancheril]

Check out what Govert said. It sounds like the bug that he describes.

If everything is functioning correctly, Excel should:

- Avoid an additional RTD connect in response to an identical RTD call (with same args).

- Reuse the same topicId when all previous uses of an RTD call (with a given set of params) have been removed from the sheet.

[David Lee]

I am indeed using Excel 2010. Was it SP2 released in 7/2013? 

I am not using the Excel DNA RtdServer, but I will check out how it works around the bug. 

Thanks for the responses!

[David Lee]

So I've reproduced the error again. Since the last time I ran this experiment my Excel has been updated. I'm currently running 14.0.7116.5000 32 bit Excel 2010. Again this is with a dumbed down rtd server and I will try to reproduce with the Excel DNA rtd server and report back.

I still have not seen Excel reuse a topic for different parameters. If I can reproduce I will post an example.

[David Lee]

The error is quite small actually now that I'm testing it again, on the order of 5MB. It's how I was handing the creating of new topics that bit me. 

[Naju Mancheril]

Have you seen examples of topicId reuse? After reading your last response, I created a test addin and ran it in the same Excel version that you used (....7116.5000). I did not see any reuse until 500, at which point I stopped testing. This was pretty disconcerting since I've definitely seen reuse on other (older?) version of Excel.

It's possible that it "garbage collects" the Ids once Excel hits some higher threshold.

I have not yet tried this in Excel 2013, but I plan to do it.

[David Lee]

I still have not seen any topic reuse at all. 

I have to amend my earlier response in that I think this is a problem with Excel. I created a dummy rtd server which does nothing but increment a counter every time ConnectData is called, then I have a macro update the argument in a tight loop. I see a growing portion of unmanaged memory in my profiler. 

Unfortunately I am blocked from uploading files at work. So add this class to an Excel-DNA project and then run excel with the xll file as the command line argument. Add say 20 SomeFunc formulas and have a macro or something else change the parameters constantly. You should see Excel's memory increase, using a profiler it will show the increase is in unmanaged memory. You can check the log file to look for duplicates but I did not see any in my version of Excel.

    [ComVisible(true)]

    [ProgId("ExcelDnaRtdTest")]

    [Guid("2760BD0B-6D0E-40D0-8A45-4381B776D837")]

    public class ExcelDnaTestRtdServer : IRtdServer

    {

        private static int Count = 0;

        private StreamWriter logFile;

        [ExcelFunction(Name = "SomeFunc")]

        public static object BmRtd([ExcelArgument("start")]string start)

        {

            object a = string.Empty;

            try

            {

                a = XlCall.RTD("ExcelDnaRtdTest", null, start);

            }

            catch (Exception e)

            {

                Console.WriteLine("Exception: {0}", e);

            }

            return a;

        }

        private void UpdateAll(object sender, EventArgs e)

        {

        }

        public int ServerStart(IRTDUpdateEvent CallbackObject)

        {

            this.logFile = new StreamWriter(@"C:\rtdTest.log");

            return 1;

        }

        public object ConnectData(int topicId, ref Array strings, ref bool newValues)

        {

            newValues = true;

            this.logFile.WriteLine("{0}:{1}", topicId, Count);

            Console.WriteLine("{0}:{1}", topicId, Count);

            return Count++;

        }

        public Array RefreshData(ref int topicCount)

        {

            return new [,] {{0, 1}, {2, 3}};

        }

        public void DisconnectData(int topicID)

        {

        }

        public int Heartbeat()

        {

            return 1;

        }

        public void ServerTerminate()

        {

            this.logFile.Dispose();

        }

    }

[Govert van Drimmelen]

Hi David,

 

As an example of the mysteries of Excel memory management, you might like to make a macro (in VBA or whatever) that pastes different unique strings into a single cell. This VBA would work:

 

Sub Test()

    For i = 1 To 1000000

        [A1] = "Test" & i

        If i Mod 100 = 0 Then DoEvents

    Next

End Sub

 

Now monitor the process memory while the macro is running.

Excel seems to have some kind of internal string cache, so as you set different strings, the memory usage increases. Only when you close the sheet is the memory reclaimed.

 

-Govert

--

[David Lee]

Hi Govert,

I've been using 

Sub Macro1()

    For Index = 1 To 10000000

        Range("A1").Select

        ActiveCell.FormulaR1C1 = Index

    Next

End Sub

As my macro for updating values. Running it alone doesn't seem to affect memory as much as your string example.

I think this thread has strayed a bit from my original question: Is it possible to have a function which updates live and with parameters changing without causing these topic allocations increasing the memory consumption? Perhaps my use case is unique or my server has a mistake in code. I will investigate further with some open source plugins.

[Govert van Drimmelen]

Hi David,

 

My point was mainly that Excel’s memory management can be quirky.

 

It doesn’t seem like you are running into problems related to Excel-DNA. I’d still suggest you use the ExcelRtdServer base class for your RTD server, since it gives you a thread-safe RTD update implementation, which is required and can be tricky to implement.

How often do your RTD topics need to change in practice?

 

I’d be very interested if you find another implementation that behaves differently.

 

Regards,

Govert

 

 

 

From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of David Lee
Sent: 23 April 2014 16:41
To: exce...@googlegroups.com
Subject: Re: [ExcelDna] ExcelDNA function for changing parameters

 

Hi Govert,

--

[David Lee]

HI Govert,

I don't think my problem is with Excel-DNA. I do have my own implementation of the RtdServer and get the same behavior as the DNA version. The problem seems to be in unmanaged memory pointing at Excel internals. I just posted to see if anyone had similar issues/formulas and if there are any known workarounds.

The inputs to this particular function could change a few times a second, though more realistically probably about several times a minute. But a few thousand of these formulas each creating a new topic a few times a minute for eight hours is just too much. I think I will have to rethink how to implement this particular formula.

Thanks,

David