I use OnTime to snapshot some cells with streaming data every min. I start it at 7am and it runs every min until 5pm
the MarketSnapshot process is listed below. It basically copies the data as values to a different range. Running it at 7am has no impact on my PC, but by 6pm it slows down my PC. My understanding of OnTime is that it will load up each loop into memory, then unwind/execute as time progresses. If this is the case, should performance improve over time, not decrease?
Sub MarketSnapshotAuto() Dim rngMyRng As Range Set rngMyRng = ThisWorkbook.Sheets("Market Snapshot").Range("Time") For Each cel In rngMyRng Application.OnTime cel, "MarketSnapshot" Next End Sub
Sub MarketSnapshot() Dim SheetStart As String SheetStart = ActiveSheet.Name Application.ScreenUpdating = False Sheets("Market Snapshot").Select Range("SnapNum").Value = Range("SnapNum").Value + 1 Calculate Range("SnapshotAbsLive").Copy Range("SnapshotAbs").Cells(1, 1) = Time Range("SnapshotAbs").Range(Cells(1, 2), Cells(1, 38)).PasteSpecial xlPasteValues 'Range("SnapshotAbs").Cells(1, 39) = 0 Range("SnapshotChangeLive").Copy Range("SnapshotChange").Cells(1, 1) = Time Range("SnapshotChange").Range(Cells(1, 2), Cells(1, 32)).PasteSpecial xlPasteValues Range("A1").Select Application.CutCopyMode = False Sheets(SheetStart).Select Application.ScreenUpdating = True End Sub