XL: Canceling OnTime Macro When Time Argument Is Volatile (121212)

WORKAROUND

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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.

   Now + TimeValue("00:04:00")