CODEWITHSUNDEEP
vbaexcel
kainC
kainC

Reputation: 400

Error handling only works once

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

Upvotes: 5

Views: 11266

Answers (3)

ahmad pj
ahmad pj

Reputation: 51

I have tested, it is possible to type :

Resume label1 (line label)

Resume next

Resume 110 (line number)

Upvotes: 0

user4039065
user4039065

Reputation:

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.

Upvotes: 7

Kris B
Kris B

Reputation: 446

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).

Upvotes: 2

Related Questions