Keep previous value instead of #N/A while recalculating cell with ExcelAsyncUtil

[Mauri Pastorini]

Hi, I was working with ExcelAsyncUtil for recalculating cells that need to query and return some external API value, Async for not blocking the sheet while it makes the request. 

The thing is that when this functions starts recalculating, It shows #N/A and I want to keep the previous value while the ExcelAsyncUtil doesnt finish, so other cells may use this current value before a new value. I know that maybe what I want is against Excel workflow, so in that case how can I do for implementing this feature with Excel-DNA?

Here is an image and the code:

        [ExcelFunction(IsVolatile = true)]
        public static object AsyncRCallTest(int a1, int a2)
        {
            Debug.WriteLine("Async called");
            return ExcelAsyncUtil.Run("AsyncRCallTest", new object[] { a1, a2 }, delegate
            {
                return SyncRCallTest(a1, a2);
            });
        }




        [MethodImpl(MethodImplOptions.Synchronized)]
        public static object SyncRCallTest(int a1, int a2)
        {
            try
            {
                Thread.Sleep(5000); //Some long time request
                return DateTime.Now.ToString("HH:mm:ss.fff");
            }
            catch (Exception err)
            {
                return err.Message;
            }
        }

Thank you

[Govert van Drimmelen]

One way to do this might be to implement it as a ‘streaming’ function with IExcelObservable and ExcelAsyncUtil.Observe rather than ExcleAsyncUtil.Run.

Then you immediately return the old value (which you must cache and keep track of for the next time), and later return a newer value when you have one.

 

You have to decide when the function should be updated again – is this tied to the user recalculation or do you have another trigger, e.g. based on time.

 

Setting these functions (async or streaming) as IsVolatile=true is normally a mistake.

 

It’s tricky to implement this kind of thing, especially when it stretches the Excel calculation model a bit.

 

-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.

[Mauri Pastorini]

Hi Govert, thank you for your fast answer.

Sorry but I am new with Excel DNA, can you explain more how can I achieve that? 

Thanks