Alex Rivera | Logout

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

Asked 2011-01-05T00:20:53.860
11

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?

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

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?

excel vba ontime
Edit
Report

1 Answer

1

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 save the SendTime is written to the array. This serves to keep track of all our scheduled times.

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.

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
answered 2011-01-10T19:27:22.340

Your Answer