Async Functions Complete Event

[savasozer]

Hi all,

I have a big add-in which contains 2500 async UDFs. These UDFs are calculating in a remote server. In order to avoid round-trip issues I put them in a queue and send them to server in batches. When server complete and send response to the client (my add-in) I call ExcelAsyncHandle.SetResult for every request. Everything works as I expected.

But the problem is I don't know when I send queued requests to my server. Is there any Excel event which notifies me "There are no more async functions which to be calculated" ?

Thank you all

Edit: I know I can wait some delay until no more UDF call. But I don't prefer that way.

[govert]

No, I'm afraid Excel does not have any extra feedback mechanism for the async calls.
For the async batching example I made (this uses the RTD-based async instead of the native async that you are using) my approach was to use a count + timeout to trigger the batches.

[savasozer]

Hi Govert,
Thank you for your reply. I wonder why did you use RTD-async instead of native-async? Is RTD-async more reliable or performant than native async?
I'm asking this because I have an idea to solve my problem. If I use RTD-async's instead of native-async's, when UDF requests completed, Excel probably will call SheetCalculated event even RTD's not completed. This event should be a trigger for me. What do you think ?

[govert]

RTD-based async function work on old version of Excel where there is no native async support. They are also not interrupt by the user's work (if the user starts typing or whatever when the native async functions are busy, they are cancelled and only restarted when the user stops). There is also more flexibility, for example making streaming async functions using RTD (so the async functions are just implemented as one-shot streaming functions).
Native async is much nicer when running workbooks in manual recalculation mode. For this case, the RTD-based async doesn't really work well.
You might like to experiment with both.
I'm not sure exactly what feedback you'd like from the calculation, but even with RTD-based async you won't know whether there are additional functions on the sheet yet to be called. You only know about these function when your UDF is actually called. So you can never know when there are 'no more functions to be calculated' What does that even mean? The user might be about to add some more functions at any time.
RTD-based functions trigger recalculation when new values are pushed, but this is not entirely deterministic. Basically Excel has a timeout, and some other rules which determine when a calculation is triggered after the cells with RTD-function in are set dirty (by the RTD server notifying of new values that are ready for the respective topics).
It sounds like you are trying to fight with Excel too much.
But it might be worth investigating native vs. RTD-based async for your purposes.

[savasozer]

Hi Govert,
Thank you for your detailed explanation. My users have very big XLS files with a lot of formulas, multiple files linked each other even on UNC path, etc... So I'm trying to develop a fast, not hanging add-in. That is why I'm fighting with Excel :) But as you suggested, I will try both ways anyway.