CODEWITHSUNDEEP
vbaexceltimer
TheBear
TheBear

Reputation: 855

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

I have a workbook with many sheets, each sheet has varying number of equations that inter-link with each other. I have a workbook wide VBA timer that is supposed to run every 5 seconds.

When I am active on a sheet that doesn't have a lot of formulas on it, it seems to run exactly once every 5 seconds... However, if I then change the active sheet to a more "busy" sheet, the vba timer just doesn't go off... Or if it does go off it was like minutes later. It is not until I switch to a less busy sheet again and then the timer runs magically like normal without having to reset anything.

I do not have any special VBA code specifically on the busy sheets... and I can't see why the VBA timer code won't run consistently across all sheets? If the timer is to be affected, it should be affected across ALL sheets and not only certain ones.

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

Upvotes: 4

Views: 392

Answers (1)

Dobbes
Dobbes

Reputation: 134

The issue is Excel has a hard time running a macro and letting other processes run. Add this after you specify your wait condition:

DoEvents

Excel will release the resources and threading used for running the macro instead of churning on it the whole time the wait is going on. This should allow your spreadsheet to function normally between wait cycles.

Upvotes: 1

Related Questions