XL: Canceling OnTime Macro When Time Argument Is Volatile

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

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:

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

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.

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

REFERENCES

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

   ontime