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:
3. Click the command button on the worksheet.
Result 5 seconds later:
4. If you want to execute this sub at a specific time, simply use the following code line:
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.
Next Chapter: Events
