High freq Range.Calculate() + Application.Calculate() Crash Issue

[Dermot]

Hi Govert,

I have an Excel-DNA addin and workbook for realtime financial data and I have been struggling with a crash issue caused by a combination of Range.Calculate() and Application.Calculate(). I think I probably need a completely different approach and I'm hoping you can advise. I'll outline the high level points below which should give you a good picture of what's happening but if need be I can put together a bare-bones demo app.

PROCESS 1:
- Application.Calculation is always set to xlCalculationManual so all calculation is controlled by the addin. Full Application.Calculate() occurs on configurable scheduler every 2-5 seconds.

PROCESS 2:
- Crypto feed which works as follows:-
(1) Async process in addin gets latest crypto data and updates internal array (all thread safe).
(2) UDF on sheet (public static object?[,] GetCryptoQuotes()) pulls crypo array onto sheet.
(3) Crypto updates occur on scheduler every 50-100ms (configurable). The addin calls Range.Calculate() on the UDF cell, triggering the pull of the latest array.

OTHER POINTS:
- Calls to the Excel macro queue are strictly controlled by an internal regulator in the addin so there is only one request on the macro queue at any one time meaning requests are not piling up on the queue. When crypto update is tiggered by scheduler and there is already something on the macro queue then it simply skips that crypto update.

CRASH:
- Usually occurs between 10-90 mins after startup.
- Crash occurs when PROCESS 1 (Application.Calculate()) is called after PROCESS 2 successfully completes.
- After Application.Calculate() is called the addin completely hangs for about 7-10 seconds i.e. no logging from any of the background async activities.
- The Application.Calculate() does not complete but background async logging resumes after the 7-10 second freeze for about 300 ms before Excel dies.
- I have tried adding pre- and post- delays of 100-2000ms around the Application.Calculate() to let Excel "settle" after the Range.Calculate() but still crashes.
- Running either PROCESS 1 or PROCESS 2 alone works fine - it's the combination of the two which causes the crash. Claude Code says it's some kind of corruption in Excel's internal calculation engine.

Please let me know what you think. Is it too much to expect Excel to handle millisecond level calculations? Should I ditch the UDF and Range.Calculate() approach for PROCESS 2 and update the sheet directly using Range.Value2 = array? Or is there a better aproach? The ideal scenario would be to let everything run off the same Application.Calculate() but that's not an option because the requirement is for crypto to update at a higher frequency.

Thanks a lot!

Dermot

[Govert van Drimmelen]

Hi Dermot,

 

Your approach should work OK, as long as you’re not getting the threading and the timing wrong.

 

Can you give a bit more details about the crash from the event log?

I assume it’s an Access Violation or something, but it’s good to confirm.

One can go down the road of setting up MiniDumps from the crash, getting the Office symbols, and seeing if you can get a stack trace and other insights into the crash.

You could also work towards a minimal reproduction of the issue - if you can crash Excel reliably with reasonable code, you might be able to get some support or attention to the issue. Your update rate does not seem unreasonable to me.

 

How exactly does this part work: “Full Application.Calculate() occurs on configurable scheduler every 2-5 seconds.” ?

What is triggering this scheduled call, and how are you making sure it is made from the main thread, and happens at a time when Excel is not busy calculating?

Similar for the Range.Calculate call – how is that made?

 

Since you’re driving the whole calculation, it’s not clear what you win by doing the extra Range.Calculate calls and pulling the data in with the UDF.

You might as well write to a range, calculate and then read results back.

 

If you do want to move to a calculation-driven approach, you could re-engineer things like this.

* Create an IObservable<T> based UDF for the crypto feed. This is exposed to Excel as an RTD-base stream, where you have some control of the sheet update rate with Application.RTD.ThrottleInterval.

* Create some UDFs that basically monitor outputs – it might write to an output file or buffer of some sort, and gets called as part of the sheet calculation, meaning you don’t have to read the sheet to get calculation results.

* Run the sheet in automatic recalculation mode.

* Note that you’re not guaranteed that Excel will fetch and calculate with every value in the input stream (with extra work, Excel-DNA allows some ways to ensure this). But that does not seem to be what you’re doing currently anyway.

I’m not sure this would perform better or be more stable, but it seems a bit more sympathetic to Excel.

 

Let me know if you have any questions or get more insight into the crash.

 

-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 visit https://groups.google.com/d/msgid/exceldna/a177ad3b-6afd-4279-89ad-752ce63bc3c8n%40googlegroups.com.