Error handling only works once

Question

I have a very simple VBA code that should try to open a nonexistent file, send me to an error handler, then back to my code in an infinite loop (intentionally).

However, the compiler only catches the error the first time, then breaks on the second pass.

I have tried every combination of On Error statements to send it back on the second pass, but nothing seems to work. Here is the code:

Sub TestError()
    On Error GoTo errError
lblError:
    On Error GoTo errError
    'Code that should raise an error and send to errError
    Excel.Application.Workbooks.Open ("lakdfjldkj")
    Exit Sub
errError:
    MsgBox "Code didn't break"
    GoTo lblError
End Sub

Is there something I'm missing, or is this a bug?

Don't test this unless you are at peace with having to kill Excel, or add an extra condition for looping

score 7 · Accepted Answer · answered Feb 12 '16 at 22:12

7

Use Resume to... well... resume processing after the error handler.

Sub TestError()
    On Error GoTo errError

    'Code that should raise an error and send to errError
    Excel.Application.Workbooks.Open "lakdfjldkj"
    Exit Sub
errError:
    MsgBox "Code didn't break"
    Resume
End Sub

I have used code like this to access a certain worksheet, if it is not found then the error handler creates one with the correct name and then resumes the processing.

answered Feb 12 '16 at 22:12

This worked! I kept the label in and I guess I have to use Resume instead of GoTo to go to a specific line – kainC Feb 12 '16 at 22:26
1

Old sub but comment to help others: This almost worked for me but my error was inside a loop and repeated the error _ad infitum_ - `Resume Next` fixed the issue. – ChemEnger Nov 11 '20 at 10:00

score 2 · Answer 2 · answered Feb 12 '16 at 22:11

2

You need to clear the error message by using:

Err.Clear

Source: https://msdn.microsoft.com/en-us/library/hh2zczch(v=vs.90).aspx

Basically it still thinks you're handling the error, and hasn't reset the handling mechanism, so it doesn't re-trigger until you clear it.

(Resume also clears the Error as well, as you can note by the reference above).

answered Feb 12 '16 at 22:11

Kris B

436
2
3

1

Err.clear didn't work, but replacing GoTo with Resume seemed to clear it – kainC Feb 12 '16 at 22:27
1

It is unwise to use `Err.Clear` within an error handler... using `Err.Clear` in lieu of `Resume` will cause unexpected behavior. Per the documentation you linked to, "the `Clear` method is called automatically [after]... any type of `Resume` statement. – ARich Feb 13 '16 at 06:16

score 0 · Answer 3 · answered Oct 31 '22 at 12:08

0

I have tested, it is possible to type :

Resume label1 (line label)

Resume next

Resume 110 (line number)

answered Oct 31 '22 at 12:08

ahmad pj

41
1
3

Error handling only works once

3 Answers3

Linked