Solved

How do I pause a running program in VBA

Posted on 2010-08-22
16
703 Views
Last Modified: 2012-05-10
In an Excel worksheet, say I have a column called program counter and another column has the function that it should call. I have created a userform as illustrated in the image. Enter the program counter which you want your program to run up to. For example, if you enter program counter as 20 and click Run, the program runs from 1 till 20. However, if it was in the middle of running and you click on 'Pause', the program should stop running. How can I do this? How do I resume upon pressing t he resume button? Any help would be  much appreciated. Thanks.
userform.JPG
0
Comment
Question by:teeling
16 Comments
 
LVL 17
ID: 33497419
Put a call to DoEvents in your loop
 
for f = 1 to 20
DoEvents
increase counter
Next
0
 

Author Comment

by:teeling
ID: 33497431
I have done that. What I need to know is the pausing part.
0
 
LVL 17
ID: 33497445
I missed a bit, the DoEvents will allow Windows to detect that you have pressed the pause button., Otherwise the lop will need to complete before the button code will be called.
There are a number of ways to have the buttons work. A simple way is to have a boolean variable called Run. Set this to true when you pres the start button and check it inside the loop. If the pause button is pressed then set that variable to false. Continue sets it back true again.
 

for f = 1 to 20
DoEvents
if Run=True then
increase counter
end if
Next
0
 
LVL 17
ID: 33497454
Actually that's nasty, I apologise.
Better with a While loop
 
Do While Run = True
Do Events
increase counter
Loop
 
 
Sub Pause
Run = False
End Sub
 
Sub Continue
Run = True
End Sub
0
 

Author Comment

by:teeling
ID: 33497590
Can't seem to get it to pause. Can you check my codes? Thanks.
Private Sub cmdLogin_Click()
    RunStatus = True
    
    Do While RunStatus = True
    DoEvents

        progctrfinal = TextBox1.Value
        For progctr = 1 To progctrfinal Step 1
            Call optionStatus(progctr)
            If progstuck Then Exit Sub
        Next progctr

    Loop
End Sub

Private Sub Pause_Click()
    RunStatus = False
End Sub

Private Sub Resume_Click()
    RunStatus = True
End Sub

Open in new window

0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 33497726
To "pause" you have to store the LAST processed value for "progctr" in another variable (outside the subs or make it static) and then START the loop back up from the next value in the loop so it appears to "resume" from where it left off.

    For progctr = lastValue To progctrfinal Step 1
0
 

Author Comment

by:teeling
ID: 33498097
Can you perhaps show me by inserting them in the codes? Thanks.
0
 

Author Comment

by:teeling
ID: 33498098
Can you perhaps show me by inserting them in the codes? Thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:Dymer2
ID: 33498418
Hi,
I think Idle Mind means this:



Private Sub cmdLogin_Click()
    static progctrfinal as integer
    RunStatus = True
   
    Do While RunStatus = True
    DoEvents

        progctrfinal = TextBox1.Value
        For progctr = 1 To progctrfinal Step 1
            Call optionStatus(progctr)
            If progstuck Then Exit Sub
        Next progctr

    Loop
End Sub

Private Sub Pause_Click()
    RunStatus = False
End Sub

Private Sub Resume_Click()
    RunStatus = True
End Sub
0
 

Author Comment

by:teeling
ID: 33498463
I have tried the code below but it doesn't seem to pause. Anyone's got any idea why?
Private Sub cmdLogin_Click()
    RunStatus = True
    If OptionButton2.Value Then
        Debug.Print "Stop"
        Exit Sub
    End If
    
    If Restart.Value Then Call optionStatus(1)
    
    If pauseflag = 0 Then
     Do While RunStatus = True
        progctrfinal = TextBox1.Value
        For progctr = 1 To progctrfinal Step 1
            DoEvents
            Call optionStatus(progctr)
            If progstuck Then Exit Sub
        Next progctr
     Loop
    End If
    
    If pauseflag = 1 Then
        pauseflag = 0
        Do While RunStatus = True
            progctrfinal = TextBox1.Value
            For progctr = lastprogctr + 1 To progctrfinal Step 1
                DoEvents
                Call optionStatus(progctr)
                If progstuck Then Exit Sub
            Next progctr
        Loop
    End If
    
End Sub

Private Sub CommandButton1_Click()  'Pause
    lastprogctr = progctr
    pauseflag = 1
    RunStatus = False
End Sub

Private Sub CommandButton2_Click()  'Resume
    RunStatus = True
End Sub

Open in new window

0
 
LVL 17
ID: 33498702
sorry, I needed to go to bed.
in your code you are checking the pauseflag before you enter your increment loops, but you need to check it inside the loops, at an appropriate place.
try adding:
if pauseflag=1 stopvalue then exit do

add this after where  you start the loop counter and before the end of the loop.

as your program loops it must check the counter to see if it has reached the end condition, increment the counter AND check  to see if any other exit condition has occurred.
0
 
LVL 17
ID: 33498721
sry again, cantsee your code while im typing on my mobile.
you need to check the exit condition inside your For loops.
try not using For Next and just use do while
its only one extra step to increment your counter

do while ctr<20 And pauseflag=0
increment ctr
doevents
loop
0
 
LVL 17

Accepted Solution

by:
Thibault St john Cholmondeley-ffeatherstonehaugh the 2nd earned 500 total points
ID: 33504376
Here's a very basic version of what you need. Have a form with three command buttons cmdStart, cmdPause and cmdResume, a textbox textbox1 and a label label1.
I have commented all the lines of the code so hopefully you can see what is going on:
The label is there so you can see your counter (and I'm using it to preserve the ctr value when paused).
It needs a check that the textbox actually holds a legal value before you start, I'd put this under the start button and only call the increment routine if the value is a good one.
On testing it, if you are just going to let it run you will need to use a value something like 20000 or you won't be able to pause fast enough.
 

Option Explicit
 Dim runstatus As Boolean 'whether the loop can run or not. form level so all the procedures can see it
Private Sub cmdPause_Click()
    runstatus = False 'set the status so the loop will stop
End Sub
Private Sub cmdResume_Click()
    increment CLng(Label1.Caption) 'restart the counting using the preserved value of the counter
End Sub
Private Sub cmdStart_Click()
    increment 1 'start the counting from 1
End Sub
Private Sub increment(ctr As Long)
 runstatus = True 'make sure the loop can run
 Do While runstatus = True And ctr < Val(TextBox1.Value) 'only enter this loop if runstatus is true and the ctr hasn't finished
    ctr = ctr + 1 'increment the counter
    Label1.Caption = ctr 'output the counter value, using the label caption as a store of the current value
    DoEvents 'so the label can update its caption, and to allow the pause button to be pressed
 Loop 're-enter the loop if the loop conditions are still true
End Sub

 
I'm sorry I took so long to get back to you, but if you need any more explanation please ask.
0
 

Author Comment

by:teeling
ID: 33508083
The line below causes an error. Run-time error '13': Type mismatch. Why is that so?
increment CLng(Label1.Caption) 'restart the counting using the preserved value of the counter

Open in new window

0
 

Author Comment

by:teeling
ID: 33508111
My mistake. My label should be label3 instead of label1. I've got it working. Thanks a lot RobinD.
0
 

Author Closing Comment

by:teeling
ID: 33508113
Fantastic solution.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now