PDA

View Full Version : Solved: Pause/resume execution



kroz
11-04-2010, 12:12 AM
Hey guys,

I have a new question for you. One of my users has brought a situation to my attention. While running one of my macros he wants to pause execution so that he/she can fix some errors within the data.
What that means to me is that i have to put my macro on pause.

My idea so far is to put a YesNo button that will Exit Sub on vbYes, and then place a continue button inside the worksheet with "continue" on it.
The only problem is that if i want to go with this i lose all the info in my variables - this approach will involve splitting my macro into two separate pieces of code.

Is there any other way of doing this? I'm thinking somewhere along the lines of a Pause/Resume button or maybe using a goto Label so that i would not reset my variables.

Thank you.

joms
11-04-2010, 12:55 AM
hi kroz, check out this link: http://p2p.wrox.com/excel-vba/35055-excel-macro-application-pause.html

the code for that link is:



Sub Wait()
' Waits 5 seconds
MsgBox "i wait"
Application.Wait Time + TimeSerial(0, 0, 5)
' Continues here after pause
MsgBox "for 5 secs"
End Sub

mdmackillop
11-04-2010, 01:34 AM
Assign a button to DoPause to toggle the break. Note that this needs refinement to prevent Wait looping endlessly if you pause execution.

Option Explicit

Dim Pause As Boolean

Sub TestPause()
Dim i As Long
For i = 1 To 10000
Cells(i, 1) = i
Cells(i, 1).Select
DoEvents
If Pause Then Wait
Next

End Sub

Sub Wait()
If Pause Then
Do
DoEvents
Loop Until Not Pause
End If
End Sub

Sub DoPause()
Pause = Not Pause
End Sub

kroz
11-04-2010, 01:47 AM
I finally decided that it's not worth bothering with WAIT and went for the old classic move.
I've declared all my variables as Module variables and i've split my macro in two.

Thanx for the input Joms and mdmackillop