Difference Escape and Ctrl + Break to interrupt code

[RB Smissaert]

Have a bit of code with this simplified construction:


Sub Test()

On Error GoTo STOPTEST
Application.EnableCancelKey = xlErrorHandler

OtherSub

Do While Condition = True
DoEvents
OtherSub
DoEvents
Loop

STOPTEST:

MsgBox "Stopped Test"

End Sub

Now when I am in the Sub OtherSub the Escape key will interrupt this code
and go to STOPTEST, but not when
I am in the Do While loop and not in OtherSub.
Ctrl + Break/Pause will work in both situations.
What could be the explanation for this?
Ideally I would like the Escape key to work in both situations.


RBS

 

[keepITcool]

Hi Bart,

I think it's related to the DoEvents.

Also note that if you have an errorhandler in Othersub
THAT will take over.

e.g.

Sub Test()
On Error GoTo errProc:
Application.EnableCancelKey = xlErrorHandler

BusyBee
MsgBox "Returned/completed"
Exit Sub

errProc:
MsgBox "Stopped Test"
End Sub

Sub BusyBee()
Dim i#, n#, m#
m = 100000000#

On Error GoTo errProc:
'note when called from test the key is in effect

For i = 1 To m: n = n + 1: Next

MsgBox "switch handler"
On Error GoTo 0
'each error will now be handled by the handler from the caller.
'but the enablekey is still active..
For i = 1 To m: n = n + 1: Next

MsgBox "from now on Esc wont work"
For i = 1 To m
n = n + 1: If n Mod 1000 Then DoEvents
Next

Exit Sub

errProc:
If MsgBox("Stopped Other... Continue?", vbYesNo) = vbYes Then
Resume
Else
End
End If
End Sub



hth
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


RB Smissaert wrote :

 

[RB Smissaert]

Hi keepITcool,

I still don't quite get it.
The Sub OtherSub has no error handler. Leaving out one or both of the
DoEvents statements
makes matters worse as Excel will just freeze or crash on pressing the
Escape key, so I need these.
I have done a bit more reading about this and it seems that Ctrl +
Pause/Break is just more reliable than Escape.
I am in Excel 2002. Maybe I need some API to capture a keypress and take
action according.

RBS

 

[Vasant Nanavati]

I have done a bit more reading about this and it seems that Ctrl +
Pause/Break is just more reliable than Escape.

You have hit the nail on the head, RBS. This has been my personal
experience.

I am in Excel 2002. Maybe I need some API to capture a keypress and take
action according.

Perhaps you are looking for something like this:

http://groups-beta.google.com/group/microsoft.public.excel.programming/msg/5ca8f27597f656cd?hl=en

Regards,

Vasant

 

[RB Smissaert]

Vasant,

Thanks, that looks like a nice and simple API and will give that a try.

RBS

 

[RB Smissaert]

Vasant,

Tried that API and it works perfect.
Thanks again for the tip.

RBS

 

[Vasant Nanavati]

Glad to help, RBS!

--

Vasant

Vasant,

Tried that API and it works perfect.
Thanks again for the tip.

RBS

 

[keepITcool]

...
Bart,
aha.. but if tou raise error 18.. it will get caught.
and if your caller sub will handle the error...

Option Explicit
Private Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer

Sub escTest()
On Error GoTo errH:
Application.EnableCancelKey = xlErrorHandler

Call BusyBee
MsgBox "done"
Exit Sub

errH:
MsgBox Err.Number & vbLf & Err.Description
End Sub

Sub BusyBee()
Dim i&, n&, m&
m = 100000000
For i = 1 To m
If GetKeyState(vbKeyEscape) < 0 Then Err.Raise 18
n = n + 1
Next
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


RB Smissaert wrote :

 

[RB Smissaert]

I think in my particular situation Ctrl + Pause/Break is actually fine,
because the Sub OtherSub will
run about every 20 seconds and in between you can do some things in Excel.
So you don't want to lose the Escape key as this can be needed for example
to get dialogs away.
It also will keep the code simple. Will have a look at both other
suggestions though.

RBS