Delay a Macro

 

To execute a sub in Excel VBA after a specific time, use onTime, Now and the TimeValue function. First, place a sub into a module.

1. Open the Visual Basic Editor and click Insert, Module.

2. Add the following sub with name reminder:

Sub reminder()

MsgBox “Don’t forget your meeting at 14.30”

End Sub

We want Excel VBA to execute this sub 5 seconds after a command button is clicked.

2. Place a command button on your worksheet and add the following code line:

Application.OnTime Now() + TimeValue(“00:00:05”), “reminder”

3. Click the command button on the worksheet.

Result 5 seconds later:

Macro executed 5 seconds later

4. If you want to execute this sub at a specific time, simply use the following code line:

Application.OnTime TimeValue(“14:00:00 am”), “reminder”

5. Click the command button on the worksheet and wait until 14:00 AM to see your sub being executed.

Note: you will probably not use Excel VBA to remind yourself of an important meeting, but if you do want, it’s best to put a macro like this in a Workbook Open Event. By doing this, you will automatically receive a reminder at 14:00 AM (you don’t have to click a command button to activate the macro). Of course, you will only receive the reminder when you leave your workbook open.