Friday, October 10, 2014

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.


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