How to manage the no error case when handling errors in VBA?

Question

I need to catch some VBA errors using the GoTo statement:

Sub mySub
 On Error GoTo errorHandler:
    Workbooks.Open("myWorkbook")
'
' Some Code
'
errorHandler:
MsgBox "ERROR"

End Sub

The problem is that when there is no error the errorHandler section is executed.
I found this discussion but the answer doesn't solve my issue.
I tried adding an Exit Sub statement as explained :

Sub mySub
 On Error GoTo errorHandler:
    Workbooks.Open("myWorkbook")
    Exit Sub

'
' Some Code
'
errorHandler:
  MsgBox "ERROR"

End Sub

In this case it exits the method when there is no error. I also tried :

 Sub mySub
 On Error GoTo errorHandler:
    Workbooks.Open("myWorkbook")
'
' Some Code
'
errorHandler:
  MsgBox "ERROR"
  Exit Sub
End Sub

But still the same issue: The errorHandler is executed even when no errors occur.

Solution 1

Just put Exit sub in.

Sub mySub
 On Error GoTo myHandler:
    Workbooks.Open("myWorkbook")
'
' Some Code
'
Exit sub

myHandler:
MsgBox "EROOR !"

err.clear
End Sub

Solution 2

Here's the pattern I prefer:

Sub SomeSub()
    On Error GoTo ErrorLabel

    'Code goes here

ExitLabel:
   'Clean-up code, if any, goes here 
   Exit Sub

ErrorLabel:
    'Error-handling code goes here
    Resume ExitLabel
End Sub

Note that Resume clears the error. I like this pattern for a few reasons:

1. Habitually inserting the exit block before the error-handling block reduces the chance that I'll have the OP's problem of accidentally dropping into the error handler.

2. I use GoTo ExitLabel for any early exit from the Sub or Function. This way, I'm less likely to skip the clean-up code by accident. Example:

   Sub SomeOtherSub()
       Dim x As ResourceThatNeedsToBeClosed
       Dim i As Long
       On Error GoTo ErrorLabel
       Set x = GetX
       For i = 1 To 100
           If x.SomeFunction(i) Then
               GoTo ExitLabel
           End If
       Next
   ExitLabel:
       x.Close
   ErrorLabel:
       'Error-handling code goes here
       Resume ExitLabel
   End Sub
   

Solution 3

Public Sub MySub
    On Error Goto Skip

    ' Some Codes

Skip:
    If err.Number > 0 Then

        ' Run whatever codes if error occurs

        err.Clear
    End If
    On Error Goto 0
End Su

Comments

[Marco]

Can you add the line On Error Goto 0? So OP understands that it's a good practice resetting error redirects... Upvoted :)

[Fionnuala]

@Marco Do you mean err.Clear? This is VBA.

[M3HD1]

@Marco should i reset it after each catching bloc ? On Error GoTo myHandler: Workbooks.Open("myWorkbook") On Error GoTo 0

[Marco]

@Remou: maybe I'm wrong, but I remember I used (many years ago) On Error Goto 0 to make code behave without error checking. And I remember it was a good practice to use this instruction to avoid unpredictable jumps for later errors... I searched Google to see if I was wrong, but here I found an article giving me On Error Goto 0...

[Marco]

@M3HD1: no, you should reset when you're sure you cannot have other errors (when you don't want to manage them) and before exiting sub. Don't take my advice as a rule because it's not... it's something I remember as a good practice to avoid headaches....

[Fionnuala]

@Marco You can use On Error GoTo 0, but it is not standard practice with an error handler, as you can see in the example posted in your link.

[Jean-François Corbett]

@Marco: I see no reason to use On Error GoTo 0, and I've never seen it in an error handler either... I've seen it in an ExitProcedure: type thing (example) to avoid infinite error handling "loops".

[Steve Rindsberg]

On Error GoTo 0 turns error handling OFF, tells VB/VBA to stop and throw up an error message if an error is encountered. It also (implicitly) clears the error condition. In a simple case like this, there's probably no disadvantage to using it but in other cases where you'd want to trap an error, deal with it then resume processing, you definitely wouldn't want to use GoTo 0. And even in this simple case, there's no advantage to it; on the whole, a bad habit to get into, I'd say.

[phoog]

@SteveRindsberg On Error Goto 0 doesn't always throw up an error message; if there is an On Error statement in effect anywhere up the call stack, that error handler will kick in first.

[Fionnuala]

Yes, I prefer this too. It seems to be the standard for apps that automatically insert error handling blocks as well. I even have a little code to remove and to create outline error handling in this format.

[41686d6564 stands w. Palestine]

The accepted answer should work whether there's an error or not, and this is actually the right way to handle errors in VBA. On the other hand, using GoTo to move around code is considered very bad practice, especially if you will be using multiple GoTo statements.

[41686d6564 stands w. Palestine]

The OP put Exit Sub in two wrong different places. You just need to put it in the right place (i.e right before your error handling code).

[Nico Haase]

Please add some explanation to your answer such that others can learn from it

[Brian]

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply.