Excel VBA timer performance differs depending on which sheet is active?

Question

I have a book with many sheets, each sheet has a different number of equations that are interconnected with each other. I have a large VBA timer with a workbook that should start every 5 seconds.

When I am active on a sheet that does not have many formulas, it works exactly once every 5 seconds ... However, if I then change the active sheet to a more "busy" sheet, the vba timer just does not disappear ... Or, if he leaves, it was like a few minutes later. Until I switch to a less loaded sheet, and then the timer works magically, as usual, without reset.

I don’t have a special VBA code specifically for busy sheets ... and I don’t understand why the VBA timer code will not work sequentially on all sheets? If the timer should be affected, it should be affected on all sheets, not just some.

Here is the VBA timer code:

Sub TimerTick()

On Error GoTo ErrorHandler
    If toggletimer = True Then
        RunMyCode
        runWhen_ES = Now() + TimeValue("00:00:05")
        Application.OnTime EarliestTime:=runWhen_ES, Procedure:="TimerTick", Schedule:=True
    End If
ErrorHandler:

End Sub

Answer 1

The problem is that Excel has difficulty running a macro and allows you to run other processes. Add this after specifying the wait condition:

DoEvents

Excel will free the resources and threads used to run the macro, instead of foaming it all the time. This should allow your spreadsheet to function normally between wait cycles.