AsyncBatchUtil, ExcelRtdObserver not disposing Observable

[Amael Noel]

Hi Govert,

I'm currently using the AsyncBatchUtil to call async service (API).
The async service is called from a volatile function...

To avoid the infinity loop issue, as you suggested in another post, I've created a cache to return the cache response when the data has been returned from the API.

I've also stored the previous XLCcalculation value, to set the calculation to Manual before running my script and I put it back before calling the Resize(Array)

Also the cache will discard the reponse after 1 minute or so based on config

It is working fine up to that point

My issue is the following :

When I try to refresh the cell. 
It is calling the ExcelAsyncUtil.Run or ExcelAsyncUtil.Observe with a delegate async method (the method that call the APi return a Task<IEnumerable<>>).

Issue is that the delegate is not called after the 1st refresh as ExcelRtdObserver  contained in AsyncBatchUtil don't call the following method for some reason
_observableStates.remove()
_asyncCallIds.Remove()

Meaning that when I try to refresh I get the previous task<IEnumerable<> stored in  ExcelRtdObserver   and my service (API) is not called....

Any suggestions will be greatly appreciated

Amael

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors - Sponsor @Excel-DNA on GitHub Sponsors

Consider setting up a regular donation, with easy billing through a corporate GitHub account.

--------------------------------------------------

 

Hi Amael,

 

It sounds like you have the array resizing involved in this call, which I guess means you are calling the function in question from an array formula and trying to return an array result.

 

That means you might be trying to use

• an RTD-based async function (like the AsyncBatchUtil or ExcelRtdObserver)
• from a version of Excel that does not support Dynamic Arrays
• from an array formula.

 

This combination will not work well, and the only workaround is to remove one of the three pieces.

 

The problem is that the pre-Dynamic Arrays version of Excel has a bug in the RTD feature which means the RTD topic disconnect is not always called when used in an array formula. There are also other problems with how RTD-based array functions are evaluated, which cause trouble with the array Resizer.

These Excel RTD problems have been completely fixed in the Dynamic Arrays versions of Excel.

 

So, possible workarounds might be

• Upgrade to a Dynamic Arrays version of Excel, where RTD calls work correctly for array results too, or
• give up on async for these for these functions, or
• restructure your functions so that there is a single-cell function that is async and returns some handle to the Excel sheet, combined with a second ‘unpacking’ functions that takes a reference to the single handle cell, and returns the data using the handle, possibly resizing the array result. Note that the handle must be returned in its own cell.

 

-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/b7d1fd53-8d1f-4bf7-ac7a-9fbb7718a35an%40googlegroups.com.

[Amael Noel]

Hi Govert,

Thanks for lot for your reply,

Indeed, I'm using the resize() function with AsyncBatchUtil for a version of Excel that doesn't support dynamic array...

Regarding Excel Dynamic Array,
Currenlty we are using Office Standard 2016 and should at some point move to Office 365... So I will have to wait a bit to use Excel Dynamic Array...

Until that day come. I decided to use a "trickshot" to sort out this ExcelAsyncUtil cache issue.

So I decided to include a timestamps (MM-dd hh:mm) to the name of the function passed tot he ExcelAsyncUtil as it is only use for caching the query and the response.

Which implied that many key / delegate will be stored in the ExcelAsyncUtil cache (based on the frequency of refreshness and how long the user will keep Excel open on their computer...)

I just hope that it will not blow up on users :)

Thanks,

Amael