Funcitonality to pause macro?!

[Webtechie]

I've created a userform based application. The application processes
thousans of records and can go for awhile (4 days or more).

The problem is that the client at times may need to pause the processing for
some reason. He has asked me to create the functionality to pause the macro?

I'm not sure how to pause the macro while it is processing. I have a
progressbar that is running. As each record is processed, the progressbar
increments.

Any ideas how to pause the macro while it is processing? Create Key stroke
event?

Thanks.

Tony

 

[JLGWhiz]

How long does he want to pause it and what does he want to pause it for?
Does he want to manually manipulate data and then continue, or does he just
want to evaluate what he has and then continue? It would be helpful to
understand what the purpose of stoping the macro is. You can stop it with a
message box set for OnTime. Or you can use Stop in and on time statement
that would be like setting a breakpoint. And a couple of other things, but
depending on what the user will do to the file, or wants to do, would
determine the approach.

 

[JLGWhiz]

This site might help.

http://www.pcreview.co.uk/forums/thread-2537048.php

 

[JLGWhiz]

One more thought, if he just wants to stop the macro to use the computer for
some other purpose, tell him abou Ctrl + Pause/Break. That will stop it and
then when he is ready, he can just restart the macro.

 

[Webtechie]

The userform is taking records from a database, going out to the internet and
getting data, then bringing that data back down to update the database.

But it could take a long time because of the number of records in the
database.

Tony

 

[Tim Williams]

What is it doing that it might take 4 days ?

Tim

 

[Webtechie]

I'd like to have the userform have a button or something that can be pressed
to pause the macro while it is running. However, the progressbar has the
focus while the macro is running.

Not sure how to have the progressbar running, screen updating set to false
and have a button be able to pause the macro if needed.

Tony

 

[Webtechie]

The processing of each record could take 6 seconds. That is 10 a minute.
For 60 minutes times 24 hours = 14,400 records processed in one day. I have
a whole lot more records to process than that.

 

[JLGWhiz]

The parameters of the objective are too vague for me. If the macro can run
for four days, is it because the file being downloaded has that many records
to process, or is it perpetually refreshing and down loading updated records?
Does the user want to pause without breaking the sequence of records being
retrieved, or do they just want to access the computer and don't particularly
care about the continuity of the records retrieval? Again, why can't the
user simply halt the macro with Ctrl + Pause/Break? Too many unanswered
questions.

 

[Webtechie]

I think you are digressing and trying to hard.

It is a simple question.

The records are processing.
A userform is showing the progress.
The user would like to stop processing to access something on the computer.
I am not sure ctrl+Pause will work, but will try.

My question was for an idea of who to stop the processing of a macro while a
userform and progressbar have the focus. The screen updating is set to false.

 

[Tim Zych]

From the description seems like you might consider a process to keep a tally
of which records have been updated, and which have not. Have a mechanism in
place that stops the macro, say with a userform button and DoEvents or ESC
with EnableCancelKey. The record at the exit point is the first record to
resume when the macro runs next.

Example using a userform

' In a regular module
Public gbAbort As Boolean

' In a userform
Private Sub cmdStop_Click()
gbAbort = True
DoEvents
End Sub

Private Sub UserForm_Activate()
gbAbort = False
Dim x As Long
For x = 1 To 100000
DoEvents
If gbAbort Then
MsgBox "You stopped on " & x
Unload Me
Exit Sub
End If
Debug.Print x
Next
End Sub

Example using EnableCancelKey

Sub ExitMacro()
On Error GoTo ErrHandler
Application.EnableCancelKey = xlErrorHandler
Dim x As Long
For x = 1 To 100000
Debug.Print x
Next
Exit Sub
ErrHandler:
If Err.Number = 18 Then
' ESC key or Ctrl + Break pressed
MsgBox "You stopped on " & x
Else
' Some other error
End If
End Sub

 

[jaf]

Hi Tony,
It's easy to pause the macro. A "Pause" button on your form can trigger the event.

Restarting where you left off is the problem.
VBA has no "continue" command like older versions of BASIC.
You would have to programmatically create pointers to every relevant piece of data including all loops, then pause the macro.

Now the problem humongous.
Why pause the macro?
A better question is... What are they doing while the macro is paused?
Load another workbook and your macro crashes.
Fire off another macro and your macro crashes.
Do anything of significance with some unknown Windows process and your macro crashes.

There are too many variables that could cause the macro to lose track of where it was.

It sounds like this process either needs to be speedup considerably, ported from VBA, or run on a dedicated machine.

John

 

[Tim Williams]

For the pausing question it seems like Tim Zych's approach would be the way
to go.
Is the slow part of the processing getting the info from the web ?

Tim

 

[got.sp4m]

If you just want to yield processing power to other processes try
this:

In a userform with two buttons (btnPauseResume and btnLoop) one label
(lblCounter):

Option Explicit

Private mboolPause As Boolean
Private mboolAbort As Boolean

Private Sub btnPauseResume_Click()
Const strcPauseCaption As String = "Pause"
Const strcContinueCaption As String = "Resume"
If mboolPause Then
btnPauseResume.Caption = strcPauseCaption
Else
btnPauseResume.Caption = strcContinueCaption
End If
mboolPause = Not mboolPause
End Sub
Private Sub btnLoop_Click()
Dim i As Long
For i = 1 To 10000000
lblCounter.Caption = CStr(i)
Do
DoEvents
If mboolAbort Then
Exit For
End If
Loop While mboolPause
Next i
End Sub
Private Sub UserForm_Terminate()
mboolAbort = True
End Sub


best regards
Peder Schmedling