VBA宏定时暂停续行问题：无需重启或拆分宏的实现方案

Great question—you can absolutely pull this off in a single macro without splitting your core logic or restarting the procedure. The trick is to use a module-level flag variable combined with OnTime to trigger a tiny helper sub (just a one-liner, not breaking up your workflow) and a responsive waiting loop in your main macro.

Here's a complete, working example:

First, declare a module-level boolean variable at the top of your VBA module (outside any subroutine):

Private ResumeExecution As Boolean

Then your main macro:

Sub AutoPauseAndContinue()
    Dim resumeAt As Date
    
    ' --------------------------
    ' Your code BEFORE the pause
    ' --------------------------
    Debug.Print "Running first section at " & Now()
    MsgBox "Macro starting—will pause in 5 seconds, then resume automatically."
    
    ' Set when you want to resume (adjust this to your desired delay)
    resumeAt = Now + TimeValue("00:00:05")
    
    ' Schedule a tiny helper sub to flip our resume flag at the target time
    Application.OnTime EarliestTime:=resumeAt, _
                      Procedure:="TriggerResume", _
                      Schedule:=True
    
    ' Wait for the flag to be set, keeping Excel responsive
    ResumeExecution = False
    Do While Not ResumeExecution
        DoEvents ' Lets Excel handle clicks, edits, etc. while waiting
    Loop
    
    ' --------------------------
    ' Your code AFTER the pause
    ' --------------------------
    Debug.Print "Resuming execution at " & Now()
    MsgBox "Macro has resumed! Continuing with the rest of your logic."
    
    ' Cleanup: Cancel any pending OnTime event (prevents accidental triggers later)
    On Error Resume Next
    Application.OnTime EarliestTime:=resumeAt, _
                      Procedure:="TriggerResume", _
                      Schedule:=False
    On Error GoTo 0
End Sub

' Tiny helper sub—only exists to set the resume flag (no core logic here)
Private Sub TriggerResume()
    ResumeExecution = True
End Sub

How this works: #

1. Module-level flag: ResumeExecution acts as a signal for your main macro to know when to continue.
2. OnTime scheduling: We tell Excel to run the TriggerResume sub at your desired resume time—this sub does nothing but flip the flag to True.
3. Responsive wait loop: The main macro enters a loop that checks the flag repeatedly, using DoEvents to keep Excel usable during the pause (no frozen screen!).
4. Cleanup: The final step cancels any pending OnTime event in case you stop the macro early, which avoids unexpected triggers later.

Key points to note: #

• Adjust resumeAt to your needs (e.g., TimeValue("00:01:30") for a 90-second pause).
• The helper sub is minimal—your entire core workflow stays in the main AutoPauseAndContinue macro, so you're not splitting your logic into separate modules.
• If you want to manually resume early, you can add a button that runs ResumeExecution = True to flip the flag immediately.

内容的提问来源于stack exchange，提问作者Marco Di Bartolo