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

How can I check for or cancel MULTIPLE pending application.ontime events in excel VBA?

提问by goofology

I'm using the Application.Ontime event to pull a time field from a cell, and schedule a subroutine to run at that time. My Application.Ontime event runs on the Workbook_BeforeSave event. As such, if a user (changes the desired time + saves the workbook)multiple times, multiple Application.Ontime events are created. Theoretically I could keep track of each event with a unique time variable.. but is there a way to check/parse/cancel pending events?

我正在使用 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:
change B9 to 12:01, Save the workbook
change B9 to 12:03, Save the workbook
change B9 to 12:05, Save the workbook
change B9 to 12:07, Save the workbook
etc

因此，如果我：
将 B9 更改为 12:01，将工作簿
更改 B9 保存为 12:03，将工作簿
更改 B9 保存为 12:05，将工作簿
更改 B9 保存为 12:07，保存工作簿
等

I end up with multiple events firing. I only want ONE event to fire (the most recently scheduled one)

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

How can I cancel ALL pending events (or enumerate them at least) on the Workbook_BeforeClose event?

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

回答by Nick Spreitzer

I don't think you can iterate through all pending events or cancel them all in one shabang. I'd suggest setting a module level or global boolean indicating whether or not to fire your event. So you'd end up with something like this:

我认为您无法遍历所有未决事件或一次性取消所有事件。我建议设置一个模块级别或全局布尔值，指示是否触发您的事件。所以你最终会得到这样的结果：

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

'fire event here

End Sub

Edit:

编辑：

Add this to the TOP of the sheet module which contains the range which contains the date/time value you're after:

将此添加到工作表模块的顶部，其中包含包含您所追求的日期/时间值的范围：

' 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

And this to a standard module:

这是一个标准模块：

Sub SendEmail()

    'add your proc here

End Sub

回答by chris neilsen

Each time you call Application.Ontime save the time the event is set to run (you could save it on a sheet or in a module scoped dynamic array)

每次调用 Application.Ontime 都会保存事件设置为运行的时间（您可以将其保存在工作表或模块范围的动态数组中）

Each time your event fires, remove the corresponding saved time

每次您的事件触发时，删除相应的保存时间

To cancel all pending event iterate through the remaining saved times calling Application.Ontime with schedule = false

要取消所有挂起的事件，请遍历剩余的保存时间，使用 schedule = false 调用 Application.Ontime

回答by Alex P

I think I may have a solution that works, based on some of the advice already given.

我想我可能有一个可行的解决方案，基于一些已经给出的建议。

In short, we create a global array and each time the user hits savethe SendTime is written to the array. This serves to keep track of all our scheduled times.

简而言之，我们创建了一个全局数组，每次用户点击saveSendTime 时都会将其写入数组。这用于跟踪我们所有的预定时间。

When the workbook is closed, we loop through the array and delete all scheduled times.

当工作簿关闭时，我们遍历数组并删除所有计划时间。

I tested this and it seemed to work on Excel 2003. Let me know how you get on.

我对此进行了测试，它似乎可以在 Excel 2003 上运行。请告诉我您的进展情况。

Dim scheduleArray() As String //Set as global array to hold times

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

Private Sub Workbook_BeforeClose
    On Error Resume Next
    Dim iArr As Integer, startTime As String

    For iArr = 0 To UBound(scheduleArray) - 1 //Loop through array and delete any existing scheduled actions 
        startTime = scheduleArray(iArr)
        Application.OnTime TimeValue(startTime), "SendEmail", , False
    Next iArr
End Sub

Sub AddToScheduleArray(startTime As String)
    Dim arrLength As Integer

    If Len(Join(scheduleArray)) < 1 Then
        arrLength = 0
    Else
        arrLength = UBound(scheduleArray)
    End If

    ReDim Preserve scheduleArray(arrLength + 1) //Resize array
    scheduleArray(arrLength) = startTime //Add start time
End Sub

回答by steve

or you can just create some cell (like abacus), for example:

或者您可以创建一些单元格（如算盘），例如：

if I use application.ontime:

如果我使用 application.ontime：

if range("V1") < 1 then

    Application.OnTime dTime, "MyMacro"

    range("V1")=range("V1") + 1

 end if

if I want to stop counting...

如果我想停止计数...

   Application.OnTime dTime, "MyMacro", , False

   range("V1")=range("V1") - 1