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.

Do you like this free website? Please follow us on Google+

5/8 Completed! Learn more about dates and times >
Back to Top: Delay a Macro   |   Go to Next Chapter: Events