How to run VBA Excel macro at a specific time and then every 2 hours thereafter

Question

I would like a VBA Excel macro to run every morning @ 8:30AM and then every 2 hours thereafter with the final one @ 4:30pm (10:30am, 12:30pm, 2:30pm,4:30pm).

I have the following two scripts so far, however cannot seem to figure out how to have it start @ 8:30AM:

Private Sub Workbook_Open()        
    Call SetTimeToRun    
End Sub

Private Sub SetTimeToRun()    
    Application.OnTime Now() + TimeValue("02:00:00"), "LiveBook"    
End Sub

Pᴇʜ · Accepted Answer · 2020-01-21T10:37:00.107

If you want to run it a specific amount eg 3 times then simply do:

Application.OnTime Now() + TimeValue("02:00:00"), "LiveBook" 
Application.OnTime Now() + TimeValue("04:00:00"), "LiveBook" 
Application.OnTime Now() + TimeValue("06:00:00"), "LiveBook"

If you want to run it continously every 2 hours without limitation then you need to add

Call SetTimeToRun

to your procedure LiveBook. For Example

Public Sub LiveBook()
    Call SetTimeToRun

    'your code here …
End Sub

So everytime LiveBook runs it initiates the next run in 2 hours.

But note that the workbook needs to be open all the time in that machine otherwise the timed procedures will not run.

If you need to run a macro at a specific times like 8:30 am, 10:30 am. I recommend to use the windows scheduler to run a VB script or something that opens the workbook, runs the macro and closes the workbook. But note that no one else can use the workbook or the scheduler will fail to open the workbook (only one person can open a workbook at a time for editing).

Also see How to set recurring schedule for xlsm file using Windows Task Scheduler.

Thanks, Peh. I was hoping I could simplify the this part of the code to one single line though: Application.OnTime TimeValue("08:30:00"), "LiveBook" Application.OnTime TimeValue("10:30:00"), "LiveBook" Application.OnTime TimeValue("12:30:00"), "LiveBook" Application.OnTime TimeValue("14:30:00"), "LiveBook" Application.OnTime TimeValue("16:30:00"), "LiveBook" — Robert, Jan 22 '20 at 17:50

How to run VBA Excel macro at a specific time and then every 2 hours thereafter

1 Answers1