General question about modern async functions

[Russ Freeman]

Hi there

- Excel 365

- C# 4.72

I have some functions using AsyncTaskUtil.RunTask(), which do some remote https calls before caching and returning the result.

These functions always return #N/A and after a second forced re-calc, return the (cached) data.  It's usable but I'm not sure why they don't automatically update once the async function has completed.

I've got auto-calc switched off. If I turn auto calc on it does eventually update the cell.  So is it the case that auto-calc needs to be on for async function to automatically display the result?

Thanks in advance

Russ

[Kedar Kulkarni]

Yes, The asynchronous functions work well only if you have calculation mode automatic (or semi-automatic). Excel usually refreshes the cell when a new result is available. (You can mask the #N/A error to show #Getting_Data.) 

You may press F9 to calculate however it will only show the values for the functions where the output is available and one may end up pressing F9 again and again till all values are populated.

[Brian]

Hi Kedar,

You wrote: You can mask the #N/A error to show #Getting_Data.

Would you kindly share the recipe to do that?

Thanks

Brian

[Kedar Kulkarni]

You can check https://groups.google.com/g/exceldna/c/f7ZjKc_8bfY/m/tFnjx0_-AgAJ as an example. I usually use batching for formulas and use a lot of reactive extensions for asynchronous functions - so its easier for us to push values to excel multiple times.  

There is another way as well to mask all #N/A to #Getting_Data - I will try to find it and share.

[Kedar Kulkarni]

For the second method I mentioned earlier.. please check https://groups.google.com/g/exceldna/c/hiMbHhIr1u8/m/on1zbtiXBgAJ 

I think  .AddReturnConversion(  converts the #N/A to #Getting_Data

static ParameterConversionConfiguration GetPostAsyncReturnConversionConfig()

        {

            // This conversion replaces the default #N/A return value of async functions with the #GETTING_DATA value.

            var rval = ExcelError.ExcelErrorGettingData;

            return new ParameterConversionConfiguration()

                .AddReturnConversion((type, customAttributes) => type != typeof(object) ? null : ((Expression<Func<object, object>>)

                                                ((object returnValue) => returnValue.Equals(ExcelError.ExcelErrorNA) ? rval : returnValue)));

        }