Non-blocking async Excel Functions

[Sean Tomlinson]

Hi Govert,

I am hoping you can give me some direction on how to best implement non-blocking async functions with ExcelDNA. This is the desired behavior:

• Call some long running async function that will make an external api call
• #GETTING_DATA is returned
• This should be non-blocking for the main Excel thread so that users can interact with the UI
• After the data is fetched it will return as an object[,] (dynamic array)

I have played around already with [ExcelAsyncFunction] following https://github.com/Excel-DNA/Samples/blob/master/Registration.Sample/AsyncFunctionExamples.cs

Here are some questions:

• Using native async results in #CALC! errors while waiting for data, can I configure this to instead return #GETTING_DATA?
• Using this method signature seems preferable to me:
  ```
  [ExcelAsyncFunction]
  public static async Task<object[,]> dnaTest(int rows, int cols)
  ```
  Can you comment on how this works under the hood as I experience this behaviour: Contents of the function is initially blocking until the first "await" is hit, then this is non-blocking, after the return is hit this is blocking again (I imagine this must always be the case due as we need the main excel thread to update the contents?)

[Sean Tomlinson]

Also in all of the samples I see nativeAsyncIfAvailable: false, is there a reason for this, would native not be preferred?

[Kedar Kulkarni]

Hi Sean,

Not a direct answer to your question but here is what I had done in the past. I usually use TaskExcelObservable from   https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs and return observable sequence. (You can use any other implementations of IExcelObservable as well). The first thing you do is manually return the #GETTING_DATA as the first value (not the string but the Excel error) when the calculation task is not completed. In this way #GETTING_DATA is always the first value and the async may take its own time to calculate and the onNext will make the excel to replace #GETTING_DATA with the actual value. 

class TaskExcelObservable : IExcelObservable
        {
            readonly Task<object> _task;

            public TaskExcelObservable(Task<object> task)
            {
                _task = task;
            }

            public IDisposable Subscribe(IExcelObserver observer)
            {

// you can add here but its better to do it only if the task status != TaskStatus.RanToCompletion

//      observer.OnNext(ExcelError.ExcelErrorGettingData );
                switch (_task.Status)
                {
                    case TaskStatus.RanToCompletion:
                        observer.OnNext(_task.Result);
                        observer.OnCompleted();
                        break;
                    case TaskStatus.Faulted:
                        observer.OnError(_task.Exception.InnerException);
                        break;
                    case TaskStatus.Canceled:
                        observer.OnError(new TaskCanceledException(_task));
                        break;
                    default:
                        var task = _task;

                                           
                        // OK - the Task has not completed synchronously
                        // And handle the Task completion

                       observer.OnNext(ExcelError.ExcelErrorGettingData );
                        task.ContinueWith(t =>
                        {

                            
                            switch (t.Status)
                            {
                                case TaskStatus.RanToCompletion:
                                    observer.OnNext(t.Result);
                                    observer.OnCompleted();
                                    break;
                                case TaskStatus.Faulted:
                                    observer.OnError(t.Exception.InnerException);
                                    break;
                                case TaskStatus.Canceled:
                                    observer.OnError(new TaskCanceledException(t));
                                    break;
                            }
                        });
                        break;
                }

                return DefaultDisposable.Instance;
            }

[Sean Tomlinson]

Hi, this works great. One more question is there a way for the sheet to refresh itself when the async call completes, even though it is in manual mode?

[Sean Tomlinson]

To add a little more context, the problem I am trying to overcome:

• When recalculating a sheet in manual mode (Shift+F9) dependent functions do not update correctly. If I chain several volatile async functions they will never reach completion as a recalculation will trigger the initial function again.
• Desired behavior would be for all async functions in a chain to recalculate only when the async portion is finished (and include cross sheet dependencies)

[Kedar Kulkarni]

In my opinion, volatile functions in conjunction with async formulas with manual calculation mode is a lot tricky to handle. My recommendation is to use automatic calculation mode with async.

You could try one of two below 

1. Cache the return value - so lets assume the user sends now() as a parameter to a formula, you should always round it to lets say a minute (after making seconds to 0) and then create the signature for async to be the value rounded to the minute and calculate the values. As now() will always get calculated but as you round it to minute, it would get cached result if recalculated before the minute completes. In the next minute the new value will calculate but subsequent calculation will return the cached value.  This way you wont have a async infinite loop.

2. Create your own version of now() which are nonvolatile and refresh periodically (more granular the frequency, more times the formula will calculate) and ask user to use those as precedent to your formulas. this way you control how frequently the formula calculates by controlling how frequently your version of now calculates. 

the above are just suggestions. there could be other ways to tackle the situation.

The beauty of excel is, excel decides the order of calculation when in automatic calculation mode as excel would then calculate some functions again and again if necessary to get you the latest value and try to keep all calculations to the latest with its logic of building calculation chain and optimizing the calculation chain continuously as and when new formulas get added in the excel session. Now it is becoming a problem for you as excel always thinks that your formula has a stale value because precedent was calculated and may or may not have a newer value (eg Today which calculates but only changes once every 24 hours). 

What you are trying to do is build the logic of excel's calculation chain in your application, which is a bit of futile exercise as we can not take care of all scenarios (eg circular reference, named range, lambda formulas, corrupt named ranges etc) which excel is already doing for you so you may never be able to get the calculation sequence correctly (though it is in theory possible). 

For me - Having calculation mode manual is last resort when in async formulas mode as you may be able to get latest value only by calling range/sheet.calculate as you store the cell references for the formulas calculated till the time they dont have #GETTING_DATA value and has a real value but it marks all volatile formulas dirty so its a slippery path that could lead to user issues (eg having stale values for some formulas.). 

hope this helps and I have not confused you.. 

[Sean Tomlinson]

Thanks this is very helpful and I appreciate your insight. However, for some of my users with large spreadsheets and dependency chains, automatic mode is not desirable as it can take a long time to recalculate all functions (this is amplified by the fact that all of our functions are volatile & async). I have a question that may help to alleviate 
the issue. Using the above approach is it possible to return #GETTING_DATA without signaling to excel that this requires an update? Such that dependent functions are only triggered on the eventual return of the actual data?

[Kedar Kulkarni]

I haven't quite understood your question. As your functions are independent, they need not and should not know if there are any dependent functions. The only thing your UDF should do is return a value OR an array based on input values in its most pure form. 

The rule I usually follow is to mimic Excel's own behavior ie as soon as I encounter an error, I short-circuit and return the same error to the cell without going ahead and checking for any other parameters - similar to the image below where Excel did not care what values other params are and returned #N/A which was a first error)

 

As Getting_data is an error, in my code as soon as any parameter is of type ExcelError, i return the same value and don't bother to return async. (The return value of function is object so it would not create any callback.) User must change precedent to trigger the formula again or our async must return with a valid non error value and then only dependent would trigger custom logic. At the start all formulas should just show #GETTING_DATA as all dependents should short-circuit and return #GETTING_DATA. 

So to sum up,  all your functions should check if the precedent is an ExcelError FIRST and just return the same error and when the right time comes, excel would make sure that you receive correct params and your formula would only execute when you have valid values. 

[Kedar Kulkarni]

btw - the excel would trigger calculation only when the following line is called. If you never call this one, you would not see the output in Excel, but I would not touch that part. 

observer.OnNext(t.Result);