EnableCancelKey with On Error Resume Next

[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

 

[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

 

[Paul S]

Tom,

Yes of course, what was I thinking of!

Many thanks for the "rescue".

Regards,
Paul