Ontime creating memory issues

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
I use OnTime to snapshot some cells with streaming data every min. I start it at 7am and it runs every min until 5pm
Code:
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?
Code:
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
figured it out. don't load each OnTime in a loop. Instead have the work routing launch itself on an OnTime event.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,432
Messages
5,572,074
Members
412,437
Latest member
PietTheijssen
Top