Scheduling a Macro to Run Every Two Minutes

My favorite method is to ask Excel to run a certain macro every two minutes. However, I realize that if a macro gets delayed because I accidently left the workbook in Edit mode while going to the staff meeting, then I don't want dozens of updates to happen in a matter of seconds.

The easy solution is to have the ScheduleAnything procedure recursively schedule itself to run again in two minutes. The following code schedules a run in two minutes and then performs Capture Data:

Sub ScheduleAnything()

' This macro can be used to schedule anything

' Enter how often you want to run the macro in hours and minutes WaitHours = 0 WaitMin = 2 WaitSec = 0

NameOfThisProcedure = "ScheduleAnything" NameOfScheduledProc = "CaptureData" 1 — End of Input Section

' Determine the next time this should run

NextTime = Time + TimeSerial(WaitHours, WaitMin, WaitSec)

1 Schedule ThisProcedure to run then

Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfThisProcedure

1 Get the Data

Application.Run NameOfScheduledProc End Sub

There are some advantages of this method. I have not scheduled a million updates in the future. I have only one future update scheduled at any given time. Thus, if I decide that I am tired of seeing the national debt every 15 seconds, I only need to comment out the Application.OnTime line of code and wait 15 seconds for the last update to happen.

0 0

Post a comment