On Error Flakeyness

B

BillCPA

What causes the 'On Error GoTo xxx' to work sometimes and not to work other
times.

I have a loop that contains an On Error statement. The first time through
the loop the code encounters a cell with data that causes an error, and the
On Error statement handles it - it sends the code to the designated label,
where I have an 'On Error GoTo 0' statement. The next time through the loop,
the 'On Error' Statement is again set, and the code encounters the next cell,
which has exactly the same data as the previous cell. This time an error
message pops up instead of the On Error statement handling the error.

I have tried using 'Err.Clear', but that doesn't seem to work either.

Also, are there certain errors that 'On Error' doesn't handle? I have tried
using it before trying to select a worksheet that does not exist, and I get
an error message rather than the 'On Error' handling it.

I can work around most of these problems, but I'd like to know what I'm not
doing correctly.
 
D

Dave Peterson

Without seeing your code, I'm betting that you don't finish up handling the
error.

From VBA's help:

If an error occurs while an error handler is active (between the occurrence
of the error and a Resume, Exit Sub, Exit Function, or Exit Property
statement), the current procedure's error handler can't handle the error.

A simple example:

I put some numbers in D1:D2 and =1/0 in D3:D4 and another number in D5 and ran
this code:

Option Explicit
Sub testme()

Dim myCell As Range

On Error GoTo errHandler:
For Each myCell In Range("d1:d4").Cells
If myCell.Value > 1 Then
MsgBox "greater than 1!"
End If
getNext:
Next myCell

Exit Sub

errHandler:
MsgBox Err.Number & vbLf & Err.Description
Resume getNext:
End Sub

If this doesn't strike a nerve, you may want to post your problem code.
 
B

BillCPA

It generally helps to read the directions, doesn't it. I've just always
assumed that giving the code somewhere to go when an error occurs, and using
the 'On Error GoTo 0' statement, makes everything OK, and that the error
handler is ready to take on the next error. Obviously it wasn't designed
that way - the Resume (or Exit) is what resets it.

Thanks for your help.
 
Top