VBA: How to troubleshoot advanced patterns when using Application.OnTime for recurring macro execution?
I've spent hours debugging this and I'm having a hard time understanding I'm trying to schedule a macro to run every minute using `Application.OnTime`, but I'm working with some unexpected behavior... The macro seems to run multiple times, which is not what I intended. Here’s the code snippet I’m using: ```vba Dim NextRun As Date Sub ScheduleMacro() NextRun = Now + TimeValue("00:01:00") Application.OnTime NextRun, "MyMacro" End Sub Sub MyMacro() ' Your code here Debug.Print "Macro executed at: " & Now ' Reschedule the next run ScheduleMacro End Sub ``` Initially, I thought the `ScheduleMacro` subroutine would only be called once, but it seems that if the macro takes longer than a minute to execute, it schedules another instance, leading to multiple instances running together. I tried adding a global flag to prevent re-entrance, like this: ```vba Dim isRunning As Boolean Sub MyMacro() If isRunning Then Exit Sub isRunning = True ' Your code here Debug.Print "Macro executed at: " & Now isRunning = False ScheduleMacro End Sub ``` However, this solution doesn’t seem to completely eliminate the scenario, especially if the macro fails or is interrupted. I’m using Excel 2019 and I’m wondering how to ensure that only one instance of the macro runs at a time while still allowing it to schedule itself for the next run. Any insights or best practices would be greatly appreciated! My development environment is Windows 10. This is for a web app running on macOS. Is there a better approach?