Application.OnTime未执行

Question

我正在尝试取消ThisWorkbook模块中的ThisWorkbook例程中的计时器。有人能解释一下以下行为吗？

按预期手动关闭工作簿 Application.OnTime函数，并取消计时器。如果我试图杀死同一个计时器不止一次，或一个不存在的计时器，我会得到一个错误。

错误: 1004:对象'OnTime‘的方法'_Application’失败

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

使用ThisWorkbook.Close关闭工作簿计时器没有被杀死，事实证明如下：

1. 计时器过期时，工作簿将重新打开。
2. 如果同一个计时器不止一次被杀死，VBA不会抛出任何错误。
3. 当试图杀死不存在的计时器时，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

在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

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

minUnit.testTimerSet: START 20:27:07:209 Application.OnTime 'minUnit.testCallBack "testTimer"，“21:27:07”:0.003532 20:27:07:212 minUnit.testTimerSet:结束: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:结束: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**错误: 1004:方法'OnTime‘of object '_Application’失败:0.002433 20:27:33:963 minUnit.testTimerKill: START 20:27:33:967 minUnit.testTimerKill: END 20:27：33:967**错误: 1004:对象'OnTime‘的方法'_Application’失败:0.002230 20:27:33:972 Application.OnTime 0，“无”，虚假20:27:33:972**错误: 1004:方法'OnTime‘of object '_Application’失败: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:结束: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:结束:2.053604

通过运行.Close跟踪closeWorkbook (应该在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:结束: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:结束: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:975minUnit.testTimerKill:结束:0.001994 20:30:11:979 minUnit.testTimerKill: START 20:30:11:981 minUnit.testTimerKill:结束:0.001847 20:30:11:983 minUnit.testTimerKill: START 20:30:11:986minUnit.testTimerKill:结束:0.002271 20:30:11:988 Application.OnTime 0，“没事”，假的: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:结束: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:结束: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 >关闭工作簿。
• 使用CloseWB宏打开工作簿>等待几个msgboxes >关闭工作簿。

我发现的是：

• 从Workbook_BeforeClose 运行“Workbook_BeforeClose”不工作。工作簿重新打开。
• 从Workbook_BeforeClose works运行“”。

对不起，我无法解释为什么是这样的。不过，现在应该对你有用了。