Interrupt Excel code execution

[automaker]

I have an application that can take a long time to run. Should the user want/need to stop during execution (press esc key), I would like to provide the option of saving certain parameters to a text file so the program can pick up where it left off and not have to start from the beginning. How do I go about attaching code to the esc key?
I am running Windows XP pro and VBA 6

If I can't use the esc key, is there another way for the user to interrupt the code execution?

Thanks for the help

 

[MacroAlan]

I don't think that can be done.

I can see writing the parameters to a text file like one would write anything to text but see lots of programming to read it and try to pick up the pieces.


Alan

 

[Zathras]

Never say it can't be done. That's like waving a red flag in front of a bull.

One way is to use a user form to initiate the process.

1. Insert a new code module and paste this code:

Option Explicit
Public EscapeRequested As Boolean

Sub LongRunningJob()
Dim x As Integer
  x = 1
  EscapeRequested = False
  While x > 0 And EscapeRequested = False
    If x >= 10 Then
      x = 1
    Else
      x = x + 1
    End If
    DoEvents
    If EscapeRequested Then
      SaveParameters
    End If
  Wend
End Sub

Sub SaveParameters()
  MsgBox "Saving Parameters"
End Sub

2. Insert a new user form and drop a button on it.
3. Double click on the button and paste this code:

Option Explicit

Private Sub CommandButton1_Click()
  LongRunningJob[COLOR=green] ' See Module1[/color]
End Sub

Private Sub CommandButton1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If KeyAscii = 27 Then
    EscapeRequested = True
  End If
End Sub

4. Press F5 to run the form.
5. Press the Escape key to see the results.

You can put whatever code you need in the sub to save your parameters.

 

[automaker]

Thanks a bunch. I will give it a try.

 

[automaker]

Zathras,
I'm not having any luck. I think I have everything configured the way you suggested but when I hit the escape key, I get the normal break in the code. It appears that the keypress procedure is never accessed. I tried using keydown to maybe catch the event earlier but no dice. I also read that the commandbutton needs to retain focus. I have screenupdating turned off for speed and because I am switching between workbooks. I tried reseting focus but that didn't help. Any suggestions?

Since I am now initiating from a user form, is there a way I can put another command button on the form and interrupt the code that way? I read something about using sendkeys but I'm not sure another button will have any effect while code is running.

What do you think.

 

[Zathras]

Sure. You can drop another command button, double-click and paste this code:

Private Sub CommandButton2_Click()
  EscapeRequested = True
End Sub

BTW, you should re-name the buttons for better documentation. I left them as CommandButton1 and CommandButton2 for simplicity sake in the example.

 

[automaker]

Zathras,
I will give this concept a try. Also, I understand about the button names.

Thanks

 

[automaker]

Zathras,
No luck. Once code is running, clicking on other buttons in the form does nothing.

I must be doing something wrong trying to use the keyboard. Somehow input from the keyboard is possible while code is executing because esc and Ctrl/alt/delete work. I am going to go back to trying to get something from the keyboard to work. I believe I will try something other than the esc key becuase... well, I don't know why. I will get back to you one way or the other.

Thanks for all the help.

 

[Zathras]

What version of Excel are you using?

Did you paste my code exactly as posted?

Post the code you are using so we can get a look at it.

 

[automaker]

I am using Excel 2002.

I will get back to you on the code.

 

[Zathras]

That might explain it. I'm using 2003.

 

[automaker]

Zathras,
I think I finally got it to work. I was doing some things that I thought would not affect the outcome like additional forms, function calls, etc. I tried everything (I thought) and couldn't get my program to work. I did something though and now it seems to work. I hate it when I can't isolate what I did wrong and what I did to fix it. It means that sooner or later, I will mess it up again.

Thanks for the help and the patience. Now I have to get busy writing the saving portion.