![]() ![]() The StopTimer procedure uses an On Error Resume Next statement to ignore any error that might be generated if you attempt to cancel a non-existent procedure. ![]() You may want to include a call to this procedure from the Auto_Close macro or Workbook_BeforeClose event procedure. This procedure uses the same OnTime syntax used in the StartTimer procedure, except that it has the schedule parameter set to False, which tells Excel to cancel the procedure. Without it, there is no way to access that event.)īelow is a procedure called StopTimer which will stop the pending OnTime procedure.Īpplication.OnTime earliesttime:=RunWhen, _ (The scheduled time works like a “key” to the OnTime method. Otherwise, there would be no way of knowing exactly what time the process was schedule for. ![]() This is why we stored the time in the RunWhen public variable. To stop an OnTime procedure, you must pass the exact scheduled time to the OnTime method. As long as Excel itself remains running, it will execute the OnTime procedure, opening the workbook if necessary. Because the OnTime method is part of the Application object, simply closing the workbook which created the event will not cancel a call to OnTime. This is how the periodic loop is implemented.Īt some point, you or your code will want to stop the timer process, either when the workbook is closed or when some condition is met. And when the The_Sub procedure is called by OnTime the next time, it will again call StartTimer to reschedule itself. This reschedules the procedure to run again. Note that the last line of The_Sub calls the StartTimer procedure. Since is a string variable containing “The_Sub”, Excel will run that procedure at the appropriate time. This stores the date and time two minutes from the current time in the RunWhen variable, and then calls the OnTime method to instruct Excel when to run the cRunWhat procedure. RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)Īpplication.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _ To start the process, use a procedure called StartTimer, similar to the code shown below. Public Const cRunIntervalSeconds = 120 ‘ two minutes The example code will also store the name of the procedure to run and the reschedule interval in public constants, although this is not required. Then, you can use the time stored in that variable to schedule or cancel the event. Therefore, it is advisable to store the time that the procedure is schedule to run in a public (or global) variable, which is available to all your code. You can’t tell Excel to cancel the next scheduled procedure. In order to cancel a pending OnTime procedure, you must pass in the exact time that the procedure is scheduled to run. It is important to remember that you tell Excel specificially when to run the procedure, not an offset from the current time. This page describes the VBA procedures for doing this.Īs arguments, the OnTime method takes a specific date and time, and a procedure to run. By writing your code to call the OnTime method by itself, you can have VBA code automatically execute on a periodic basis. Using VBA, you can call upon the OnTime method of the Excel Application object to instruct Excel to run a procedure at a given time. For example, you may want to refresh data from a data base source every few minutes. You may need to design your Excel workbooks to run a procedure periodically, and automatically. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |