Calling UDF 300-500 times per second

[typedef]

Hello 

I need to call UDF with rate 300-500 times per second. From Add-In I am calling VBA callback on Excel side with the same rate.

My question, what is some reasonable limit on number of such exchanges. My concerns if app. is running for several hours , ques of unprocessed requests can be seamlessly  accumulated.

Thanks,

Alex.

[Kedar Kulkarni]

Hi Alex,

Can you provide more information as to what the callback is supposed to do? There is no set limit in my opinion as it depends on your environment / available free memory ie if office 64 bit, number of workbooks open, how many formulas are there, how many volatile formulas, if you have broken dependencies / #Ref in named ranges etc. So I am afraid, there is no definitive answer. Microsoft themselves answer many questions in the following link with "Limited by available memory"  https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

If it is an RTD function then you will have a continuous stream and the callbacks would get accumulated and there would be a lot of backpressures that would be built making excel very slow with no end in sight. If you are changing a cell in edit mode then once user presses enter, excel would become nonresponsive as excel would try to execute all accumulated actions before returning the control to the user. 

If it is not an RTD, I assume user would make some changes to the excel workbook / trigger some action, then only the UDF would execute and though there is backpressure built, excel would be nonresponsive momentarily but should be back once queue is cleared and user would get the control back. My suggestion is to combine the action into one action and run using the QueueAsMacro command. 

We have done it multiple ways in the past. 

1. In the vba function we collect the cells that we want to run call back against (eg coloring the cells) in a collection (grouped by worksheet). We used AfterCalculate event to run the callback at once on all the excel references (cells) collected during function calculation at once. Please be careful to not trigger calculation again by changing any cell contents, else you can get into an infinite loop. Coloring cells should not trigger any calculation.

2. You can use timer to run the action against the collected cells periodically ie after every 1 second. Maybe look at the batching example to get an idea.

callback from .net code to vba is also going to be slower if you have your callback in vba, so keep all code in .net preferably as you could run non-UI code in the background. If you provide more information then more ideas could be shared but prima facie running action once against every cell could be very slow and running action on multiple cells at one go should be faster and a lesser number of requests should get accumulated in the queue.

thanks,

Kedar

[Alex Yemets]

Hello Kedar

Thanks for the detailed response. You covered a lot questions that was part of my concerns

In VBA callback I am getting data from the Observable stream that getting called up to 100 times per second. Based on data I ordinate call to UDF that async passes it to the high performance backend. Technically on 100 callbacks I might need to do 300 UDF calls. 

I noticed that the UI gets less responsive when I need to update cell content during full load. I am not experiencing memory issues, but performance. Some callbacks contain JSON that I need to parse. 

I guess in my case best practice is to batch data as much as possible.

Thanks a lot again for your help.

Alex.

--
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/f8b481de-b39f-4faf-96c3-4ca037d5c947n%40googlegroups.com.