vba - Application.OnTime 未执行

Question

我正在尝试取消 ThisWorkbook 模块的 Workbook_Close 例程中的计时器。谁能解释以下行为？:

手动关闭工作簿
Application.OnTime 按预期运行并取消计时器。
如果我多次尝试杀死同一个计时器，或者一个不存在的计时器，我会收到一个错误

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

对我来说，这是支持该功能正常工作的证据。

使用 ThisWorkbook 关闭工作簿。关闭
计时器没有被杀死，事实证明:

计时器到期时工作簿重新打开

如果同一个计时器被杀死多次，VBA 不会抛出任何错误

VBA 在尝试终止不存在的计时器时不会抛出错误

更多背景

Application.Run 在这两种情况下都会按预期触发。对我来说，这表明应用程序对象仍在加载，并且 VBA 运行时仍在正常运行。

测试码

在名为 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

在本工作簿中

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

跟踪手动关闭(按预期抛出错误)...

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

通过运行 closeWorkbook 跟踪以 .Close 关闭(应该在 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

Answer 1

我做了一些修改。出于测试目的，我们只需要一个 msgbox 来通知例程是否已正确运行/停止。

在标准模块中:

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

在 ThisWorkbook 模块中:

Option Explicit

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

Private Sub Workbook_Open()
    Run "TimerStart"
End Sub

这对我有用。以下场景已经过测试:

手动运行“TimerSet”> 等待几个 msgboxes > 手动运行“TimerKill”。

手动运行“TimerSet”> 等待几个 msgboxes > 关闭工作簿。

打开工作簿 > 等待几个 msgboxes > 关闭工作簿。

打开工作簿 > 等待几个 msgboxes > 使用 CloseWB 宏关闭工作簿。

我发现的是:

从 Workbook_BeforeClose 运行“TimerKill”不工作 .工作簿重新打开。

从 Workbook_BeforeClose 运行“CloseWB”作品 .

对不起，我无法解释 为什么这是。但是，它现在应该对您有用。