在 VBA 中处理错误时如何管理无错误情况？

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

提问by M3HD1

I need to catch some VBA errors using the GoTostatement:

我需要使用以下GoTo语句捕获一些 VBA 错误：

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 errorHandlersection is executed.
I found this discussionbut the answer doesn't solve my issue.
I tried adding an Exit Substatement as explained :

问题在于，当没有错误时，errorHandler将执行该部分。
我找到了这个讨论，但答案并没有解决我的问题。
我尝试Exit Sub按照说明添加一条语句：

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 errorHandleris executed even when no errors occur.

但仍然是同样的问题：errorHandler即使没有发生错误也会执行。

回答by Fionnuala

Just put Exit sub in.

只需将 Exit sub 放入即可。

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

myHandler:
MsgBox "EROOR !"

err.clear
End Sub

回答by phoog

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 Resumeclears the error. I like this pattern for a few reasons:

请注意，Resume清除错误。我喜欢这种模式有几个原因：

1. Habitually inserting the exit block beforethe 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 ExitLabelfor anyearly 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
   

1. 习惯性地在错误处理块之前插入 exit 块可以减少我遇到 OP 意外掉入错误处理程序的问题的机会。

2. 我使用GoTo ExitLabel的任何从Sub或Function提前退场。这样，我就不太可能意外跳过清理代码。例子：

   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
   

回答by Alpha

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

回答by anurag

Use below code in error handler section:

在错误处理程序部分使用以下代码：

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

回答by quinneverett

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.

我和你遇到了完全相同的问题，上面的解决方案不起作用。他们显然甚至没有看到您在原始帖子的 2 个不同地方已经写了 Exit Sub。似乎没有任何在线网站理解有时不会出现错误（如果总是会出现错误，您为什么要那样编码？），而当没有错误时，您显然不会想退出子。您也不希望 myHandler 在没有错误时运行。呸！这是我想出的似乎有效的解决方案。

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

回答by Mark1000000.01

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

我在 ErrorHandler 中使用 If 语句，如果没有错误，它将停止执行。这是通过使用 Err.Number (Err (object) number (eg Run-time error 9: Subscript out of range)) 来实现的

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

回答by vthiep94

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

回答by Gagan

sub XYZ ()
on error goto label

"Write your macro"

Label:
      If Err.Description <> "" Then
       "your code if error occurs for eg:"
       msgbox "Error Occured!"
       Exit Sub
       Else
       "Your code when no error occurs for eg"
        msgbox " Done."
       End If
Exit Sub