XL: Canceling OnTime Macro When Time Argument Is Volatile

ID: Q121212

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Macintosh, version 5.0
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

SYMPTOMS

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).

WORKAROUND

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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

In order to cancel an OnTime method, you must call another OnTime method to set the schedule argument equal to False. However, note that this method will not work if the time argument in the OnTime method is set with the Now function.

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

1. Enter the following macro code into a module sheet in a new workbook.

   Dim TimeNow As Double
   Dim TimeStop As Double
   Dim TimeSet As Double

   Sub 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_OnTime
   End Sub

   Sub 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 If
   End Sub

   Sub TestMacro()
      ActiveCell.Value = 1
      ActiveCell.Offset(1, 0).Select
      TimeSet = TimeSet + TimeValue("00:00:15")
      Set_OnTime
   End 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, then all macros stop. If it is not, then the TestMacro is called. The TestMacro will place a value of 1 in the active cell, select the next cell down from the active cell, and then reset the TimeSet variable to 15 seconds past its current value. The TestMacro then calls the Set_OnTime macro.

MORE INFORMATION

In order 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"). When the 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.

REFERENCES

For more information about the OnTime function, choose the Search button in Visual Basic Help and type:

   ontime

Additional query words: XL97 97 7.00 5.00

Keywords          : kbprg kbdta KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97
Platform          : WINDOWS

Last Reviewed: May 17, 1999