EnableCancelKey with On Error Resume Next

P

Paul S

Hi,

I want to brute force errors in a long loop with
On Error.Resume Next

But I also want to give user a chance to Ctrl Break and
clean up in an orderly way.

Variations on the following seem to work intermittently
but unreliably (.EnableCancelKey = xlErrorHandler inside
the loop slightly more reliable):

Sub ErrorTest()

With Application
For i = 1 To 100000
On Error GoTo ErrH
.EnableCancelKey = xlErrorHandler
'sometimes catches Ctrl Break here

On Error Resume Next
'don't want to break here
.EnableCancelKey = xlDisabled
x = 1 / 0 ' allow this error
Next

ErrH:
.EnableCancelKey = xlInterrupt
End With

MsgBox i & " Err " & Err.Number '18 = user Ctrl Break
End Sub

Not sure if what I'm trying to achieve is possible but any
solution appreciated.

TIA,
Paul
 
T

Tom Ogilvy

Again, help comes to the rescue:

xlErrorHandler The interrupt is sent to the running procedure as an
error, trappable by an error handler set up with an On Error GoTo statement.
The trappable error code is 18.


So if err.Number = 18 then you can jump out or if it isn't, continue on

as an example:


Sub Tester10()
Application.EnableCancelKey = xlErrorHandler
On Error GoTo ErrHandler
For i = 1 To 100000
If Rnd() < 0.001 Then
Err.Raise 1000
End If
Cells(1, 1) = i
Next
Exit Sub
ErrHandler:
If Err.Number <> 18 Then
Resume Next
End If
MsgBox "You cancelled"
End Sub
 
P

Paul S

Tom,

Yes of course, what was I thinking of!

Many thanks for the "rescue".

Regards,
Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top