Solved

Pause a VBA program and make sure it has finished running a certain function before really pausing

Posted on 2010-08-24
23
469 Views
Last Modified: 2012-05-10
Referring to the code below, I am able to pause a program when I click on the pause button and resume whenever I click on the resume button. However, I always don't resume from the right spot. How can I press on the pause button and make sure that it has finished running the CommandCode function before really stopping? Thanks.
Private Sub cmdLogin_Click()
    Call increment(1)
End Sub

Private Sub increment(progctr As Long)
    RunStatus = True    'Make sure the loop can run
    progctrfinal = TextBox1.Value
    
    Do While RunStatus = True And progctr < progctrfinal
        Call CommandCode(progctr)
        progctr = progctr + 1
        Label3.Caption = progctr    'output the counter value, using the label caption as a store of the current value
        DoEvents
        If progstuck Then Exit Do
    Loop
    
End Sub

Private Sub Pause_Click()  
    RunStatus = False
End Sub

Private Sub Resume_Click()  
    RunStatus = True
    increment CLng(Label3.Caption) + 1 'restart the counting using the preserved value of the counter
End Sub

Function CommandCode(PC As Long)
    Dim command As String
    Dim argPassedIn As String
    Dim myarray As Variant
    
    command = Worksheets("GUI").Cells(PC + 1, 2).Value
    argPassedIn = Worksheets("GUI").Cells(PC + 1, 3).Value

    'There is only one argument
    If InStr(argPassedIn, ",") = False And Len(argPassedIn) > 0 Then CallByName Me, command, VbMethod, argPassedIn
    
    'There is no argument
    If Len(argPassedIn) = 0 Then CallByName Me, command, VbMethod
    
    'There are 2 arguments
    If InStr(argPassedIn, ",") Then
    myarray = Split(argPassedIn, ",")
    CallByName Me, command, VbMethod, myarray
    End If   
End Function

Open in new window

0
Comment
Question by:teeling
  • 11
  • 8
  • 2
  • +1
23 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33518501
Use a pause command in teh code?

Chris
Private Sub cmdLogin_Click()

    Call increment(1)

End Sub



Private Sub increment(progctr As Long)

    RunStatus = True    'Make sure the loop can run

    progctrfinal = TextBox1.Value

    

    Do While RunStatus = True And progctr < progctrfinal

        Call CommandCode(progctr)

stop

        progctr = progctr + 1

        Label3.Caption = progctr    'output the counter value, using the label caption as a store of the current value

        DoEvents

        If progstuck Then Exit Do

    Loop

    

End Sub



Private Sub Pause_Click()  

    RunStatus = False

End Sub



Private Sub Resume_Click()  

    RunStatus = True

    increment CLng(Label3.Caption) + 1 'restart the counting using the preserved value of the counter

End Sub



Function CommandCode(PC As Long)

    Dim command As String

    Dim argPassedIn As String

    Dim myarray As Variant

    

    command = Worksheets("GUI").Cells(PC + 1, 2).Value

    argPassedIn = Worksheets("GUI").Cells(PC + 1, 3).Value



    'There is only one argument

    If InStr(argPassedIn, ",") = False And Len(argPassedIn) > 0 Then CallByName Me, command, VbMethod, argPassedIn

    

    'There is no argument

    If Len(argPassedIn) = 0 Then CallByName Me, command, VbMethod

    

    'There are 2 arguments

    If InStr(argPassedIn, ",") Then

    myarray = Split(argPassedIn, ",")

    CallByName Me, command, VbMethod, myarray

    End If   

End Function

Open in new window

0
 

Author Comment

by:teeling
ID: 33518523
I only want it to pause when I click on the pause button though, i.e. Pause_Click() is called. When Pause_Click() is called, it immediately sets RunStatus to false, thus whatever's in the Do While loop will stop running.

I need to know how to press on the pause button but let the program finish running the current do while progctr and then pause?
0
 
LVL 17
ID: 33518640
the do while will continue as long as the two loop conditions are true. Once in the loop you have an exit if progstuck. if this is set during the called procedure then the  execution will jump out of the loop and continue from the line below. otherwise hitting pause will only stop the loop being entered each cycle, it won't cause any code to stop immediately the button is pressed, only at the beginning of the loop when the pauseflag is checked.
could the called procedure be throwing an error?
0
 
LVL 17
ID: 33518649
you are restarting at .caption  +1 and not at the value it held when it was paused, this could be why you are missing bits.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33518788
IN that case I think you are stuck ... but the vbe should always complete the current instruction so if you need it stop forward a few instructions press F8 to step it forward.

Chris
0
 
LVL 17
ID: 33524538
Here is a very slow step-through of your loop to help you see what is happening:
I have added an explanation with each line
   Do While RunStatus = True And progctr < progctrfinal

This checks the value of the runstatus and whether progctr is less than its progctrfinal value.
If both these conditions are true then the program will remember which line it is on and begin to execute the first line inside the loop. If either or both condition are false then the program execution will jump straight to the End Sub and exit the increment routine. So on starting, the progctr is 1 and lets say the finalctr is 20.
We have just set the runstatus to true and the ctrfinal value before we did the check above, both loop conditions are true so the next line calls the CommandCode routine.
       Call CommandCode(progctr)

The routine doesn't seem to affect either of our loop variables (Which is good) so when it finishes and exits it returns program execution to out next in-loop line.
         progctr = progctr + 1
 
We increment the value of our loop counter, just that, nothing more.
       Label3.Caption = progctr    'output the counter value, using the label caption as a store of the current value

We change the value of the label to our new counter value. We won't see the change yet as this program has control of the processor and won't let windows do any screen updating to our program at the moment, but remember that the value hs been incremented.
    DoEvents
We just temporarily released control to windows. It will now be able to update the label value and also check to see if anyone has pressed the Pause button. Lets say they have so the Pause_click code will execute and set the value of the runstatus variable to False. Nothing else for windows to do so it returns to our loop and checks the value of progstuck.
        If progstuck Then Exit Do

I don't have any information on progstuck so I can'[t tell you any more than if it is true then the execution will exit the loop now, go straight to the End Sub and exit the increment routine.
If progstuck is not true we get to the loop instruction.
    Loop

Remember where I said the program will remember where it is? This is where it uses that information. Loop tells it to go back up the top and check the loop conditions again.
 
    Do While RunStatus = True And progctr < progctrfinal

This time, the progctr has increased by 1 since the last time we checked it, but is still less than the final value it is aiming for. The runstatus however has now been set to false by the pause button code. This time the loop will not be entered, the program execution drops to the next line after the word Loop and will end sub and exit the increment routine.
  End Sub
 

The top of the loop is the only place where the runstatus has any affect on this code so anything called inside the loop must have reached its natural end before the pause occurs!
Now nothing is happening, but we do have a couple of changes. The label.caption value is now 2 (and we can see it). The runstatus is set to false.
If the Resume button is pressed, the increment code is called again, but with the value from the label (plus 1, but this will cause you to miss the number after the pause). (I'm not sure who told you to reset the runstatus under the Resume button, but it is unnecessary as the status is reset at he top of the increment routine - oops).
So we're calling increment from the pause button, the value we've sent in is 2 (or 3 if you leave the +1 in the call).
The loop conditions are both true so the loop will enter and continue, calling the Command routine with the value 2 (or 3 if the +1 is left in)..
The only way to  have the Command code not fully execute is if it throws an error itself or fails some part of its task.
I'm sorry this is so long, but I'm trying to show you how the loop code operates so you can narrow down the area to find your 'starting at wrong place' problem.
0
 
LVL 17
ID: 33524581
ah, phew! It wasn't me that put that extra reset of runstatus under the resume.
Had me worried there for a moment. :7)
 
Can you explain little more what made you think it was restarting at the wrong place?
0
 

Author Comment

by:teeling
ID: 33527092
I was the one who put in the reset of runstatus. Sorry. That was a really good step-by-step explanation. Thanks for taking the time and effort to do so. Really appreciate it.
DoEvents
We just temporarily released control to windows. It will now be able to update the label value and also check to see if anyone has pressed the Pause button.

Does it mean that if I were to press the 'Pause' button when CommandCode(progctr) is running, it will have no effect? The RunStatus = False will only take on an effect after the DoEvents is reached?

Thanks.
0
 

Author Comment

by:teeling
ID: 33527146
If you compare both images, you will see that MySend Chr(13) has been sent twice on the last line of Debug Screen whereas on the Excel Sheet, it goes to CheckWarning() after sending MySend Chr(13) only once.
excel-GUI.JPG
Debug-Screen.JPG
0
 
LVL 17
ID: 33527317
>Does it mean that if I were to press the 'Pause' button when CommandCode(progctr) is running, it will have no effect? The RunStatus = False will only take on an effect after the DoEvents is reached?

Yes, well... The Reaction to the button happens at the doevents. That reaction is to set the runstatus variable to false. Still nothing happens until the program gets round to the top of the loop again and checks the value of the variable. It's only now that pressing the button has an effect, the conditions are no longer true and the loop doesn't get re-entered.
No need to apologise for sticking bits of code in, it's your program you can do what you like. A good way to learn is to change things about and see if you can work out what's happening. I apologise for drawing attention to it, I thought I had left it in there. It won't do any harm where it is at the moment, but its something to be aware of as the complexity of your program grows and you start tearing your hair out wondering why a variable is still being reset when you know you just removed that code from the increment routine.
 
Unfortunately I can't tell from the screen prints as I don't know where in the code you have your debug.print statement. I can see an equivalent line for each of the rows, and a final chr 13 that matches the Warning row, but without the full code that was executing I can't tell you why from that.
Can you test by hitting the pause button and looking at the sheet while paused? You can see the number it paused at in the label3 caption, at the pause point the Command code should have executed 1 less than that many times - the counter is incremented after the command code has been called so it displays 1 higher than the actual executions. Might be clearer for future checking to change that call to after the increment, call the procedure with 0 instead of 1 and terminate the loop at 1 below the finalctr so it loops the correct number of times.
 
Best way to see what's happening is to step through your code as chris suggested.
try adding this:
   Do While RunStatus = True And progctr < progctrfinal
If
        Call CommandCode(progctr)
        progctr = progctr + 1
 
0
 
LVL 17
ID: 33527323
oops sorry, posted that too early.... don't add anything yet I'm just checking it.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:teeling
ID: 33527334
alright.. still reading your previous post :)
0
 
LVL 17
ID: 33527379
Here's the addition, it takes *that* long to open a spreadsheet on my dying machine.
 
    Do While RunStatus = True And progctr < progctrfinal

      If progctr=11 then stop  
        Call CommandCode(progctr)
        progctr = progctr + 1
 
I've stopped it at 11 because it's near where you think your problem is, change 11 to anything you like. It just saves stepping through the same lines over and over as it will have no effect unless the ctr is exactly 11 and the program can run quickly to there.
It will do as it says and as chris suggested up above, when the program has thrown itself into a pause state, step through it line by line using the F8 key. As it steps off each line of code you should be able to switch to your spreadsheet and see the results of that line executing. Hopefully you will be able to narrow down what is actually going on at that place. This Pause is nothing to do with the pause/runstatus/resume code in your program, it's part of the debug tool, put there so you can step through your code line by line and also find the values of variables by typing into the debug window.
0
 

Author Comment

by:teeling
ID: 33527444
I think the problem lies within my WaitCurs() and CheckWarning() codes. Call WaitCurs(17,16) on progctr = 13 seems to be screwing things up. It checks for the cursor to be at a certain position before proceeding.
Function WaitCurs(Row As Integer, Col As Integer)
    ctr9 = 1
    Do Until ctr9 = 800000
                DoEvents
        If RowNo = Row And ColNo = Col Then
            CurPos = True
            Exit Do
        Else
            If ctr5 > 799000 Then
            CurPos = False
            End If
        End If
        ctr9 = ctr9 + 1
    Loop
End Function

Open in new window

0
 
LVL 17
ID: 33528897
Hi teeling,
unfortunately our times seem to be offset by a few hours. I went to bed at 2am, just got up and going to work. I cant see your whole code on my mobile, but if youve narrowed it down to there you should be able to find it.
try pausing (with the vbEditor,(click in the left hand bar next to your code line and a symbol will appear, the program will pause at that line.) just above the lines you suspect, step in with f8 and see if you can catch it. Dont try stepping through 80000 times manually :7)
I'll catch up with you later. Good luck.
0
 
LVL 17

Accepted Solution

by:
Thibault St john Cholmondeley-ffeatherstonehaugh the 2nd earned 500 total points
ID: 33534607
I've taken the liberty of shortening your waitcur code, there's less moving parts so might be easier to follow.
I'm not positive it does what you want, there is no check of ctr5, and it will never exit unless the row and col positions are reported true. What it does is wait forever and only continue if those conditions become true. Your's looks more like a delay with a timeout after 800000 loops so this might not be what you want.
Function waitcurs2(Row As Integer, Col As Integer)
'setting curpos to false before beginning to test, the only way out of the loop is if it becomes true
    Do While True 'spin forever. It will never exit unless the col and row conditions become true, so safer to add a check of a stop variable that's driven by a button as well as the check for the cursor position.
        DoEvents 'allow things to happen
        If RowNo = Row And colno = Col Then 'if cursor at the spot
            CurPos = True 'set curpos to true
            Exit Do 'and jump out
        End If
    Loop
End Function

If you are still having trouble locating your problem, try stopping at the lines you believe to be the cause and check that the value of the variables are as expected. If they are then you could call your subroutines directly from the immediate pane with those values and step the code through line by line. You will get there, it just takes a little patience.
0
 

Author Comment

by:teeling
ID: 33537921
That's a really good WaitCurs() function modification. I've been meaning to make that more efficient. I found out where the problem lies.

The WaitCurs(17,16) function at progctr = 12 was previously waiting for a cursor position that will never get there. Hence, when I press pause, it gets stuck because it's not even there yet. And when I press resume, the function on that progctr gets repeated. I've added in another method of checking, i.e. WaitChar().

For example, WaitChar("Y",4,71,"Y",5,71) is checking for 2 letters in a terminal screen in 2 different positions, i.e. "Y" at (4,71) and "Y" at (5,71). This seems to solve my problelm.

RobinD, I really appreciate your help in this matter. Thank you very much. I just got into work now as well.
Function WaitChar(ByRef myarray As Variant)
    counter = 1
    CharPos = False
    
    Do Until CharPos = True
                DoEvents
        If ScreenArr(myarray(1), myarray(2)) = myarray(0) And ScreenArr(myarray(4), myarray(5)) = myarray(3) Then
            CharPos = True
            Exit Do
        Else
            CharPos = False
        End If
        counter = counter + 1
    Loop
    
End Function

Open in new window

0
 
LVL 17
ID: 33538172
>waiting for a cursor position that will never get there
so easy to do. Another good way to trap yourself is a loop that never gets entered.
I'm glad you found the problem. 2am again, I'm off to bed.
 
0
 

Author Comment

by:teeling
ID: 33555678
Related question, If I find that one of the progctr step is stuck, is there anyway to close the userform and change whatever is on the excel sheet and then bring back the userform and resume from where it left off?
0
 
LVL 17
ID: 33557038
Not probably as easy as you hope. If you close the form the program stops and all the values of the variables are lost.
You could perhaps use one of the cells in the worksheet to start and maintain the counter values, so when you restart the program it grabs the value from the worksheet instead of from the label or the textbox. A better solution would be to identify why the counter is getting stuck and insert something in the code to deal with it.
If you do need to stop the program and want to restart from a known row then you could add a 'Start from..' text box as well as your final and count between the two. It will work, but it's a bit scruffy if anyone but you is ever going to use the program.
Dealing with all possible errors in code and exiting politely when someting really fatal occurs is the better way to go.
 
0
 

Author Comment

by:teeling
ID: 33592346
Well. I agree with you. Thanks for the advice.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34914428
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 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

19 Experts available now in Live!

Get 1:1 Help Now