VBA - Run at Set Intervals

[The Power Loon]

I have a script I want to run every 15 minutes, for 5 hours (20 times). I can't use a loop and wait, because it doesn't factor in the time it takes for the script to run, which can vary and throw off when exactly it starts running in subsequent loops.

Is there a way to set a timer that excludes processing time?

 

[dragontbone]

You can try using this code.

 ' if you wanna change the timing, the format is Hours:Minutes:Seconds
       
        Application.OnTime Now + TimeValue("00:00:05")

You'd have to account for each time you looped through the code, but that should add a timer to when you wanna run the code

 

[Micron]

You'd need a Public variable in a code module (e.g. alertTime) where you'd have code such as

Public Sub EventMacro()
'do whatever here
End Sub

In workbook.open event:

Private Sub workbook_Open()
alertTime = Time + TimeValue("00:02:00") 'this adds 2 min. to time workbook opens for first run
Application.OnTime alertTime, "EventMacro"
End Sub

If to be run immediately on wb opening, then your wb open event would call EventMacro or whatever procedure you need to run.

 

[dragontbone]

You can try using this code.

' This Sub is the main program that calls the other programs

Sub Start_Timer()
    Dim Run_Program As Integer
    Run_Program = Range("Action").Value
    If (Run_Program = 1) Then
       
       ' if you wanna change the timming, the format is Hours:Minutes:Seconds
       
        Application.OnTime Now + TimeValue("00:00:05"), "DoSomething"
        
    End If

End Sub

You'd have to account for each time you looped through the code, but that should add a timer to when you wanna run the code

 

[rollis13]

Hi to all.
This could be another solution. When file opens (can become a separate macro) all twenty processes Application.OnTime are created and will execute as per array time:

Option Explicit
Private Sub Workbook_Open()
    Dim timeArray As Variant
    Dim x      As Long
    timeArray = Array("14:00", "14:15", "14:30", "14:45", "15:00", "15:15", "15:30", "15:45", "16:00", "16:15", "16:30", "16:45", "17:00", "17:15", "17:30", "17:45", "18:00", "18:15", "18:30", "18:45") '<- change as needed
    For x = LBound(timeArray) To UBound(timeArray)
        Application.OnTime TimeValue(timeArray(x)), "MyScript" '<- where MyScript is your macro
    Next x
End Sub