How to Pause Macro then resume via keystrokes

[cruss]

Ladies, Gents,

When the macro is running, it effectively stops the user from selecting any cells, or working within the spreadsheet in any way.

I need the spreadsheet user to be able to pause the running macro via a set of key strokes eg CTRL p for Pause (or other method?).
When the macro is paused, the user can jump into the spreadsheet and "do their thing" eg select a cell for a title, do a graph and print it etc etc. Then when he or she is ready, resume the macro from where it paused with another set of keystrokes (or other method - button?).

They should be able to do this as many times as they like while the macro is running.

The application is Data acquisition from a measuring device, where there can be an hour or more between 1 reading and the next.

Any help here would be appreciated.

Regards

Chris

 

[sijpie]

Ctrl-Break will stop the macro

What is your macro doing while it runs and while waiting on the next data set? Is it going through a check loop?

VBA has severe restrictions - while it is running there can be no outside interference (other than Ctrl-Break)
But at the same time you can't 'wait' for a fixed period that allows user interference during the wait.

There is a wait function, and that does just that. It waits for a predefined interval then continues. It does not check the keyboard queu.

Now you can add some flexibility by adding your own keyboard handler, but it is pretty advanced stuff.

this following code allows the user to intercept the code by pressing the F10 key:
At the top of your module:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "User32" _
   (ByVal vKey As Long) As Long

then somewhere in the module:

' ### this is the function that intercepts a keystroke. If nothing 
'### is in the keybuffer it returns false
Private Function KeyDown(ByVal vKey As Long) _
    As Boolean
   KeyDown = GetAsyncKeyState(vKey) And &H8000
End Function
'DEMO USAGE
Sub testk()
    Dim i As Long, waitTime
    Dim newhour As Integer, newminute As Integer, newsecond As Integer
    For i = 0 To 5000
        ' check to see if F10 is pressed
        If KeyDown(vbKeyF10) Then
        MsgBox "You pressed F10!"
        Exit For
        End If
        ActiveCell.Value = i
        ActiveCell.Offset(1, 0).Select
    Next i
    MsgBox "End"
End Sub

You could use this to stop the macro from running(you can't 'Pause' the macro), then ask the user to press a restart button that runs a macro to bring it back in the loop.

 

[cruss]

Thanks for the reply sijpie.

The spreadsheet is logging RS232 input from digital indicators that measure deflection. It starts the NOW() function to make a clock/timer then adds the user defined time intervals at which it has to go fetch the reading from the instrument with some DDE code and a program called WinWedge (by Taltech).

Since the NOW() function is running non stop, the spreadsheet can't be broken into easily by the user. I need them to be able to stop/pause the macro to do what they want in between readings - which can be hours or even days. Then it needs to be able to resume and start looking at the clock to see if it's time for the next reading.

It sucks the resources hard by using the NOW() function though. It would be better to send the spreadsheet as just the macro doesn't give you the full picture - be here it is :

Sub CaptureData()
'
'
LastRow = Range("J65536").End(xlUp).Row

' Start Actual time in Cell I2
Range("I2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
RealTime = ActiveCell
Selection.Copy

' Special Paste Actual time into I21 to keep as a fixed baseline and avoid continual refreshing
Range("I21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

For x = 22 To LastRow

TimerPointCell = "I" & x
Range(TimerPointCell).Select
TimerPoint = ActiveCell
Do
Range("I2").Select
ActiveCell.FormulaR1C1 = "=NOW()"
RealTime = ActiveCell
Diff = TimerPoint - RealTime
Loop While Diff > 0.000001
DataCell = "H" & x
Range(DataCell).Select
' Code to get data from Digimatic Indicator
ActiveCell = x

Next x

End Sub


But yes - I need a way to stop the code in between readings to allow the user to play around, the a way for them to restart and have the macro pick up where it left off.

Thanks sijpie

Chris

 

[cruss]

Hi Sijpie,

Thanks for Code. I have been playing with it - works well thank you.

I did make a small alteration and am not sure why it has a problem - Run time Error 91?????. See code below:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "User32" _
(ByVal vKey As Long) As Long

' ### this is the function that intercepts a keystroke. If nothing
'### is in the keybuffer it returns false
Private Function KeyDown(ByVal vKey As Long) _
As Boolean
KeyDown = GetAsyncKeyState(vKey) And &H8000
End Function
'DEMO USAGE
Sub testk()
Dim i As Integer
Dim DataCell As Range

For i = 1 To 5000
' check to see if F10 is pressed
If KeyDown(vbKeyF10) Then
MsgBox "You pressed F10 Pal!"
Exit For
End If
DataCell = "a" & i
Range(DataCell).Select
ActiveCell = i
' ActiveCell.Value = i
' ActiveCell.Offset(1, 0).Select
Next i
MsgBox "End of Demo"
End Sub

 

[sijpie]

When you post code, please put it between code tags (either press the # button in the forum text window, or type the tags out [code ] your code here [/code ] (without the space in front of the closing brackets)
In your modified example you declare DataCell as a range.
Then the firsttime you use it you are trying to point DatCell to A1 using:

DataCell = "A" & i

That is not the correct way. What VBA thinks you are doing is setting the value of Datacell to "A1" (or whatever i is). But DataCell hasn't been assigned yet. Hence the error.

Correct way to set a range to a range:

Set DataCell = Range("A" & i)

If you want to point an Object (such as a Range, a WorkSheet or so) to an object you need to use this constructtion

Set MyObject = Object

You need to understand that VBA uses defaults to do things, and therefore it is better programming to write out what you mean.

dim MyRange as Range
Set MyRange = Range("B20:F40")
 
MyRange = "A1"
'This is interpreted as:
'MyRange.Value = "A1"
'and so all cells in the range get the value "A1"

The inefficiencies in your capture code I will discuss in the next post

 

[sijpie]

See the comments in the code. Best to copy it inot your code editor, than you can read it more easily

Sub CaptureData()
'
'   You haven't declared any of your variables
'   This can lead to errors when you make a typo
'   It also has Excel create all these variables as type 'Variant'
'   Which take a lot of memory space and are inefficient to work with for Excel
'   Also an other programmer then doesn't quickly see what the variable is
'   supposed to do. And bugs can occur because you do things which you
'   were not planning to do, but the error is hidden because excel "makes it work"
LastRow = Range("J65536").End(xlUp).Row
' Start Actual time in Cell I2
Range("I2").Select              'Selecting a cell is inefficient
ActiveCell.FormulaR1C1 = "=NOW()"
RealTime = ActiveCell
Selection.Copy
' Special Paste Actual time into I21 to keep as a fixed baseline and avoid continual refreshing
Range("I21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'   You could have set the time value in the cell without using the formula and cut and paste
'   by using the function Now()
For x = 22 To LastRow
'   You didn't indent your code, making it very difficult to see where
'   the loops and ifs are, leading to errors
TimerPointCell = "I" & x
Range(TimerPointCell).Select
TimerPoint = ActiveCell
Do      'this whole do loop can be replaced with a simple Wait function
Range("I2").Select
ActiveCell.FormulaR1C1 = "=NOW()"   ' Why is this in a loop?
'   Why are you setting the same cell over and over again to the
'   current time?
RealTime = ActiveCell
Diff = TimerPoint - RealTime
Loop While Diff > 0.000001
DataCell = "H" & x
Range(DataCell).Select
' Code to get data from Digimatic Indicator
ActiveCell = x  'what does this do? What is your active cell
'   after the data has been collected?
'   Do you use this at all later?
Next x
End Sub

Now for a cleaned up version:

Sub CaptureData()
'
    Dim LastRow As Long
    
    LastRow = Range("J65536").End(xlUp).Row
    
    ' Store start time into I21 to keep as a fixed baseline and avoid continual refreshing
    Range("I21").Value = Now()
    
    For x = 22 To LastRow
        
'        TimerPoint = Range("I" & x).Value   'is Timerpoint used in the data colection
        'otherwise this whole line is superfluous
        
        Application.Wait (Now + 0.000001)
        
        Range("H" & x).Select
        ' Code to get data from Digimatic Indicator
'        ActiveCell = x  'what does this do?
        
    Next x
End Sub

Now for the final version with user interuption

Option Explicit

Private Declare Function GetAsyncKeyState Lib "User32" _
(ByVal vKey As Long) As Long
 
' ### this is the function that intercepts a keystroke. If nothing
'### is in the keybuffer it returns false
Private Function KeyDown(ByVal vKey As Long) _
As Boolean
      KeyDown = GetAsyncKeyState(vKey) And &H8000
End Function

 
 
Sub CaptureData()
'
    Dim LastRow As Long
    
    LastRow = Range("J65536").End(xlUp).Row
    
    ' Store start time into I21 to keep as a fixed baseline and avoid continual refreshing
    Range("I21").Value = Now()
    
    For x = 22 To LastRow
        
'        TimerPoint = Range("I" & x).Value   'is Timerpoint used in the data colection
        'otherwise this whole line is superfluous
        
        Application.Wait (Now + 0.000001)
        
        'Check for user interuption
        If KeyDown(vbKeyF10) Then Exit For
        
        Range("H" & x).Select
        ' Code to get data from Digimatic Indicator
'        ActiveCell = x  'what does this do? Can it be deleted
        
    Next x
End Sub

There is something in your code I don't understand.
Why is it going down the existing rows each time checking for data? Where is the data added? It should get to the bottom row within a few seconds. Then the code stops.

 

[cruss]

Hi,

The spreadsheet front end has a list of times that I want to capture the data after the instant the operator hits the start command eg 1min, 2mins, 4mins, 8mins etc etc. So the list could be weeks long. The aim is to let a long test run automatically and capture all data.

The now() function runs all the time because it is constantly looking at the next data capture time point and asking "am I there yet". When it does eventually reach it, then it will run off and do the capture - currently I was just writing the line number in for the sake of running the code. The now() function running constantly is a heavy user of resources but I don't know of another way to constantly compare the actual time to a future time/date in the spreadsheet.

You key capture code does the trick, so now I just need to add in all the error checking and catering for different scenarios the user may expect eg pausing the code, adding a few more data capture Date/Times, then restarting etc etc.

Thanks for your help sofar, it's been exceptional.

The only part that I would like to be more efficient is the use of the now() function, which is essentially a timer.

Regards

Chris

 

[sijpie]

VBA is not the ideal platform for this, as (as mentioned before) it only knows two modes - not running, or running.
In other languages you can tell it to run a s abackground thread, occasionally checking if soemthing needs to be done. In VBA you need to do this a s a running loop, and so it is a foreground thread.
So it is not somuch the now() function, as running through this loop that takes the resources.

 

[sijpie]

This code will do what you want I think, run past the waiting times given in column J and it assumes the results are written in column H.
When the user pauses it will continue where it leftr off once the macro is restarted.

Sub CaptureData()
'
    Dim LastRow As Long, StartRow As Long, x As Long, t As Long
    
' ###    column of measurement interval times in minutes
' ###   starts from J22
' ###   result will be written in column H next to time interval
' ###   User can interupt measurement and restart at last point
    
    LastRow = Range("J65536").End(xlUp).Row
    StartRow = Range("H65536").End(xlUp).Row + 1 ' find last entry
    If StartRow < 22 Then StartRow = 22          ' no entries yet
    
    With Range("H" & StartRow)
        Do While .Offset(x, 2).Value <> vbNullString ' while column J has value
            ' value in J  is in minutes to wait for next measurement
            For t = 0 To .Offset(x, 2).Value * 60 '* 2     ' number of  seconds to wait
                Application.Wait (Now + 0.0000115741)  ' 1 second loop
                'Check for user interuption
                If KeyDown(vbKeyF10) Then
                    MsgBox "User Interuption, when finished restart macro to continue"
                    Exit Sub
                End If
            Next t
        
            .Offset(x, 0).Select
' ###             Code to get data from Digimatic Indicator
        x = x + 1
        Loop
    End With
    MsgBox "All measurements done"
End Sub

I have split up the waiting loop in 1 second intervals to check for user interuption. If you would set application.wait for the whole period, then the user would not be able to interupt. Wait is Wait in VBA, nothing happens.

You will notice (with taskManager) that the processor is making overtime during the loop, agian as explained above, VBA keeps it busy...

 

[sijpie]

You can also try to loo at Application.onTime. This schedules to run a procedure in the future and then acts normally. At the assigned time, the macor will then run.

you could set up to run the datagathering as per the schedule.

But I have had problems getting a demo to work for you, so obviously it is not as easy as it looks. More info:

http://www.ozgrid.com/Excel/run-macro-on-time.htm
http://www.mrexcel.com/forum/showthread.php?t=64257
http://msdn.microsoft.com/en-us/library/aa195809(v=office.11).aspx