Excel Application.OnTime causing glitchy behavior any alternative?

Question

After running Application.OnTime every 10 seconds eventually Excel will get extremely glitchy and force me to double click in a cell to force it to refresh among other seemingly random behavior. Is there any alternative needed to run a task often while still allowing the EXCEL GUI to remain responsive or reliable?

To test this I literally run a simple VBA function to modify a few cells on multiple computers and the issue eventually persists, in a few minutes or hours, or instantly if ran every 5 seconds.

Some pseudo code:

Foo(){
wait 10 seconds (non blocking)
Foo()
}

Please provide a minimal reproducible example which shows how, starting with a blank workbook, someone else can reproduce this problem. My guess is that there is something important about your situation that you haven't mentioned. Having said that, a cheap fix might be to include a DoEvents in the macro. — John Coleman
@JohnColeman I feel like I have asked many variations of this question before, but it is literally true... if you simply write to cells with Application.OnTime for a long enough period of time the GUI completely locks up... it's literally as simple as it can get, DoEvents doesn't even respond, I have tried minimizing and maximizing Excel programmatically etc etc, it is pretty crazy and appears to just be a glitch with Excel being unable to write back and forth between the script without locking up. The only solution is manually minimize maximize and double click in cell and that works 50%... — CodeCamper
@JohnColeman when I say you can do literally anything (edit cells within a 10 second interval etc) I meant it, but I will try to put together a minimal example for you. — CodeCamper

FaneDuru FaneDuru · Accepted Answer · 2019-12-15T13:20:23

You can create a Timer at the module level. A simple Timer, but taking care of all necessary variable setting. Otherwise, Excel can crush. You can also create a very stable Timer but it is a whole project behind such an idea. Anyhow, I can use such a Timer without problems in my projects. What is nice is the fact that once started the Timer works even in cell edit mode... Create a standard module (I all the time name it "Timer_module") with the next code:

Option Explicit

Declare PtrSafe Function SetTimer Lib "user32" (ByVal hWnd As LongPtr, _
            ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
Declare PtrSafe Function KillTimer Lib "user32" (ByVal hWnd As LongPtr, ByVal nIDEvent As LongPtr) As Long

Private TimerID As LongPtr
Private xTimer As Long, howMany As Long


Sub StartTimer(TimerSeconds As Long, howManyTimes As Long)
    howMany = howManyTimes
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000, AddressOf T_Pr)
End Sub
Sub StopTimer()
       On Error Resume Next
       KillTimer 0, TimerID
       xTimer = 0
End Sub
Sub T_Pr(ByVal hWnd As Long, ByVal uMsg As Long, _
            ByVal nIDEvent As Long, ByVal dwTimer As Long)
      xTimer = xTimer + 1:  Debug.Print xTimer, Now
      DoEvents
      If xTimer = howMany Then StopTimer: Debug.Print "Timer stopped: " & Now
End Sub

In another module the Timer can be simple called like in the next code:

Sub testTimeer()
  StartTimer 2, 4
End Sub

Were the first parameters means the number of seconds which must path until the timer function will do something. The code can be easily adapted to work for milliseconds. The second parameter means the number of timer function runs after the timer is stopped. It is just an example an it can be easily adapted in order to fulfill your need. So you can set it to act just once, you can call `StopTimer()' procedure whenever you need (from a loop, for instance) and so on...

P.S. Such a timer can be easily linked to a form. You have to take care of TimerID definition. It should be the form handle. For instance, in this way:

TimerID = GetActiveWindow

This piece of code must be part of the form initialize event and of course TimerID must be a Public declared variable... And you must take care that when the Timer runs all the rest of the running code to be very stable and have error handlers able to stop Timer in case of errors.

Excel Application.OnTime causing glitchy behavior any alternative?

1 Answers