How can I force my own crude "auto-recalculate" while working in an otherwise manual-recalc state?

[d0rian]

I pull in thousands of lines of real-time / tick-by-tick stock option data via my brokerage's RTD server, and I then run a bunch of formulas / calculations on all of the data I pull in (some of them resource-intensive, like real-time ranking, INDEX lookups on a table of 4,000+ rows, etc, though I've made a point to avoid array or dynamic formulas which I'm told are resource-hogs). This results in a performance lag rendering my book largely unusable.

For example: when I populate a new workbook with ONLY the RTD formulas, all of the cells update very promptly (at least once / second). However in my workbook with all of the ancillary formulas and calculations, cells can take 45-seconds or more to update after a value changes in the brokerage's software window, rendering it un-usable for live trading.

But I got an interesting suggestion: if it's the auto-calculation resulting from every new tick (of which there are hundreds coming in every second) that's causing the lag, maybe I can set my workbook to MANUAL calculation, and simply emulate auto-calc by forcing a recalc at a prescribed interval (maybe once / second). How would I accomplish that?

 

[DRSteele]

I avoid VBA as a policy, but I have one workbook similar to yours that uses the following code to update every 1 second. Alter the code to two seconds, or half-second or whatever you fancy. Put a s button in the workbook to START the macro and another to STOP it.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Public whn As Double
 Public Const T = 1 ' ONE second
 Public Const macroo = "refresh" ' the name of the procedure to run[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Sub refresh()
 Application.ActiveSheet.Calculate
 StartTimer
 End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
 Sub StartTimer()
 whn = Now + TimeSerial(0, 0, T)
 Application.OnTime EarliestTime:=whn, Procedure:=macroo, Schedule:=True
 End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
 Sub StopTimer()
 On Error Resume Next
 Application.OnTime EarliestTime:=whn, Procedure:=macroo, Schedule:=False
 End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]