You are currently offline, waiting for your internet to reconnect

XL2000: Canceling OnTime Macro When Time Argument Is Volatile

This article was previously published under Q213452
This article has been archived. It is offered "as is" and will no longer be updated.
In Visual Basic for Applications, canceling a recursive OnTime macro may be difficult when the earliestTime argument is volatile (for example, if you are using the current time).
To cancel an OnTime method, you must call another OnTime method to set the schedule argument equal to False. However, note that this method does not work if the time argument in the OnTime method is set with the NOW function.

To prepare the first OnTime method so that you can cancel it later, create a time variable and use this variable in both cases, as in the following example:

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners -

Microsoft Advisory Services -

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:;EN-US;CNTACTMS
  1. Type or paste the following macro code into a module sheet in a new workbook:
    Dim TimeNow As DoubleDim TimeStop As DoubleDim TimeSet As DoubleSub RunMeFirst()   TimeNow = Now   TimeStop = TimeNow + TimeValue("00:00:59") 'this sets the time at                                              'which all macros will                                              'stop   TimeSet = TimeNow + TimeValue("00:00:15")   Set_OnTimeEnd SubSub Set_OnTime()  Application.OnTime TimeSet, "TestMacro"   If TimeSet > TimeStop Then  'compare the time to stop with the time                               'at which the TestMacro is scheduled to                               'run     Application.OnTime TimeSet, "TestMacro", , False   End IfEnd SubSub TestMacro()   ActiveCell.Value = 1   ActiveCell.Offset(1, 0).Select   TimeSet = TimeSet + TimeValue("00:00:15")   Set_OnTimeEnd Sub					
  2. Activate Sheet1 in your workbook.
  3. Run the RunMeFirst macro.
The TimeStop variable is set to 59 seconds after you run the RunMeFirst macro, and the TimeSet variable is set to 15 seconds after you run the RunMeFirst macro. Then, the Set_OnTime macro is called, which in-turn checks to see if the TimeSet variable is greater than the TimeStop variable. If it is, all macros stop. If it is not, the TestMacro is called. The TestMacro places a value of 1 in the active cell, selects the next cell down from the active cell, and then resets the TimeSet variable to 15 seconds past its current value. The TestMacro then calls the Set_OnTime macro.
More information
To cancel the first OnTime method, the second OnTime method must have the same serial number for the time argument as the first OnTime method. When you use a NOW function for the earliestTime argument, such as
Now + TimeValue("00:04:00")
Microsoft Excel uses the serial value of Now + TimeValue("00:04:00"). Whenthe second OnTime method is issued, the serial value of NOW will have changed. Because the NOW function is volatile, you cannot exactly duplicate the earliestTime argument in the second OnTime method.
For more information about the OnTime function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type ontime method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Article ID: 213452 - Last Review: 10/25/2013 23:42:00 - Revision: 1.0

Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbprb kbprogramming KB213452