Ontime creating memory issues

[yankee428]

I use OnTime to snapshot some cells with streaming data every min. I start it at 7am and it runs every min until 5pm

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

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 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

 

[yankee428]

figured it out. don't load each OnTime in a loop. Instead have the work routing launch itself on an OnTime event.