How to manage the no error case when handling erro

问题:

I need to catch some VBA error using the GoTo statement like this :

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.

回答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

回答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
   

回答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

回答4:

I am having the exact same issue as you, and the solutions above did not work. They clearly didn't even see you wrote Exit Sub in already in 2 different places in your original post. No site online seems to understand that sometimes there won't be an error (if there was always going to be an error, why did you code it that way?), and when there isn't an error, you obviously don't want to Exit Sub. Nor do you want the myHandler to run when there isn't an error. DUH! This is the solution I cam up with which seems to work.

On Error GoTo ErrorHandler
'This is the thing I am trying to do...
Workbooks("SpreadSolver.xlsb").Activate
'If it works, great. 
'Don't touch the error stuff and move on. 
'I.e. go to the part that I know works (the rest of the macro)
GoTo ThisPartWorks

'If the thing I am trying to do doesn't work...
ErrorHandler:
MsgBox "Error: Please open Spread Solver and then run the macro."
'Only want to Exit Sub if there is an error.. duh.
Exit Sub

ThisPartWorks:
'the rest of your macro can go here...
'...
End Sub

回答5:

I use an If statement, within the ErrorHandler, which will stop execution if there is no error. This is achieved by using the Err.Number (Err (object) number (e.g. Run-time error 9: Subscript out of range))

If Err.Number >= 1 Then
MsgBox ("Message")
End
Else: other code
End If
Exit Sub

回答6:

This is what I have done. Works like a charm

Sub someProgram ()
    On Error goto Handler:
        x = 7/0

    'Some code you wanna execute  with or without error
Exit Sub

Handler:
     'Write your handler here

Resume next 'After handling error, this line will help you resume the next line

End sub

回答7:

Use below code in error handler section:

if err.number>0 the
    ' error handling goes here
else
    ' some code
end if