CODEWITHSUNDEEP
vbaerror-handling
Ton Plomp
Ton Plomp

Reputation: 3105

VBA Error handler exits inner function on second error

I have a simple piece of code that if it fails should retry (after a small wait). Here's the basic setup:
Main call Bar which calls Foo. Both Foo and Bar have a simple On Error handler which shows a message box. In Foo the error handler also has a GoTo to the start of the Function. Here's the code:

Sub main()
Bar
End Sub
Function Bar()
    On Error GoTo ErrHandBar
    Foo
ErrHandBar:
    MsgBox ("ErrhandBar")
End Function
Function Foo()
    On Error GoTo ErrhandFoo
Retry:
    x = y / 0
ErrhandFoo:
    MsgBox ("ErrhandFoo")
    Application.Wait (Now + TimeValue("0:00:5"))
    GoTo Retry
End Function

If I run Main step-by-step it shows me that ErrHandFoo only get's called once. On the second error it call ErrHandFoo.
I am not looking for other programming techniques, I merely want to understand why this happens!
Some details:
VBA 7.0.1619 on Excel 2010 (14.0.6106.5005 32 bit)

Upvotes: 0

Views: 199

Answers (1)

djikay
djikay

Reputation: 10628

The likely reason for this behaviour is that you haven't "resumed" from the first error. You can't throw an error from within an error handler because, after the first error happens, you're effectively in the error handler for foo.

You should replace your GoTo with a Resume, like the following, so VBA no longer thinks you are inside the error handler:

...
ErrhandFoo:
    MsgBox ("ErrhandFoo")
    Application.Wait (Now + TimeValue("0:00:5"))
    Resume Retry ' NOT GoTo
End Function

Upvotes: 2

Related Questions