Re: cell references and ExcelObservable disposal

[Govert van Drimmelen]

Hi David,

There was an Excel bug in the released version of Excel 2010 that
might cause this. See
http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/ba06ac78-7b64-449b-bce4-9a03ac91f0eb/

There is a hotfix available here:
http://support.microsoft.com/kb/2405840

And the fix should also be in SP1.

If this is not the Excel version you are running under, or if the
hotfix or service pack don't fix the issue, please let me know. Then
there might be an issue I am unaware of.


As to what we do in Excel-DNA to deal with the Excel bug, I don't have
a satisfactory answer yet. I think my preference would be to warn the
user if they are running an Excel version with the bug in, when
ExcelAsyncUtil.Initialize() is called. I'm not sure how to check for
this correctly.
Another option is to try to detect the input change and orphan RTD
topic, and dispose of it when called from the same caller again. I
don't know how to make this work right if you have multiple async
function calls in a single formula.

What do you think we should do?

Regards,
Govert




On Mar 5, 11:31 pm, DavidS <dsh...@gmail.com> wrote:
> Hello,
>
> I am studyng the behaviour of asynchronous ExcelDna. I wrote a test program
> which is included below. In my program, MyFunction calls
> ExcelAsyncUtil.Observe(). which allows ExcelDna to create my
> ExcelObservables, as needed. This seems to work fine. If I delete the
> contents of a cell containing the function, ExcelDNA calls Dispose on the
> subscription. This also seems fine. If I change the parameter of the
> function, ExcelDna deletes the old subscription and creates a new
> ExcelObservable. Still fine. But if a parameter of the function refers to
> another cell, and that other cell is changed, ExcelDna does create a new
> ExcelObservable but does not call delete on the subscription. You can
> easily see this if you run the code below and do the following in Excel:
>
> Type in a cell:
> *    =MyFunction("a")*
> Your debugger will show that the function is called, an ExcelObservable is
> constructed, and the observer subscribes to the ExcelObservable. Then there
> are periodic OnNext() calls. Fine. If the cell is deleted, ExcelDna calls
> delete on the ExcelObservable. Fine.
>
> Change the cell to:
> *    =MyFunction("b")*
> Your debugger will show that the function is called, a new ExcelObservable
> is constructed, the observer subscribes to it and delete is called on the
> old ExcelObservable. Fine again.
>
> Change the cell so that the function parameter refers to another cell which
> contains a string
> *    =MyFunction(C1)* where *C1="a"*
> Your debugger will show that the function is called, a new ExcelObservable
> is constructed, the observer subscribes to it and delete is called on the
> old ExcelObservable. So fine.
>
> Change the cell with the parameter to something else, like "b". Your
> debugger will show that the function is called, a new ExcelObservable is
> constructed, the observer subscribes to the new ExcelObservable but ...
> ExcelDna *does not call delete* on the old ExcelObservable. My program
> continues to pump values to ExcelDna.
>
> Any help or suggestions would be much appreciated.
>
> Regards,
>
> DavidS
>
> *
> using System;
> using System.Threading;
> using System.Threading.Tasks;
> using ExcelDna.Integration;
> using NLog;
>
> public class MyExcelObservable : IExcelObservable, IDisposable
> {
> *
> * static readonly Logger logger = LogManager.GetCurrentClassLogger();*
> * readonly string callerFunctionName, callerParameter;*
> * readonly CancellationTokenSource cts = new CancellationTokenSource();*
> * public MyExcelObservable(string f, string p)*
> * {*
> * callerFunctionName = f;*
> * callerParameter = p;*
> * logger.Debug("{0}({1}): Constructor", callerFunctionName,
> callerParameter);*
> * }*
> * public IDisposable Subscribe(IExcelObserver observer)*
> * {*
> * logger.Debug("{0}({1}): Subscribe", callerFunctionName, callerParameter);*
> * Task.Factory.StartNew(() =>*
> * {*
> * int i = 0;*
> * while (!cts.Token.IsCancellationRequested)*
> * {*
> * i++;*
> * logger.Debug("{0}({1}) OnNext({2})", callerFunctionName, callerParameter,
> i);*
> * observer.OnNext(i);*
> * Thread.Sleep(4000);*
> * }*
> * }, cts.Token*
> * );*
> * return this; // observer calls Dispose() on this to unsubscribe*
> * }*
> * public void Dispose() // on unsubscribe*
> * {*
> * cts.Cancel();*
> * logger.Debug("{0}({1}): Observer unsubscribed, task cancelling",
> callerFunctionName, callerParameter);*
> * }*
> **
> * }
> public class MyFunctions
> {
> *
> * static readonly Logger logger = LogManager.GetCurrentClassLogger();*
> * [ExcelFunction()]*
> * public static object MyFunction(string callerParameter)*
> * {*
> * ** const string callerFunctionName = "MyFunction";*
> * logger.Debug("{0}({1}): Function called", callerFunctionName,
> callerParameter);*
> * return ExcelAsyncUtil.Observe(callerFunctionName, callerParameter, () =>
> new MyExcelObservable(callerFunctionName, callerParameter));*
> * }*
> **
> * }
> *

[Govert van Drimmelen]

Also see this discussion, with a suggested patch:
https://groups.google.com/group/exceldna/browse_frm/thread/644c4900135b1f0c

-Govert

On Mar 5, 11:48 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi David,
>
> There was an Excel bug in the released version of Excel 2010 that

> might cause this. Seehttp://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/ba06ac7...

[Govert van Drimmelen]

Hi David,

Thank you very much for having a close look at the async / RxExcel
feature, and your detailed questions. If you find it useful, I hope
you'll be able to post a description or example somewhere of how you
are using it.

-----

I've made a check-in on CodePlex (change set 78811 here:
http://exceldna.codeplex.com/SourceControl/list/changesets ) that also
includes a fix for the RTD Disconnect issue under unpatched Excel
2010. I haven't tested it myself, but I believe it addresses the issue
completely, without too much overhead. Maybe you can check that this
version still works as you expect. If you have access to an unpatched
Excel 2010, some feedback on whether the fix works would be very
welcome. With this version you should always see the Disconnect /
Dispose call as expected, even under the buggy Excel 2010 release
version.

-----

As to your other issues: I agree with your description of the async /
ExcelObservable behaviour, but it's the best implementation I could
figure out given how Excel's RTD feature works. The behaviour you
describe is as expected.

1. The wrapper UDF is called for every update.
We have no control over when Excel calls the wrapper UDF - in
particular, for any RTD wrapper UDF Excel will always call the UDF
when the RTD value changes. In general, the UDF need not return the
value that the RTD Server provides, a feature that we use e.g. to
return strings longer than 255 characters. The ExcelAsyncUtil.Run and
ExcelAsyncUtil.Observe functions are written so that the extra calls
to the wrapper UDF are very lightweight, and in particular don't call
into your async function or observable at all. I can't imagine the
overhead of the extra calls being significant (unless you're doing
something like logging every call), but anyway we have no way to avoid
these calls.

The only way to eliminate the extra UDF call is to make a plain RTD
server (perhaps using Excel-DNA's thread-safe ExcelRtdServer base
class), doing the COM registration yourself, and then putting raw RTD
calls in the sheet, like =RTD("MyCool.DataSource",,"Close", "MSFT")

Otherwise Excel will always make the extra calls to the UDF.

2. The #N/A return before the first value.
Excel-DNA is does not 'send' the #N/A value to the cell, our UDF
wrapper must return *something*. I've chosen for the UDF wrapper to
return #N/A while the async call is outstanding / OnNext has not been
called. It's not easy for the UDF to wait for the first OnNext at that
point - it would basically have to suspend Excel completely until the
OnNext happens, which is not what we want at all.

Of course you can customize the wrapper UDF to return something other
than the #N/A value, or provide an immediate OnNext value of your
choice. But we need to return something from the UDF...

3. Mixing async / Observe with volatile functions doesn't work
Your example of MyFunction(TEXT(NOW(),"HH:mm:ss.sss")) is actually a
different case to the #N/A story above. Mixing async / observe
functions with volatile inputs will not work. Every call with a new
parameter would Dispose the observable and then create a new
observable (with the new parameters) which is subscribed to, followed
by an update to the UDF which repeats the cycle. Your UDF could ignore
the input parameter (just don't pass it to the ExcelAsyncUtil.Observe
call), but then you need some out-of-band way of dealing with changes
to the input value. The current implementation is quite general in
this regard.

With the normal use, where the input parameters are used to create the
observable, I can't think of a sensible way for volatile to mix with
async functions, and am happy if Excel does not crash in this case.
I'd say the perpetual '#N/A' in this case would be 'by design'.

-----

I hope my answer addresses these concerns, and look forward to some
more questions... :-)

Regards,
Govert



On Mar 6, 2:22 pm, DavidS <dsh...@gmail.com> wrote:
> Hello Govert,
>
> With Excel 2010 SP1, my ExcelDna program now does call delete correctly. So
> it seems the reported bug was the problem. A great thanks! I suggest you
> just warn developers in the release notes rather than trying to workaround
> this Excel bug. The fix is easy (upgrade to SP1), but a workaround could be
> difficult and possibly break other functionality.
>
> More questions. I am looking at the debug output from my ExcelDna test
> program. I see that when I type my MyFunction() into Excel, first Excel
> calls MyFunction(), then an ExcelObservable object is created, then the
> observer subscribes. After that, data is sent to the subscriber using a
> task whick periodically calls OnNext(value). This seems to be what I would
> expect. However, each time after calling OnNext(value), Excel calls then
> MyFunction() again for every worksheet cell which contains MyFunction(). I
> wonder why this happens and whether it is necessary. I would expect that
> MyFunction() would only be called only when a MyFunction() is first typed
> into Excel or when the MyFunction(parameter) parameter changes. I have
> attached an image file showing the debug output. Please have a look at the
> middle section which shows what happens when there are three cells
> containing MyFunction().
>
> One more thing. When I type MyFunction() in a cell or change the
> MyFunction(parameter) parameter, first Excel calls MyFunction, then an
> ExcelObservable object is created, then the observer subscribes. At this
> point, since OnNext() has not yet been called, ExcelDna does not know the
> value to be returned to the cell, and ExcelDna returns "#NA". This behavior
> could especially cause problems with functions that take a date as
> parameter, like, for example MyFunction(TEXT(NOW(),"HH:mm:ss.sss")), which,
> will continually display "#NA". I think it would be better if ExcelDna did
> not send the first result ("#NA") to the cell until the next Rtd refresh
> (which defaults to every 2 seconds). Please have a look at the third
> section of the attached image which roughly shows at what point cells "#NA"
> is shown in cells.
>
> Thanks again for the help.
>
> Regards,
>
> David
>
>  all.png
> 104KViewDownload

[Naju Mancheril]

Hi David,

Here are some alternative approaches.

Approach 1: Maybe your users would be happy using an RTD replacement for NOW() rather than the volatile one. Here is how to make one:


    [ExcelFunction(Name="ExcelDna.Now")]
    public static object ExcelDnaNow(int ms) {
      var delay=TimeSpan.FromMilliseconds(ms);
      var values=Observable.Timer(delay, delay)
        .Timestamp()
        .Select(x => (object)x.Timestamp.ToString("HH:mm:ss"));
      return ExcelAsyncUtil.Observe("ExcelDnaNow", ms, values.AsExcelObservable);
    }

This uses the Rx Observable class. It also uses these helper classes which convert IObservable<object> into the IExcelObservable (not sure if there's an easier way to do this, Govert would know).


    private class MyObserver<TValue> : IObserver<TValue> {
      private readonly IExcelObserver observer;

      public MyObserver(IExcelObserver observer) {
        this.observer=observer;
      }

      public void OnNext(TValue value) {
        observer.OnNext(value);
      }

      public void OnError(Exception error) {
        observer.OnError(error);
      }

      public void OnCompleted() {
        observer.OnCompleted();
      }
    }


    private class MyExcelObservable<TValue> : IExcelObservable {
      private readonly IObservable<TValue> source;

      public MyExcelObservable(IObservable<TValue> source) {
        this.source=source;
      }

      public IDisposable Subscribe(IExcelObserver observer) {
        return source.Subscribe(new MyObserver<TValue>(observer));
      }
    }

And finally, this extension method:

    public static IExcelObservable AsExcelObservable<TValue>(this IObservable<TValue> source) {
      return new MyExcelObservable<TValue>(source);
    }

Approach 2: Combine a tick stream with the other IObservable stream you currently have have. For example:

    [ExcelFunction(Name="ExcelDna.Now")]
    public static object ExcelDnaNow(int ms) {
      var delay=TimeSpan.FromMilliseconds(ms);
      var inputs1=Observable.Return("some value");
      var ticks=Observable.Timer(delay, delay).Timestamp();
      var results=inputs1.CombineLatest(ticks, (v, t) => {
        // do crazy time-based calculate here!
        // return object
        return (object)t.Timestamp.ToString("HH:mm:ss");
      });
      return ExcelAsyncUtil.Observe("ExcelDnaNow", ms, results.AsExcelObservable);
    }

Here, inputs1 is an IObservable that you already have. ticks is a stream that ticks every 'ms' milliseconds. A new result value will be calculated if either inputs1 or ticks produces a new value.

Try this with =ExcelDna.Now(1000).

Approach 3: If you don't have an "fixed" IObservable, but want to build one per tick. You can use .Select() to make a new IObservable when a new tick arrives and .Switch() to thread the results of *that* stream into one sequence of result values.

    [ExcelFunction(Name="ExcelDna.Now")]
    public static object ExcelDnaNow(int ms) {
      var delay=TimeSpan.FromMilliseconds(ms);
      var smallerDelay=TimeSpan.FromMilliseconds(ms/10);
      var ticks=Observable.Timer(delay, delay).Timestamp();
      var results=ticks
        .Select(x => Observable.Timer(smallerDelay, smallerDelay)
                       .Select(y => String.Format("Tick {0} from stream built at {1:HH:mm:ss}", y, x.Timestamp)))
        .Switch()
        .Select(x => {
          // return object
          return (object)x;
        });
      return ExcelAsyncUtil.Observe("ExcelDnaNow", ms, results.AsExcelObservable);
    }

Try this with =ExcelDna.Now(1000). It's very cool!

[Govert van Drimmelen]

Hi Naju,

I want to suggest an important improvement to how you call the
ExcelAsyncUtil.Observe:

You should create the observable inside the delegate passed to the
Observe method, and not every time the UDF is called. Your first
example might rather be:

[ExcelFunction(Name="ExcelDna.Now")]
public static object ExcelDnaNow(int ms)
{

return ExcelAsyncUtil.Observe("ExcelDnaNow", ms, () =>
{

var delay=TimeSpan.FromMilliseconds(ms);
var values=Observable.Timer(delay, delay)
.Timestamp()
.Select(x =>
(object)x.Timestamp.ToString("HH:mm:ss"));

return values.AsExcelObservable();
});
}

Otherwise you are doing the construction of the Observable many, many
times, though it will only get started once and would have worked
correctly. With all the work pushed into the delegate, it will only
run once, as intended.

The other examples would change similarly.

The helper class looks right - there is something similar in
Distribution\Samples\AsyncFunction\RxExcel.cs. The only reason we're
not using the 'real' IObservable interfaces is to continue to
target .NET 2.0 with the compiled Excel-DNA.


With the helpers in the RxExcel.cs file, your code for the first
example would become:

[ExcelFunction(Name="ExcelDna.Now")]
public static object ExcelDnaNow(int ms)
{

return RxExcel.Observe("ExcelDnaNow", ms, () =>
{
var delay=TimeSpan.FromMilliseconds(ms);
return Observable.Timer(delay, delay)
.Timestamp()
.Select(x => x.Timestamp.ToString("HH:mm:ss"));
});
}

-Govert

On Mar 7, 12:41 am, Naju Mancheril <naju.manche...@gmail.com> wrote:
> Hi David,
>
> Here are some alternative approaches.
>

> *Approach 1: *Maybe your users would be happy using an RTD replacement for

> *Approach 2:* Combine a tick stream with the other IObservable stream you

> currently have have. For example:
>
>     [ExcelFunction(Name="ExcelDna.Now")]
>     public static object ExcelDnaNow(int ms) {
>       var delay=TimeSpan.FromMilliseconds(ms);
>       var inputs1=Observable.Return("some value");
>       var ticks=Observable.Timer(delay, delay).Timestamp();
>       var results=inputs1.CombineLatest(ticks, (v, t) => {
>         // do crazy time-based calculate here!
>         // return object
>         return (object)t.Timestamp.ToString("HH:mm:ss");
>       });
>       return ExcelAsyncUtil.Observe("ExcelDnaNow", ms,
> results.AsExcelObservable);
>     }
>
> Here, inputs1 is an IObservable that you already have. ticks is a stream
> that ticks every 'ms' milliseconds. A new result value will be calculated
> if either inputs1 or ticks produces a new value.
>
> Try this with =ExcelDna.Now(1000).
>

> *Approach 3:* If you don't have an "fixed" IObservable, but want to build

[Govert van Drimmelen]

Hi David,

Thank you - I've now been able to check too, and indeed the Disconnect
bug under Excel 2010 is still there.

I don't have another plan...

-Govert


On Mar 7, 12:28 pm, DavidS <dsh...@gmail.com> wrote:
> Hi Govert,
>
> Thank you for your very detailed response.
>
> Although I am now using Excel 2010 SP1, I kept a copy of the virtual
> machine containing my previous development environment. I tested your 7881
> source code for the RTD disconnect issue under unpatched Excel 2010. The
> changes did not fix the bug. Sorry! Line 223 of ExcelRtdServer.cs was not
> called:
>
> if (_activeTopics.ContainsKey(topicId))
> {
> ((IRtdServer)this).DisconnectData(topicId); // line 223
>
> }
>
> 1. It is helpful to know that Excel calls the wrapper UDF for every update.
> I can now see that this is the mechanism which is used to send  updates to
> Excel. You state that the only way to avoid extra UDF calls is with a plain
> RTD server. That's where I coming from. I have been using such a plain RTD
> server in my program for many years. Incidentally, I asked you some
> questions about ExcelDna RTD 6 years ago and I remember you were also very
> helpful at that time! Back then I decided not to use ExcelDna because the
> RTD functionality seemed immature. Now I am revisiting ExcelDna and hope to
> improve my program using ExcelDna's RTD or Async capabilities.
>
> 2. Yes, I now see that I can control the initial values sent to Excel by
> calling OnNext(initial value) as soon as the observer subscribes. Perfect.
>
> 3. I can now see why async/Observe with volatile don't mix. It was not a
> good example.
>
> Thanks again!
>
> Regards,
>
> David

>
>
>
>
>
>
>
> On Thursday, March 7, 2013 6:09:39 AM UTC+8, Govert van Drimmelen wrote:
>
> > Hi David,
>
> > Thank you very much for having a close look at the async / RxExcel
> > feature, and your detailed questions. If you find it useful, I hope
> > you'll be able to post a description or example somewhere of how you
> > are using it.
>
> > -----
>
> > I've made a check-in on CodePlex (change set 78811 here:

> >http://exceldna.codeplex.com/SourceControl/list/changesets) that also

[Naju Mancheril]

Hi Govert,

Thanks for the deferred construction tip. I will also start using the RxExcel class.

Thanks,
Naju