CODEWITHSUNDEEP
excelvbavbe
Karan Talwar
Karan Talwar

Reputation: 3

Pause/Resume code execution in VBA using a button on a userform

I have Three buttons on a User form Run, Pause, Reset. Reset Button ends the code execution, Pause button Breaks/pause the code execution using STOP Command, Run button should restart the execution. I am having trouble resuming the code execution paused by the STOP command.

Stop/End Code: Application.VBE.CommandBars.FindControl(ID:=228).Execute

Pause/Break Code: Stop

Resume Code: Application.VBE.CommandBars.FindControl(ID:=186).Execute

Please let me know how can I resume the execution if a code is paused using 'Stop' command. Also, Will Sleep command let me resume a code?

Upvotes: 0

Views: 551

Answers (1)

Gove
Gove

Reputation: 1814

When you execute STOP, it puts your application in "break mode."I don't think it is possible to execute ANY VBA code while in break mode. When I need to interrupt some long-running code, put several "DoEvents" statements in the long-running code, or at the right place in a loop. DoEvents allows running VBA code to check to see if there are other events that need to be done and allows them to process. So if the long-running code encounters a DoEvents statement, and another button has been clicked, it will allow the code on the clicked button run. By setting up variables that get changed by your "pause" and "reset" buttons, you can get your main code to behave just as you like.

Here's a form with code behind the "Run" button to check each cell (one by one) to look for "Stackoverflow". Because a worksheet as more than 17 billion cells, it will take a long time to search them one by one.

example userform

Here's the code behind the form. The object names should be self-explanatory.

Private paused As Boolean
Private resetting As Boolean
Private running As Boolean

Private Sub cmdPauseResume_Click()
  If paused Then
    cmdPauseResume.Caption = "Pause"
    paused = False
  Else
    cmdPauseResume.Caption = "Resume"
    paused = True
  End If

End Sub

Private Sub cmdReset_Click()
  resetting = True
End Sub

Private Sub cmdRun_Click()
  Dim row As Long
  Dim col As Long
  
  If running Then Exit Sub ' no need to run
  
  running = True
  
  ' nested loop to seach each cell, one by one.  Just doing this to make long-running code
  For row = 1 To 1048576
    col = 1
    Do Until col > 16384
        If resetting Then
          lblStatus.Caption = ""
          paused = False
          Exit For
        ElseIf paused Then
          Application.Wait DateAdd("s", 1, Now)
          Debug.Print row, col
        Else
            lblStatus.Caption = "Now searching cell " & Cells(row, col).Address(False, False)
             If Cells(row, col).Value = "Stackoverflow" Then
               lblStatus.Caption = "Stackoverflow found in cell " & Cells(row, col).Address(False, False)
               Exit For
             End If
             col = col + 1
         End If
         DoEvents
    Loop
  Next
  
  running = False
  paused = False
  resetting = False
  cmdPauseResume.Caption = "Pause"
End Sub

The "Application.wait" method only has precision down to whole seconds, so we are waiting for a whole second at a time to process the next action while paused. You can implement a "sleep" statement with millisecond precision, but it is complex, being different for Windows vs Mac.

Upvotes: 1

Related Questions