Problems with Application.OnTime in Excel's VBA

Question

I am trying to run a certain macro in very short intervals (every second, and, yes it works and does not freeze the system) from a certain time until a certain time. This means I need a starting point and a cutoff point. I cannot use the Workbook_Open() Event, since I already have other macros triggering at different times after the opening of the Workbook. From my experiments so far, any attempt I made ended up either running it from the moment i open the Workbook and with the appropriate schedule of once a second (which is bad since I need it to wait a bit before it starts) or at the time I want it to start but runs only once.

The basic line I use to trigger the Macro once a second is this:

Application.OnTime Now + TimeValue("00:00:01"), "Path to Macro"

To summarize:

I need something like the line above to start running 15 minutes after the Workbook is opened and stop 3 hours later.

Any help would be much appreciated.

Answer 1

What other timed macros are started from workbook_open, and why are these interfering? It sounds like you're limiting yourself unnecessarily. Here's how to address the issue:

Workbook_open should use application.ontime to call a general function do_timed_events. The do_timed_events function should re-add itself using application.ontime each time it is run. It should also keep track of state. For its first few runs, it should perform the other specific tasks, then wait 15m, then start performing the every-second task.

Here's some pseudocode:

private var do_timed_events_state as string
sub do_timed_events
   if do_timed_events_state = "" then
      do_task_1(arg1,arg2)
      do_timed_events_state = "task_2"
      Application.OnTime Now + TimeValue("00:00:01"), "do_timed_events"
   elseif do_timed_events_state = "task_2" then
      do_timed_events_state = "repeating_task"
      Application.OnTime Now + TimeValue("00:00:01"), "do_timed_events"
   elseif do_timed_events_state = "repeating_task" then
      Application.OnTime Now + TimeValue("00:00:01"), "do_timed_events"
   end if
end sub

You can probably come up with a better design than me on this one.

Answer 2

This is brilliant code. Would the following be possible:

Dim do_timed_events_state As String

Sub do_timed_events() If do_timed_events_state = "" Then 'code for task_1 do_timed_events_state = "task_2" Application.OnTime Now + TimeValue("00:02:00"), "do_timed_events" ElseIf do_timed_events_state = "task_2" Then 'code for task_2 do_timed_events_state = "task_3" Application.OnTime Now + TimeValue("00:02:00"), "do_timed_events" ElseIf do_timed_events_state = "task_3" Then 'code for task_3 do_timed_events_state = "task_4" Application.OnTime Now + TimeValue("00:02:00"), "do_timed_events" ElseIf do_timed_events_state = "task_4" Then 'code for task_4 do_timed_events_state = "task_5" Application.OnTime Now + TimeValue("00:02:00"), "do_timed_events" ElseIf do_timed_events_state = "task_5" Then 'code for task_5 do_timed_events_state = "" Application.OnTime Now + TimeValue("00:02:00"), "do_timed_events" End If End Subenter code here