VBA Express : Excel - Schedule a Macro to Run After a Specified Period of Time

Excel

Schedule a Macro to Run After a Specified Period of Time

Ease of Use

Easy

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

This macro demonstrates how to schedule a macro to run after a set amount of time.

Discussion:

You want to run a macro, but not right now. You want it to run in 10 seconds, or 20 minutes, or 7 hours. This macro demonstrates how to do just that.

Code:

instructions for use

			Option Explicit 
 
Sub ScheduleAProcedure() 
     
     'TimeSerial(hours, minutes, seconds)
    Application.OnTime Now + TimeSerial(0, 0, 10), "TimedMacro" 
     
End Sub 
 
Sub TimedMacro() 
     
     'This should be the macro that runs; we simply show a message box
    MsgBox "The code was just executed!", vbInformation 
     
End Sub

How to use:

Open Excel.
Alt + F11 to open the VBE.
Insert Module.
Paste the code there.
Change the TimeSerial line as needed. The syntax is: TimeSerial(Hours, Minutes, Seconds). This is the amount of time that will pass before "TimedMacro" will run.
Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

Tools | Macro | Macros...
Select ScheduleAProcedure and press Run.

Sample File:

TimeMacro.ZIP 6.11KB

Approved by mdmackillop

This entry has been viewed 414 times.