VBA 错误处理的好模式

Good Patterns For VBA Error Handling

提问by jwoolard

What are some good patterns for error handling in VBA?

VBA 中的错误处理有哪些好的模式？

In particular, what should I do in this situation:

特别是在这种情况下我应该怎么做：

... some code ...
... some code where an error might occur ...
... some code ...
... some other code where a different error might occur ...
... some other code ...
... some code that must always be run (like a finally block) ...

I want to handle both errors, and resume execution after the code where the error may occur. Also, the finally code at the end must ALWAYS run - no matter what exceptions are thrown earlier. How can I achieve this outcome?

我想处理这两个错误，并在可能发生错误的代码之后恢复执行。此外，最后的 finally 代码必须始终运行 - 无论之前抛出什么异常。我怎样才能达到这个结果？

回答by guillermooo

Error Handling in VBA

VBA 中的错误处理

• On Error GotoErrorHandlerLabel
• Resume(Next| ErrorHandlerLabel)
• On Error Goto 0(disables current error handler)
• Errobject

• On Error Goto错误处理程序标签
• Resume( Next| ErrorHandlerLabel)
• On Error Goto 0（禁用当前的错误处理程序）
• Err目的

The Errobject's properties are normally reset to zero or a zero-length string in the error handling routine, but it can also be done explicitly with Err.Clear.

该Err对象的属性通常被重置为零或错误处理程序零长度字符串，但它也可以用显式执行Err.Clear。

Errors in the error handling routine are terminating.

错误处理例程中的错误正在终止。

The range 513-65535 is available for user errors. For custom class errors, you add vbObjectErrorto the error number. See MS documentation about Err.Raiseand the list of error numbers.

范围 513-65535 可用于用户错误。对于自定义类错误，您添加vbObjectError到错误编号。请参阅有关错误编号的MS 文档Err.Raise和列表。

For not implemented interface members in a derivedclass, you should use the constant E_NOTIMPL = &H80004001.

对于派生类中未实现的接口成员，您应该使用常量E_NOTIMPL = &H80004001。

---

---

Option Explicit

Sub HandleError()
  Dim a As Integer
  On Error GoTo errMyErrorHandler
    a = 7 / 0
  On Error GoTo 0

  Debug.Print "This line won't be executed."

DoCleanUp:
  a = 0
Exit Sub
errMyErrorHandler:
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
Resume DoCleanUp
End Sub

Sub RaiseAndHandleError()
  On Error GoTo errMyErrorHandler
    ' The range 513-65535 is available for user errors.
    ' For class errors, you add vbObjectError to the error number.
    Err.Raise vbObjectError + 513, "Module1::Test()", "My custom error."
  On Error GoTo 0

  Debug.Print "This line will be executed."

Exit Sub
errMyErrorHandler:
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
  Err.Clear
Resume Next
End Sub

Sub FailInErrorHandler()
  Dim a As Integer
  On Error GoTo errMyErrorHandler
    a = 7 / 0
  On Error GoTo 0

  Debug.Print "This line won't be executed."

DoCleanUp:
  a = 0
Exit Sub
errMyErrorHandler:
  a = 7 / 0 ' <== Terminating error!
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
Resume DoCleanUp
End Sub

Sub DontDoThis()

  ' Any error will go unnoticed!
  On Error Resume Next
  ' Some complex code that fails here.
End Sub

Sub DoThisIfYouMust()

  On Error Resume Next
  ' Some code that can fail but you don't care.
  On Error GoTo 0

  ' More code here
End Sub

回答by Joel Goodwin

I would also add:

我还要补充：

• The global Errobject is the closest you have to an exception object
• You can effectively "throw an exception" with Err.Raise

• 全局Err对象是最接近异常对象的对象
• 您可以有效地“抛出异常” Err.Raise

And just for fun:

只是为了好玩：

• On Error Resume Nextis the devil incarnate and to be avoided, as it silently hides errors

• On Error Resume Next是魔鬼的化身，要避免，因为它默默地隐藏错误

回答by Johnno Nolan

So you could do something like this

所以你可以做这样的事情

Function Errorthingy(pParam)
On Error GoTo HandleErr

 ' your code here

    ExitHere:
    ' your finally code
    Exit Function

    HandleErr:
        Select Case Err.Number
        ' different error handling here'
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "ErrorThingy"
        End Select


   Resume ExitHere

End Function

---

---

If you want to bake in custom exceptions. (e.g. ones that violate business rules) use the example above but use the goto to alter the flow of the method as necessary.

如果你想在自定义异常中烘焙。（例如，违反业务规则的那些）使用上面的示例，但根据需要使用 goto 更改方法的流程。

回答by LimaNightHawk

Here's my standard implementation. I like the labels to be self-descriptive.

这是我的标准实现。我喜欢标签是自我描述的。

Public Sub DoSomething()

    On Error GoTo Catch ' Try
    ' normal code here

    Exit Sub
Catch:

    'error code: you can get the specific error by checking Err.Number

End Sub

Or, with a Finallyblock:

或者，使用一个Finally块：

Public Sub DoSomething()

    On Error GoTo Catch ' Try

    ' normal code here

    GoTo Finally
Catch:

    'error code

Finally:

    'cleanup code

End Sub

回答by Dick Kusleika

Professional Excel Developmenthas a pretty good error handling scheme. If you're going to spend any time in VBA, it's probably worth getting the book. There are a number of areas where VBA is lacking and this book has good suggestions for managing those areas.

Professional Excel Development有一个很好的错误处理方案。如果您打算在 VBA 中花费任何时间，那么这本书可能是值得的。VBA 在许多领域都缺乏，本书对管理这些领域提出了很好的建议。

PED describes two error handling methods. The main one is a system where all entry point procedures are subprocedures and all other procedures are functions that return Booleans.

PED 描述了两种错误处理方法。主要的是一个系统，其中所有入口点过程都是子过程，所有其他过程都是返回布尔值的函数。

The entry point procedure use On Error statements to capture errors pretty much as designed. The non-entry point procedures return True if there were no errors and False if there were errors. Non-entry point procedures also use On Error.

入口点过程使用 On Error 语句来捕获几乎按照设计的错误。如果没有错误，非入口点过程返回 True，如果有错误，则返回 False。非入口点过程也使用 On Error。

Both types of procedures use a central error handling procedure to keep the error in state and to log the error.

两种类型的过程都使用中央错误处理过程来保持错误状态并记录错误。

回答by Thiago Cardoso

I use a piece of code that i developed myself and it is pretty good for my codes:

我使用了一段我自己开发的代码，它对我的​​代码来说非常好：

In the beginning of the function or sub, I define:

在函数或子函数的开头，我定义：

On error Goto ErrorCatcher:

and then, I handle the possible errors

然后，我处理可能的错误

ErrorCatcher:
Select Case Err.Number

Case 0 'exit the code when no error was raised
    On Error GoTo 0
    Exit Function
Case 1 'Error on definition of object
    'do stuff
Case... 'little description here
    'do stuff
Case Else
    Debug.Print "###ERROR"
    Debug.Print "   ? Number  :", Err.Number
    Debug.Print "   ? Descrip :", Err.Description
    Debug.Print "   ? Source  :", Err.Source
    Debug.Print "   ? HelpCont:", Err.HelpContext
    Debug.Print "   ? LastDLL :", Err.LastDllError
    Stop
    Err.Clear
    Resume
End Select

回答by whistle britches

Here's a pretty decent pattern.

这是一个相当不错的模式。

For debugging: When an error is raised, hit Ctrl-Break (or Ctrl-Pause), drag the break marker (or whatever it's called) down to the Resume line, hit F8 and you'll step to the line that "threw" the error.

用于调试：当出现错误时，按 Ctrl-Break（或 Ctrl-Pause），将中断标记（或其他任何名称）拖到 Resume 行，按 F8，您将移至“抛出”的行错误。

The ExitHandler is your "Finally".

ExitHandler 是您的“最终”。

Hourglass will be killed every time. Status bar text will be cleared every time.

沙漏每次都会被杀死。每次都会清除状态栏文本。

Public Sub ErrorHandlerExample()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler
    Dim varRetVal As Variant

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SomeTable", dbOpenDynaset, dbSeeChanges + dbFailOnError)

    Call DoCmd.Hourglass(True)

    'Do something with the RecordSet and close it.

    Call DoCmd.Hourglass(False)

ExitHandler:
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

ErrHandler:
    Call DoCmd.Hourglass(False)
    Call DoCmd.SetWarnings(True)
    varRetVal = SysCmd(acSysCmdClearStatus)

    Dim errX As DAO.Error
    If Errors.Count > 1 Then
       For Each errX In DAO.Errors
          MsgBox "ODBC Error " & errX.Number & vbCrLf & errX.Description
       Next errX
    Else
        MsgBox "VBA Error " & Err.Number & ": " & vbCrLf & Err.Description & vbCrLf & "In: Form_MainForm", vbCritical
    End If

    Resume ExitHandler
    Resume

End Sub



    Select Case Err.Number
        Case 3326 'This Recordset is not updateable
            'Do something about it. Or not...
        Case Else
            MsgBox "VBA Error " & Err.Number & ": " & vbCrLf & Err.Description & vbCrLf & "In: Form_MainForm", vbCritical
    End Select

It also traps for both DAO and VBA errors. You can put a Select Case in the VBA error section if you want to trap for specific Err numbers.

它还捕获 DAO 和 VBA 错误。如果您想捕获特定的 Err 编号，您可以在 VBA 错误部分放置一个 Select Case。

Select Case Err.Number
    Case 3326 'This Recordset is not updateable
        'Do something about it. Or not...
    Case Else
        MsgBox "VBA Error " & Err.Number & ": " & vbCrLf & Err.Description & vbCrLf & "In: Form_MainForm", vbCritical
End Select

回答by nickD

The code below shows an alternative that ensures there is only one exit point for the sub/function.

下面的代码显示了一种替代方法，可确保子/函数只有一个退出点。

sub something()
    on error goto errHandler

    ' start of code
    ....
    ....
    'end of code

    ' 1. not needed but signals to any other developer that looks at this
    ' code that you are skipping over the error handler...
    ' see point 1...
    err.clear

errHandler:
    if err.number <> 0 then
        ' error handling code
    end if
end sub

回答by Chip Pearson

Also relevant to the discussion is the relatively unknown Erlfunction. If you have numeric labels within your code procedure, e.g.,

与讨论相关的是相对未知的Erl函数。如果您的代码过程中有数字标签，例如，

Sub AAA()
On Error Goto ErrorHandler

1000:
' code
1100:
' more code
1200:
' even more code that causes an error
1300:
' yet more code
9999: ' end of main part of procedure
ErrorHandler:
If Err.Number <> 0 Then
   Debug.Print "Error: " + CStr(Err.Number), Err.Descrption, _
      "Last Successful Line: " + CStr(Erl)
End If   
End Sub 

The Erlfunction returns the most recently encountered numberic line label. In the example above, if a run-time error occurs after label 1200:but before 1300:, the Erlfunction will return 1200, since that is most recenlty sucessfully encountered line label. I find it to be a good practice to put a line label immediately above your error handling block. I typcially use 9999to indicate that the main part of the procuedure ran to its expected conculsion.

该Erl函数返回最近遇到的数字行标签。在上面的示例中，如果在 label 之后1200:但之前发生运行时错误1300:，则该Erl函数将返回1200，因为这是最近成功遇到的 line label 。我发现在错误处理块的正上方放置一个行标签是一个很好的做法。我通常9999用来表示程序的主要部分达到了预期的效果。

NOTES:

笔记：

• Line labels MUST be positive integers -- a label like MadeItHere:isn't recogonized by Erl.

• Line labels are completely unrelated to the actual line numbers of a VBIDE CodeModule. You can use any positive numbers you want, in any order you want. In the example above, there are only 25 or so lines of code, but the line label numbers begin at 1000. There is no relationship between editor line numbers and line label numbers used with Erl.

• Line label numbers need not be in any particular order, although if they are not in ascending, top-down order, the efficacy and benefit of Erlis greatly diminished, but Erlwill still report the correct number.

• Line labels are specific to the procedure in which they appear. If procedure ProcAcalls procedure ProcBand an error occurs in ProcBthat passes control back to ProcA, Erl(in ProcA) will return the most recently encounterd line label number in ProcAbefore it calls ProcB. From within ProcA, you cannot get the line label numbers that might appear in ProcB.

• 行标签必须是正整数——像这样的标签MadeItHere:不能被 识别Erl。

• 行标签与 a 的实际行号完全无关VBIDE CodeModule。您可以按您想要的任何顺序使用任何您想要的正数。在上面的示例中，只有 25 行左右的代码，但行标签编号从1000. 编辑器行号和与 一起使用的行标签号之间没有关系Erl。

• 行标签编号不必按任何特定顺序排列，尽管如果它们不是升序、自上而下的顺序，则其功效和益处Erl会大大降低，但Erl仍会报告正确的编号。

• 行标签特定于它们出现的过程。如果过程ProcA调用过程ProcB并且发生错误ProcB，将控制传递回ProcA，Erl(in ProcA) 将在ProcA调用之前返回最近遇到的行标签号ProcB。从内部ProcA，您无法获得可能出现在ProcB.

Use care when putting line number labels within a loop. For example,

在循环中放置行号标签时要小心。例如，

For X = 1 To 100
500:
' some code that causes an error
600:
Next X

If the code following line label 500but before 600causes an error, and that error arises on the 20th iteration of the loop, Erlwill return 500, even though 600has been encounterd successfully in the previous 19 interations of the loop.

如果标签行之后500但之前的代码600导致错误，并且该错误出现在循环的第 20 次迭代中，即使在循环的前 19次迭代中已成功遇到，也Erl将返回。500600

Proper placement of line labels within the procedure is critical to using the Erlfunction to get truly meaningful information.

在程序中正确放置线标签对于使用该Erl功能获取真正有意义的信息至关重要。

There are any number of free utilies on the net that will insert numeric line label in a procedure automatically, so you have fine-grained error information while developing and debugging, and then remove those labels once code goes live.

网络上有许多免费的实用程序会自动在程序中插入数字行标签，因此您在开发和调试时可以获得细粒度的错误信息，然后在代码上线后删除这些标签。

If your code displays error information to the end user if an unexpected error occurs, providing the value from Erlin that information can make finding and fixing the problem VASTLY simpler than if value of Erlis not reported.

如果您的代码在发生意外错误时向最终用户显示错误信息，则提供该Erl信息中的值可以使查找和修复问题比Erl不报告值简单得多。

回答by igorsp7

I find the following to work best, called the central error handling approach.

我发现以下最有效，称为中央错误处理方法。

Benefits

好处

You have 2 modes of running your application: Debugand Production. In the Debugmode, the code will stop at any unexpected error and allow you to debug easily by jumping to the line where it occurred by pressing F8 twice. In the Productionmode, a meaningful error message will get displayed to the user.

您有 2 种运行应用程序的模式：Debug和Production。在调试模式下，代码将在任何意外错误处停止，并允许您通过按两次 F8 跳转到发生错误的行来轻松调试。在生产模式下，将向用户显示有意义的错误消息。

You can throw intentional errors like this, which will stop execution of the code with a message to the user:

您可以像这样故意抛出错误，这将停止执行代码并向用户发送消息：

Err.Raise vbObjectError, gsNO_DEBUG, "Some meaningful error message to the user"

Err.Raise vbObjectError, gsUSER_MESSAGE, "Some meaningful non-error message to the user"

'Or to exit in the middle of a call stack without a message:
Err.Raise vbObjectError, gsSILENT

Implementation

执行

You need to "wrap" all subroutines and functions with any significant amount of code with the following headers and footers, making sure to specify ehCallTypeEntryPointin all your entry points. Note the msModuleconstant as well, which needs to be put in all modules.

您需要使用具有以下页眉和页脚的任何大量代码“包装”所有子例程和函数，确保ehCallTypeEntryPoint在所有入口点中指定。还要注意msModule常量，它需要放在所有模块中。

Option Explicit
Const msModule As String = "<Your Module Name>"

' This is an entry point 
Public Sub AnEntryPoint()
    Const sSOURCE As String = "AnEntryPoint"
    On Error GoTo ErrorHandler

    'Your code

ErrorExit:
    Exit Sub

ErrorHandler:
    If CentralErrorHandler(Err, ThisWorkbook, msModule, sSOURCE, ehCallTypeEntryPoint) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Sub

' This is any other subroutine or function that isn't an entry point
Sub AnyOtherSub()
    Const sSOURCE As String = "AnyOtherSub"
    On Error GoTo ErrorHandler

    'Your code

ErrorExit:
    Exit Sub

ErrorHandler:
    If CentralErrorHandler(Err, ThisWorkbook, msModule, sSOURCE) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Sub

The contents of the central error handler module is the following:

中央错误处理模块的内容如下：

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: Error handler code.
'
'           Run SetDebugMode True to use debug mode (Dev mode)
'           It will be False by default (Production mode)
'
' Author:   Igor Popov
' Date:     13 Feb 2014
' Licence:  MIT
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit
Option Private Module

Private Const msModule As String = "MErrorHandler"

Public Const gsAPP_NAME As String = "<You Application Name>"

Public Const gsSILENT As String = "UserCancel"  'A silent error is when the user aborts an action, no message should be displayed
Public Const gsNO_DEBUG As String = "NoDebug"   'This type of error will display a specific message to the user in situation of an expected (provided-for) error.
Public Const gsUSER_MESSAGE As String = "UserMessage" 'Use this type of error to display an information message to the user

Private Const msDEBUG_MODE_COMPANY = "<Your Company>"
Private Const msDEBUG_MODE_SECTION = "<Your Team>"
Private Const msDEBUG_MODE_VALUE = "DEBUG_MODE"

Public Enum ECallType
    ehCallTypeRegular = 0
    ehCallTypeEntryPoint
End Enum

Public Function DebugMode() As Boolean
    DebugMode = CBool(GetSetting(msDEBUG_MODE_COMPANY, msDEBUG_MODE_SECTION, msDEBUG_MODE_VALUE, 0))
End Function

Public Sub SetDebugMode(Optional bMode As Boolean = True)
    SaveSetting msDEBUG_MODE_COMPANY, msDEBUG_MODE_SECTION, msDEBUG_MODE_VALUE, IIf(bMode, 1, 0)
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: The central error handler for all functions
'           Displays errors to the user at the entry point level, or, if we're below the entry point, rethrows it upwards until the entry point is reached
'
'           Returns True to stop and debug unexpected errors in debug mode.
'
'           The function can be enhanced to log errors.
'
' Date          Developer           TDID    Comment
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 13 Feb 2014   Igor Popov                  Created

Public Function CentralErrorHandler(ErrObj As ErrObject, Wbk As Workbook, ByVal sModule As String, ByVal sSOURCE As String, _
                                    Optional enCallType As ECallType = ehCallTypeRegular, Optional ByVal bRethrowError As Boolean = True) As Boolean

    Static ssModule As String, ssSource As String
    If Len(ssModule) = 0 And Len(ssSource) = 0 Then
        'Remember the module and the source of the first call to CentralErrorHandler
        ssModule = sModule
        ssSource = sSOURCE
    End If
    CentralErrorHandler = DebugMode And ErrObj.Source <> gsNO_DEBUG And ErrObj.Source <> gsUSER_MESSAGE And ErrObj.Source <> gsSILENT
    If CentralErrorHandler Then
        'If it's an unexpected error and we're going to stop in the debug mode, just write the error message to the immediate window for debugging
        Debug.Print "#Err: " & Err.Description
    ElseIf enCallType = ehCallTypeEntryPoint Then
        'If we have reached the entry point and it's not a silent error, display the message to the user in an error box
        If ErrObj.Source <> gsSILENT Then
            Dim sMsg As String: sMsg = ErrObj.Description
            If ErrObj.Source <> gsNO_DEBUG And ErrObj.Source <> gsUSER_MESSAGE Then sMsg = "Unexpected VBA error in workbook '" & Wbk.Name & "', module '" & ssModule & "', call '" & ssSource & "':" & vbCrLf & vbCrLf & sMsg
            MsgBox sMsg, vbOKOnly + IIf(ErrObj.Source = gsUSER_MESSAGE, vbInformation, vbCritical), gsAPP_NAME
        End If
    ElseIf bRethrowError Then
        'Rethrow the error to the next level up if bRethrowError is True (by Default).
        'Otherwise, do nothing as the calling function must be having special logic for handling errors.
        Err.Raise ErrObj.Number, ErrObj.Source, ErrObj.Description
    End If
End Function

To set yourself in the Debugmode, run the following in the Immediate window:

要将自己设置为调试模式，请在立即窗口中运行以下命令：

SetDebugMode True