CODEWITHSUNDEEP
excelvba
CodeCamper
CodeCamper

Reputation: 6984

Excel Application.OnTime causing glitchy behavior any alternative?

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()
}

Upvotes: 0

Views: 423

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

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.

Upvotes: 2

Related Questions