Async worksheet function not being re-triggered on F2-Return

48 views
Skip to first unread message

Simon Pearson

unread,
Jan 15, 2022, 8:00:18 AMJan 15
to Excel-DNA
Hi,

I have come across an issue - which may be down to my own inabilities - where an async function that returns an IObservable<> is not re-triggered when the cell is edited (but the inputs are not changed).

For normal Excel UDFs in both VBA and ExcelDna I would expect that the UDF would be called every time the cell is edited, but in this case it is not occurring.

Here is a much-simplified example:

using System;
using System.Reactive.Linq;
using ExcelDna.Integration;
using ExcelDna.Registration;

namespace Pearson.Excel.Plugin
{
    public class Addin : IExcelAddIn
    {
        public void AutoOpen()
        {
            ExcelIntegration.RegisterUnhandledExceptionHandler(ex=>$"!!! EXCEPTION: {ex.ToString()}");

            ExcelRegistration.GetExcelFunctions()
                .ProcessAsyncRegistrations()
                .RegisterFunctions();
        }

        [ExcelAsyncFunction]
        public static IObservable<object> getInfiniteStream(double sleepInterval)
        {
            var counter = 0;

            return Observable.Interval(TimeSpan.FromSeconds(sleepInterval))
                .Select(_ =>
                {
                    counter++;
                    return counter as object;
                });
        }

        public void AutoClose()
        {
            throw new NotImplementedException();
        }

    }
}

I'm imagining the issue will be something to do with the long-running observable that doesn't complete - but this is actually the pattern I need as the real use case is subscribing to a long-running streaming web API.

I have had a quick look at FunctionExecutionHandlers in the Registration library but couldn't work out if they are appropriate here.

Any help would be greatly appreciated.

Thanks a lot,

Simon.

Govert van Drimmelen

unread,
Jan 15, 2022, 9:56:01 AMJan 15
to exce...@googlegroups.com

Hi Simon,

 

In principle the Registration extension does something like this to your IObservable function:

 

             *      static IObservable<string> myFunc(string name, int msDelay) {...}

             *

             *   is rewritten to

             *

             *      static object myFunc(string name, int msDelay)

             *      {

             *          return ExcelAsyncUtil.Observe<string>(          // obsMethod

             *              "myFunc",                                   // name

             *              new object[] {(object)name, (object)msDelay},   // paramsArrayExp

             *              () => myFunc(name, msDelay));                   // innerLambda

             *      }

 

I’ll call the original myFunc the ‘inner’ function and the second version the ‘wrapper’ function.

 

Then you have the scenario where

  • a formula is entered into a cell, say =myFunc(“xyz”, 1000)
  • this causes the wrapper to execute, calling the ExcelAsyncUtil.Observe with the parameters and the innerLamdba
  • since there is no live RTD topic (the internal Excel mechanism) with the name and parameters, a topic is set up and the inner function is called to set up the observable
  • as the observable updates, the Excel-DNA RTD server notifies Excel to recalc the cell
  • the wrapper function is called again in every recalc (with the same function parameters)
  • since there is now a matching RTD topic, the value that was pushed from the observable is returned directly, skipping the inner function

 

Now pressing F2 & Enter on the cell recalculates the wrapper function, but looks exactly like the calculation triggered by the observable update.

So the RTD topic is found and the value returned directly, without running the inner function again.

 

If the parameters do change between calls, the ExcelAsyncUtil.Observe will notice that there is no matching RTD topic for the name and parameters, and will set up a new topic, and call the inner function to get the observable to hook up.

By not getting the RTD value for the old topic when the cell is recalculated, Excel understands that the old topic must be disconnected, and Excel -DNA ensures that Dispose is called on the IDisposable returned from your observable’s Subscribe method.

Similar if the formula is deleted.

 

I hope that explains what you’re seeing as being the ‘expected’ behaviour.

I think this is what you are asking about.

 

Thus using F2 is not a good way to explicitly rigger a reset of the observable (and causing the inner function to be called again).

Inside the wrapper function we can’t distinguish the F2 & Enter recalculation from the RTD / observable update causing a calculation of the wrapper.

You could try to set some internal flags and track the timing, but that will probably be hard to make reliable, as the observable-triggered recalc can happen much later if Excel has been busy.

 

There are some options I can think of:

  • You can make a trigger parameter that is (perhaps optionally) added to your list of parameter just for the purpose of resetting the observables. This can come from a single cell to update a whole batch of formulas.
  • You can set up an external mechanism to trigger a reset, for example a ribbon button(s). For this you would set up some global (static) mechanism to track the reset requests, and then have the observable also monitor this and trigger a reset or extra update if signalled.

 

-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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/4c8aa77b-8736-41e4-9978-ce7da3dd2e2dn%40googlegroups.com.

Simon Pearson

unread,
Jan 15, 2022, 11:33:08 AMJan 15
to Excel-DNA
Thanks for the explanation Govert.

I think i will inject an extra param into the registration of my streaming functions to enable the user to retrigger the call if required.

It's a little bit ugly but definitely the most reliable.

Thanks,

Simon.

Reply all
Reply to author
Forward
0 new messages