Re: ExcelAsyncUtil.Observe parameters

[Govert van Drimmelen]

Hi David,

You are right - those parameters are used to uniquely identify the
call, so that we can find the async / observable value when the UDF is
called by Excel and we know when to start a new calculation if an
input parameter has changed.

The second parameter to ExcelAsyncUtil.Observe can be an array too. So
your function would be implemented as

object MyFunction(string a, int b, double c, DateTime d)
{
return ExcelAsyncUtil.Observe("MyFunction", new object[] {a, b, c,
d}, () => ...);
}


For this version of Excel-DNA I've tried to get the internals of the
async/observable implementation right, not worrying so much about the
ease-of-use to the add-in developer. In future we might make something
that generates the wrapper from the inside part that is now passed as
a delegate. Your add-in could already implement such an automated
wrapper generator, and register the functions with
ExcelIntegration.RegisterMethods.

-Govert


On Mar 8, 10:24 am, DavidS <dsh...@gmail.com> wrote:
> Hello,
>
> Are the function name and parameter variables passed to
> ExcelAsyncUtil.Observe("MyFunction", parameters, ()=> ...) used simply to
> uniquely identify the request? What is the recommended way to pass, for
> example, the following function parameters to ExcelAsyncUtil.Observe?
>
> *object MyFunction(string a, int b, double c, DateTime d)
> {
> *
> *return ExcelAsyncUtil.Observe("MyFunction", ?, () => ...);
> *
> *}
>
> *
> DavidS

[Nathan Barish]

Hi Govert,

I also have a question about the parameters to ExcelAsyncUtil.Observe.  I have a function that runs differently depending on some state associated with where it is run from (I attached an example).  I noticed that if the function is called from multiple places before the observable finishes both locations get the same value. If I tack on the bit of state I get from the caller location to the parameters I pass to the function things work as I expect.  So I thought maybe I could just pass a GUID. I never want two function calls sharing the same observable, however that didn't work at all.  This makes more sense if you run the attached example and see the results.  

Is the behavior I see in the uncommitted working case in the example file expected behavior?  Why does that work but not the GUID test.

Thanks a bunch!

-Nate

[Govert van Drimmelen]

Hi Nate,

Your understanding is correct the first two parameters to ExcelAsyncUtil.Observe (or ExcelAsyncUtil.Run) are used to uniquely identify a call context. Typically you'd pass the function name for the first, and an array containing the input parameters as the second argument. These arguments are associated with the RTD topic that is created internally.

When the async or observable function completes or updates, Excel is notified that the RTD topic has been updated, and then Excel invalidates the cells that call that topic and when recalculating it calls into the function again. In this second call (if the topic information matches!) the result is then returned from the ExcelAsyncUtil.Observe call and hence to your wrapper function and the worksheet.

Note that you need the initial function call and the second call that Excel makes to that cell after the internal update to share the same context, else the whole mechanism fails.

So if you pass a new Guid during every call, the matching topic is never found, and a new topic is created instead of returning the value from the existing topic.

By passing in the result from xlfCaller as one of the array entries, you make different topics for calls from different cells (even though other parameters are the same). This is correct if you want this behaviour, and expected to work exactly as you show in your sample. Note that you need not build the string with the row/column info - you can put the ExcelReference object that is returned from xlfCaller directly into the array passed into ExcelAsyncUtil.Observe. The internal equality check for this topic information knows about the ExcelReference type.

Let me know if anything is still unclear.

Regards,

Govert

[Govert van Drimmelen]

Hi Nate,

There is another problem with your code, though. This might just be due to how you made the sample, and not in your actual implementation.

In every call of the function, you are making a new Task with the code:

            var longTask = Task.Run(delegate

            {

                Thread.Sleep(5000);

                return callingCellLocation + "  " + myParameter;

            });

However, the function gets called multiple times for every single async call. So you only want to create the task on the first such call (or if the call has not completed yet. 

That's why the third parameter of ExcelAsyncUtil.Observe is not an observable, but a function that will create the observable. Internally it is only called when needed, and not if the value is just to be returned.

A better version might be:

        public static object MyFunction(object myParameter)

        {

            var callingCell = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

            var parameters2 = new object[2];

            parameters2[0] = myParameter;

            parameters2[1] = callingCell;

            return ExcelAsyncUtil.Observe("MyFunction", parameters2,

                () =>

                {

                    // Only create the long running task inside the delegate here, which is not actually called for every call of the outside wrapper function

                    var callingCellLocation = "(" + callingCell.RowFirst + "," + callingCell.ColumnFirst + ")";

                    var longTask = Task.Run(delegate

                    {

                        Thread.Sleep(5000);

                        return callingCellLocation + "  " + myParameter;

                    });

                    return longTask.ToExcelObservable();

                });

        }

-Govert

[Nathan Barish]

Very helpful, thanks.

I had been wondering why my function was get called twice when I only entered it once in Excel.  Now I understand!

On Friday, March 8, 2013 at 4:34:34 AM UTC-5, Govert van Drimmelen wrote:

[Nathan Barish]

One more question, to confirm my experiments:

Is it correct that the function name passed does not need to be the same as the actual excel function name?  Its just another part of the key used to uniquely identify the RTD topic.

Thanks

[Govert van Drimmelen]

Yes.

 

-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 https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Jon Lee]

Hi Govert,

I think I'm having a problem related to this topic. If I have an Array returning async UDF which calls ExcelAsync.Util.Run() and I call it multiple times with the same parameters, the first time it calculates properly but the other times does not recalculate and just returns back the same first results. I do wait until the first async function to return before calling it again - so the flag should be reset to calculate even if it's the same parameters again. This happens even if I call the UDF with different parameters and then call it again with the same. This seems to only happen with Array async UDF.

Jon

[Govert van Drimmelen]

Hi Jon,

An Excel-DNA async function that you make with ExcelAsyncUtil.Run(...) or ExcelAsyncUtil.Observe(...) is based on the Excel RTD functionality, and will have the same restrictions and bugs as Excel RTD functions. According to Microsoft, an Excel RTD function cannot return an array. See: https://support.microsoft.com/en-us/kb/286258 When a wrapper around an RTD function returns an array, we know the behaviour of Excel is quite strange. The wrapper function will get called multiple times, once for every cell in the array, and once for the overall array.

There is another issue with RTD functions called via array formulas - the RTD topic Disconnect is not called correctly be Excel. A consequence of this behaviour is the problem you see - the RTD topic is never cleared internally, so you get the same value next time you call it with the same parameters.

The best workaround is to split the async array function into two parts, one that is async and returns a 'handle' as a single value, and the other that is an array function that unpacks the data pointed at by the handle. You'd have to use a separate cell to hold the handle, in this case, so it's a bit less convenient to use. But it works well.

-Govert

[Jon Lee]

Hi Govert

Yes I keep falling for this same problem. Excel, RTD and array are just a bad combination.

Thanks for reminding.

Jon