如何在 Excel VBA 中检查或取消多个待处理的 application.ontime 事件？

我正在使用 Application.Ontime 事件从单元格中提取时间字段，并安排一个子例程在那时运行。我的 Application.Ontime 事件在 Workbook_BeforeSave 事件上运行。因此，如果用户（更改所需时间+保存工作簿）多次创建多个 Application.Ontime 事件。理论上，我可以使用唯一的时间变量来跟踪每个事件。但是有没有办法检查/解析/取消待处理的事件？

Private Sub Workbook_BeforeSave
    SendTime = Sheets("Email").Range("B9")
    Application.OnTime SendTime, "SendEmail"
End Sub

Private Sub Workbook_BeforeClose
    Application.OnTime SendTime, "SendEmail", , False
End Sub

So if I:
将B9更改为12:01，保存工作簿
将B9更改为12:03，保存工作簿
将B9更改为12:05，保存工作簿
将B9更改为12:07，保存工作簿
etc

我最终触发了多个事件。我只想触发一个事件（最近安排的事件）

如何取消 Workbook_BeforeClose 事件上的所有待处理事件（或至少枚举它们）？

我不认为你可以迭代所有待处理的事件或一次性取消它们。我建议设置一个模块级别或全局布尔值来指示是否触发您的事件。所以你最终会得到这样的结果：

Dim m_AllowSendMailEvent As Boolean
Sub SendMail()
If Not m_AllowSendMailEvent Then Exit Sub

'fire event here

End Sub

Edit:

将其添加到工作表模块的顶部，该模块包含包含您要查找的日期/时间值的范围：

' Most recently scheduled OnTime event. (Module level variable.)
Dim PendingEventDate As Date

' Indicates whether an event has been set. (Module level variable.)
Dim EventSet As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SendTimeRange As Range

' Change to your range.
Set SendTimeRange = Me.Range("B9")

' If the range that was changed is the same as that which holds
' your date/time field, schedule an OnTime event.
If Target = SendTimeRange Then

    ' If an event has previously been set AND that time has not yet been
    ' reached, cancel it. (OnTime will fail if the EarliestTime parameter has
    ' already elapsed.)
    If EventSet And Now > PendingEventDate Then

        ' Cancel the event.
        Application.OnTime PendingEventDate, "SendEmail", , False

    End If

    ' Store the new scheduled OnTime event.
    PendingEventDate = SendTimeRange.Value

    ' Set the new event.
    Application.OnTime PendingEventDate, "SendEmail"

    ' Indicate that an event has been set.
    EventSet = True

End If

End Sub

这是一个标准模块：

Sub SendEmail()

    'add your proc here

End Sub