Application.OnTime not executed

Question

I am trying to cancel a timer in the Workbook_Close routine in the ThisWorkbook module. Can anyone explain the following behaviour?:

Closing the workbook manually Application.OnTime functions as expected and cancels the timer. If I try to kill the same timer more than once, or a non-existent timer, I get an error

ERROR: 1004: Method 'OnTime' of object '_Application' failed

To me, this is supporting evidence that the function is working properly.

Closing the Workbook using ThisWorkbook.Close The timer is not killed as evidenced by the facts that:

The workbook re-opens when the timer expires
VBA does not throw any error if the same timer is killed more than once
VBA does not throw an error on attempting to kill a non-existent timer

Further Context

Application.Run fires as expected in both cases. To me this indicates that the Application Object is still loaded and the VBA Runtime is still functioning properly.

Test Code

In a standard Module called minUnit

Private Sub testCallBack(name As String, nextTime As String)
  MsgBox "callback " & name & " " & nextTime
End Sub

Public Function sProcedure(callBackProcedure As String, mName As String, nextTime As Date) As String
' Constructs a properly formatted string to feed to OnTime for a call back with two parameters
  sProcedure = "'" & callBackProcedure & " " & """" & mName & """," & """" & fmtTime(nextTime) & """'"
End Function

Private Sub testTimerSet()
  gnextTime = Now() + TimeSerial(1, 0, 0)
  Application.OnTime gnextTime, sProcedure("Globals.testCallBack", _
                                            "testTimer", gnextTime)
End Sub

Public Sub testTimerKill()
  On Error Resume Next
  Application.OnTime gnextTime, sProcedure("Globals.testCallBack", _
                                           "testTimer", gnextTime), _
                                          , False
End Sub

In ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

  Globals.testTimerKill
  Globals.testTimerKill
  Globals.testTimerKill

  On Error Resume Next

  Application.OnTime 0, "Nothing", , False

  Application.Run sProcedure("minUnit.testCallBack", "Application.Run", Now())

  Application.OnTime Now(), sProcedure("minUnit.testCallBack", "Application.OnTime Now()", Now()), , True

End Sub

Sub closeWorkbook()
    ThisWorkbook.Close
End Sub

Trace for manual closing (errors thrown as expected)...

20:27:07:206 minUnit.testTimerSet:  START
20:27:07:209  Application.OnTime 'minUnit.testCallBack "testTimer","21:27:07"'                     :0.003532
20:27:07:212 minUnit.testTimerSet:  END                                                            :0.006447

20:27:13:618 minUnit.testTimerKill:  START
20:27:13:621 minUnit.testTimerKill:  END                                                           :0.003337

20:27:21:240 minUnit.testTimerSet:  START
20:27:21:244  Application.OnTime 'minUnit.testCallBack "testTimer","21:27:21"'                     :0.004301
20:27:21:246 minUnit.testTimerSet:  END                                                            :0.006274

20:27:33:946 ThisWorkbook.Workbook_BeforeClose:  START
20:27:33:949  minUnit.testTimerKill:  START
20:27:33:951  minUnit.testTimerKill:  END                                                          :0.001921
20:27:33:953  minUnit.testTimerKill:  START
20:27:33:957  minUnit.testTimerKill:  END
20:27:33:957**ERROR: 1004: Method 'OnTime' of object '_Application' failed                         :0.002433
20:27:33:963  minUnit.testTimerKill:  START
20:27:33:967  minUnit.testTimerKill:  END
20:27:33:967**ERROR: 1004: Method 'OnTime' of object '_Application' failed                         :0.002230
20:27:33:972  Application.OnTime 0, "Nothing", , False
20:27:33:972**ERROR: 1004: Method 'OnTime' of object '_Application' failed                         :0.024134
20:27:33:977  Application.Run 'minUnit.testCallBack "Application.Run","20:27:33"'                  :0.031184
20:27:33:983  minUnit.testCallBack:  START
20:27:35:995  minUnit.testCallBack:  END                                                           :2.012402
20:27:35:997  Application.OnTime Now() 'minUnit.testCallBack "Application.OnTime Now()","20:27:35"':2.051651
20:27:35:999 ThisWorkbook.Workbook_BeforeClose:  END                                               :2.053604

Trace for closing with .Close by running closeWorkbook (should have thrown the first error at 20:30:11:979)...

20:29:48:201 minUnit.testTimerSet:  START
20:29:48:204  Application.OnTime 'minUnit.testCallBack "testTimer","21:29:48"'                     :0.003342
20:29:48:206 minUnit.testTimerSet:  END                                                            :0.005207

20:29:51:942 minUnit.testTimerKill:  START
20:29:51:945 minUnit.testTimerKill:  END                                                           :0.002946

20:29:55:444 minUnit.testTimerSet:  START
20:29:55:448  Application.OnTime 'minUnit.testCallBack "testTimer","21:29:55"'                     :0.003535
20:29:55:450 minUnit.testTimerSet:  END                                                            :0.005446

20:30:11:966 ThisWorkbook.closeWorkbook:  START
20:30:11:971  ThisWorkbook.Workbook_BeforeClose:  START
20:30:11:973   minUnit.testTimerKill:  START
20:30:11:975   minUnit.testTimerKill:  END                                                         :0.001994
20:30:11:979   minUnit.testTimerKill:  START
20:30:11:981   minUnit.testTimerKill:  END                                                         :0.001847
20:30:11:983   minUnit.testTimerKill:  START
20:30:11:986   minUnit.testTimerKill:  END                                                         :0.002271
20:30:11:988   Application.OnTime 0, "Nothing", , False                                            :0.016905
20:30:11:991   Application.Run 'minUnit.testCallBack "Application.Run","20:30:11"'                 :0.019140
20:30:11:996   minUnit.testCallBack:  START
20:30:13:976   minUnit.testCallBack:  END                                                          :1.979131
20:30:13:977   Application.OnTime Now() 'minUnit.testCallBack "Application.OnTime Now()","20:30:13"':2.005963
20:30:13:985  ThisWorkbook.Workbook_BeforeClose:  END                                              :2.013265

TAKL · Answer 1 · 2014-11-17T06:18:11.027

0

I have made some modifications. For testing purposes we only need a msgbox to notice whether the routine has been run/stopped correctly.

In standard module:

Option Explicit
Public dTime As Date ' Needs to be a public/global variable

Public Sub TimerStart()
    dTime = Now() + TimeSerial(0, 0, 5)
    Application.OnTime dTime, "TimerStart"

    MsgBox "Callback " & TimeValue(dTime)
End Sub

Public Sub TimerKill()
    On Error Resume Next
    Application.OnTime dTime, "TimerStart", , False
End Sub

Public Sub CloseWB()
    TimerKill
    ThisWorkbook.Close SaveChanges:=True
End Sub

In ThisWorkbook module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Run "CloseWB"
End Sub

Private Sub Workbook_Open()
    Run "TimerStart"
End Sub

This works for me. Following scenarios have been tested:

Run "TimerSet" manually > Wait for a few msgboxes > Run "TimerKill" manually.
Run "TimerSet" manually > Wait for a few msgboxes > Close workbook.
Open workbook > Wait for a few msgboxes > Close workbook.
Open workbook > Wait for a few msgboxes > Close workbook using the CloseWB macro.

What I've found is:

Running "TimerKill" from Workbook_BeforeClose does not work. The workbook reopens.
Running "CloseWB" from Workbook_BeforeClose works.

I'm sorry I can't explain why this is. However, it should work for you now.

edited Nov 17 '14 at 06:18

answered Nov 14 '14 at 06:25

TAKL

339
4
11

testTimerKill was indeed faulty paste... thanks for pointing that out, now corrected. I'll cross-check the rest of your analysis and report back. – Cool Blue Nov 14 '14 at 11:05
Your answer doesn't cover the failure mode which is when the workbook is closed using code `ThisWorkbook.Close` – Cool Blue Nov 14 '14 at 11:09
@CoolBlue I have updated my answer, including a solution for your request. Please try it and report back. Somehow, killing the timer before running the timer again, solves this problem. Kind of funky, since the timer has already passed, however it was the only way the timer would stop from the CloseWB macro. – TAKL Nov 14 '14 at 12:23
Tack sa mycket for having a stab at this but when I ran [your code](https://app.box.com/s/5q4cwyay3jnukitz68da) I got the same result: after running closeWB workbook re-opens on next timer. I'm running Office 2013 on Windows 7, same result for x32 and x64. – Cool Blue Nov 14 '14 at 13:53
Also tested with Office 2010 on Windows XP: same result as per my original question. – Cool Blue Nov 14 '14 at 14:20
Updated answer. Man, that OnTime can be a pain in the. Seems like it doesn't work to run the TimerKill from Workbook_BeforeClose event. It needs to run a separate macro that runs TimerKill instead. Perhaps it has something to do with the modules (ThisWorkbook being a private one). Anyways, hope it works now. I'm running Office 2010 btw. – TAKL Nov 17 '14 at 06:21
OK, thanks for confirming. Your answer is very neat. Conclusion is: Application.OnTime is completely ignored after the Workbook_BeforeClose event handler has been entered. My question is: Why? And we just don't know... Microsoft? – Cool Blue Nov 17 '14 at 13:47
Although I cannot answer WHY it happens, I did provide a solution to your problem. I've upped this question though, hoping someone else can explain the weird behaviour of Workbook_BeforeClose. – TAKL Nov 21 '14 at 06:38

Application.OnTime not executed

1 Answers1

Linked