Pausing and resuming macros

The Problem

Saw this on Alan's site today in a comment to a tip called "Mouse Click Event in VBA"

While my macro is running a repetitive task in a loop, I hit ESC and get out of it, wherever it is. What I would like is a way to get out only after finishing the complete task (a montecarlo simulation) so that it can be resumed where it was interrupted. Just detecting a keypress or mouse click somewhere that the macro could test at the end of the loop would be great.

I am just a beginner at excel VBA macros, and have been so for ten years.

I love that last comment.  I've been doing this less that ten years and feel like I am learning like a newbie as every day passes.

First response (with edits, of course...)

I don't have a solution but I do have a thought or two.

I would try to implement a userform with a Pause/Resume toggle button. Clicking the button would change the state of the button (and the caption, of course). 

When the code is running and hits the suspend point(s) it checks the state of the button, then continues on or suspends work. 

You would also want to use the Click event handler for the button, checking the state change and resuming as appropriate.

Also, Alan has a hint or more on running macros in background mode. You might need to incorporate that information.

Second Response (with edits, of course...)

Looking at this a little further I found out I was on track.

Alan does have some tips that are helpful in understanding how to solve the problem.

• Running Macros in the Background
• Aborting a Macro and Retaining Control
• Pausing Macros for User Input
• Working while a Macro is Running

Actually, I built three ways of doing this.  The first two were based on the assumption that the macro would be run from a  userform.

Click the Start button,

•  The Start button goes dark 
• The Pause button, which is a toggle rather than a command button, is enabled along with the check box for stopping the process altogether

Click the enabled Pause button

• Processing stops, for now
• The Pause button turns into a "Resume" button
• The Stop check box is enabled

Click the Resume button

• The Resume button reverts to a Pause
• If the Stop check box is unchecked the process continues
• If the Stop check box is on (checked) then 
• Processing ends
• The Start button is enabled
• The Stop check box is now unchecked (Can't stop something that isn't running!)
• The  Pause button and Stop check box are both disabled.

The Code (Version 1)

Userform ufmStartPauseResumeStop01

Private Sub tgbPauseResume_AfterUpdate()

    If tgbPauseResume Then

        Me.cbxStop.Enabled = True

        Me.cbxStop = False

        tgbPauseResume.Caption = "Resume"

    Else

        tgbPauseResume.Caption = "Pause"

    End If

End Sub

Private Sub UserForm_Activate()

    Me.cbtStart.Enabled = True

    Me.tgbPauseResume.Enabled = False

    Me.cbxStop.Enabled = False

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'   Prevents use of the Close button if the control macro called by the Start button is running

    If Me.tgbPauseResume.Enabled And CloseMode = vbFormControlMenu Then

    ' An alternative to "Me.tgbPauseResume.visible" would be "Not Me.cbtStart.visible"

        MsgBox "Close button disabled during run."

        Cancel = True

    End If

End Sub

Module Mod_01_Enabled

Option Explicit

Sub StartPauseResumeStop_Enabled()

    ufmStartPauseResumeStop01.Show

End Sub

Private Sub ControlWithFormEvents_Enabled()
    '  This is the control module
    Dim cel As Range
    Set cel = ThisWorkbook.Sheets(1).Range("A1")
    cel.Parent.Activate
    cel.Select
    Do ' Loop runs until the Check Box for stopping the run is clicked on _
         See comments in UserForm object module
        cel.Value = AddAndDivide(cel)
        If ufmStartPauseResumeStop01.tgbPauseResume Then
            Do
                DoEvents
            Loop Until Not ufmStartPauseResumeStop01.tgbPauseResume
        End If
        DoEvents
    Loop Until ufmStartPauseResumeStop01.cbxStop
    ufmStartPauseResumeStop01.cbtStart.Enabled = True
    ufmStartPauseResumeStop01.tgbPauseResume.Enabled = False
    ufmStartPauseResumeStop01.cbxStop = False
    ufmStartPauseResumeStop01.cbxStop.Enabled = False
End Sub

Module Mod_CommonSub

Option Explicit

Function AddAndDivide(cel As Range)

    If Len(cel.Value) = 0 Then cel.Value = 0

    If IsNumeric(cel.Value) Then

        If CInt(Left(CStr(cel.Value), 1)) > 2 Then

            AddAndDivide = cel.Value / 2

        Else

            AddAndDivide = cel.Value + 1

        End If

    End If

End Function