正确处理 VBA (Excel) 中的错误

Properly Handling Errors in VBA (Excel)

提问by skofgar

I've been working with VBA for quite a while now, but I'm still not so sure about Error Handling.

我已经使用 VBA 有一段时间了，但我仍然不太确定错误处理。

A good article is the one of CPearson.com

一篇好文章是 CPearson.com 的一篇

However I'm still wondering if the way I used to do ErrorHandling was/is completely wrong: Block 1

但是我仍然想知道我过去做 ErrorHandling 的方式是否完全错误： Block 1

On Error Goto ErrCatcher
   If UBound(.sortedDates) > 0 Then

       // Code

   Else
ErrCatcher:
       // Code

   End If

The if clause, because if it is true, will be executed and if it fails the Goto will go into the Else-part, since the Ubound of an Array should never be zero or less, without an Error, this method worked quite well so far.

if 子句，因为如果它为真，将被执行，如果它失败，Goto 将进入 Else 部分，因为数组的 Ubound 永远不应该为零或更少，没有错误，这种方法工作得很好，所以远的。

If I understood it right it should be like this:

如果我理解正确，它应该是这样的：

Block 2

第 2 座

On Error Goto ErrCatcher
    If Ubound(.sortedDates) > 0 Then

       // Code
    End If

    Goto hereX

ErrCatcher:
       //Code
    Resume / Resume Next / Resume hereX

hereX:

Or even like this: Block 3

甚至像这样： Block 3

On Error Goto ErrCatcher
    If Ubound(.sortedDates) > 0 Then

       // Code
    End If

ErrCatcher:
    If Err.Number <> 0 then
       //Code
    End If

The most common way I see is that one, that the Error "Catcher" is at the end of a sub and the Sub actually ends before with a "Exit Sub", but however isn't it a little confusing if the Sub is quite big if you jump vice versa to read through the code?

我看到的最常见的方法是，错误“Catcher”位于子程序的末尾，而子程序实际上以“退出子程序”结束，但是如果子程序非常复杂，会不会有点混乱大如果你跳转反之亦然阅读代码？

Block 4

第 4 座

Source of the following Code: CPearson.com

以下代码来源： CPearson.com

  On Error Goto ErrHandler:
   N = 1 / 0    ' cause an error
   '
   ' more code
   '
  Exit Sub

  ErrHandler:

   ' error handling code'

   Resume Next

End Sub 

Should it be like in Block 3 ?

应该像第 3 块那样吗？

采纳答案by osknows

I definitely wouldn't use Block1. It doesn't seem right having the Error block in an IF statement unrelated to Errors.

我绝对不会使用 Block1。在与 Errors 无关的 IF 语句中包含 Error 块似乎不正确。

Blocks 2,3 & 4 I guess are variations of a theme. I prefer the use of Blocks 3 & 4 over 2 only because of a dislike of the GOTO statement; I generally use the Block4 method. This is one example of code I use to check if the Microsoft ActiveX Data Objects 2.8 Library is added and if not add or use an earlier version if 2.8 is not available.

我猜块 2,3 和 4 是一个主题的变体。我更喜欢使用 Blocks 3 & 4 而不是 2，因为不喜欢 GOTO 语句；我一般使用Block4方法。这是我用来检查是否添加了 Microsoft ActiveX 数据对象 2.8 库以及如果没有添加或在 2.8 不可用时使用较早版本的代码示例。

Option Explicit
Public booRefAdded As Boolean 'one time check for references

Public Sub Add_References()
Dim lngDLLmsadoFIND As Long

If Not booRefAdded Then
    lngDLLmsadoFIND = 28 ' load msado28.tlb, if cannot find step down versions until found

        On Error GoTo RefErr:
            'Add Microsoft ActiveX Data Objects 2.8
            Application.VBE.ActiveVBProject.references.AddFromFile _
            Environ("CommonProgramFiles") + "\System\ado\msado" & lngDLLmsadoFIND & ".tlb"

        On Error GoTo 0

    Exit Sub

RefErr:
        Select Case Err.Number
            Case 0
                'no error
            Case 1004
                 'Enable Trust Centre Settings
                 MsgBox ("Certain VBA References are not available, to allow access follow these steps" & Chr(10) & _
                 "Goto Excel Options/Trust Centre/Trust Centre Security/Macro Settings" & Chr(10) & _
                 "1. Tick - 'Disable all macros with notification'" & Chr(10) & _
                 "2. Tick - 'Trust access to the VBA project objects model'")
                 End
            Case 32813
                 'Err.Number 32813 means reference already added
            Case 48
                 'Reference doesn't exist
                 If lngDLLmsadoFIND = 0 Then
                    MsgBox ("Cannot Find Required Reference")
                    End
                Else
                    For lngDLLmsadoFIND = lngDLLmsadoFIND - 1 To 0 Step -1
                           Resume
                    Next lngDLLmsadoFIND
                End If

            Case Else
                 MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
                End
        End Select

        On Error GoTo 0
End If
booRefAdded = TRUE
End Sub

回答by RolandTumble

You've got one truly marvelous answer from ray023, but your comment that it's probably overkill is apt. For a "lighter" version....

你从 ray023 那里得到了一个真正了不起的答案，但你说这可能是矫枉过正的评论是恰当的。对于“更轻”的版本......

Block 1is, IMHO, bad practice. As already pointed out by osknows, mixing error-handling with normal-path code is Not Good. For one thing, if a newerror is thrown while there's an Error condition in effect you will notget an opportunity to handle it (unless you're calling from a routine that also has an error handler, where the execution will "bubble up").

恕我直言，第 1 块是不好的做法。正如 osknows 已经指出的那样，将错误处理与正常路径代码混合在一起是不好的。一方面，如果在有效的错误条件下抛出新错误，您将没有机会处理它（除非您从一个也有错误处理程序的例程中调用，否则执行将“冒泡” ）。

Block 2looks like an imitation of a Try/Catch block. It should be okay, but it's not The VBA Way. Block 3is a variation on Block 2.

块 2看起来像是对 Try/Catch 块的模仿。应该没问题，但这不是 VBA 方式。Block 3是 Block 2 的变体。

Block 4is a bare-bones version of The VBA Way. I would stronglyadvise using it, or something like it, because it's what any other VBA programmer inherting the code will expect. Let me present a small expansion, though:

Block 4是 The VBA Way 的基本版本。我强烈建议使用它或类似的东西，因为这是任何其他继承代码的 VBA 程序员所期望的。不过，让我介绍一个小的扩展：

Private Sub DoSomething()
On Error GoTo ErrHandler

'Dim as required

'functional code that might throw errors

ExitSub:
    'any always-execute (cleanup?) code goes here -- analagous to a Finally block.
    'don't forget to do this -- you don't want to fall into error handling when there's no error
    Exit Sub

ErrHandler:
    'can Select Case on Err.Number if there are any you want to handle specially

    'display to user
    MsgBox "Something's wrong: " & vbCrLf & Err.Description

    'or use a central DisplayErr routine, written Public in a Module
    DisplayErr Err.Number, Err.Description

    Resume ExitSub
    Resume
End Sub

Note that second Resume. This is a trick I learned recently: It will neverexecute in normal processing, since the Resume <label>statement will send the execution elsewhere. It can be a godsend for debugging, though. When you get an error notification, choose Debug (or press Ctl-Break, then choose Debug when you get the "Execution was interrupted" message). The next (highlighted) statement will be either the MsgBoxor the following statement. Use "Set Next Statement" (Ctl-F9) to highlight the bare Resume, then press F8. This will show you exactlywhere the error was thrown.

注意第二个Resume。这是我最近学到的一个技巧：它永远不会在正常处理中执行，因为该Resume <label>语句会将执行发送到其他地方。不过，它可能是调试的天赐之物。当您收到错误通知时，选择“调试”（或按 Ctl-Break，然后在收到“执行被中断”消息时选择“调试”）。下一个（突出显示的）语句将是 theMsgBox或 the following statement。使用“设置下一个语句”（Ctl-F9）突出显示裸露的Resume，然后按 F8。这将向您显示错误发生的确切位置。

As to your objection to this format "jumping around", A) it's what VBA programmers expect, as stated previously, & B) your routines shouldbe short enough that it's not far to jump.

至于你反对这种“跳来跳去”的格式，A）这是 VBA 程序员所期望的，如前所述，B）你的例程应该足够短，不远就可以跳转。

回答by ray

Two main purposes for error handling:

错误处理的两个主要目的：

1. Trap errors you can predict but can't control the user from doing (e.g. saving a file to a thumb drive when the thumb drives has been removed)
2. For unexpected errors, present user with a form that informs them what the problem is. That way, they can relay that message to you and you might be able to give them a work-around while you work on a fix.

1. 您可以预测但无法控制用户执行的陷阱错误（例如，当拇指驱动器被移除时，将文件保存到拇指驱动器）
2. 对于意外错误，请向用户提供一个表格，告知他们问题所在。这样，他们可以将该消息传递给您，而您可以在进行修复时为他们提供解决方法。

So, how would you do this?

那么，你会怎么做呢？

First of all, create an error form to display when an unexpected error occurs.

首先，创建一个错误表单以在发生意外错误时显示。

It could look something like this (FYI: Mine is called frmErrors):

它可能看起来像这样（仅供参考：我的称为 frmErrors）：

Notice the following labels:

请注意以下标签：

• lblHeadline
• lblSource
• lblProblem
• lblResponse

• 大头条
• 资源
• lbl问题
• 响应

Also, the standard command buttons:

此外，标准命令按钮：

• Ignore
• Retry
• Cancel

• 忽略
• 重试
• 取消

There's nothing spectacular in the code for this form:

这种形式的代码没有什么特别之处：

Option Explicit

Private Sub cmdCancel_Click()
  Me.Tag = CMD_CANCEL
  Me.Hide
End Sub

Private Sub cmdIgnore_Click()
  Me.Tag = CMD_IGNORE
  Me.Hide
End Sub

Private Sub cmdRetry_Click()
  Me.Tag = CMD_RETRY
  Me.Hide
End Sub

Private Sub UserForm_Initialize()
  Me.lblErrorTitle.Caption = "Custom Error Title Caption String"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  'Prevent user from closing with the Close box in the title bar.
    If CloseMode <> 1 Then
      cmdCancel_Click
    End If
End Sub

Basically, you want to know which button the user pressed when the form closes.

基本上，您想知道用户在表单关闭时按下了哪个按钮。

Next, create an Error Handler Module that will be used throughout your VBA app:

接下来，创建一个将在整个 VBA 应用程序中使用的错误处理程序模块：

'****************************************************************
'    MODULE: ErrorHandler
'
'   PURPOSE: A VBA Error Handling routine to handle
'             any unexpected errors
'
'     Date:    Name:           Description:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'03/22/2010    Ray      Initial Creation
'****************************************************************
Option Explicit

Global Const CMD_RETRY = 0
Global Const CMD_IGNORE = 1
Global Const CMD_CANCEL = 2
Global Const CMD_CONTINUE = 3

Type ErrorType
    iErrNum As Long
    sHeadline As String
    sProblemMsg As String
    sResponseMsg As String
    sErrorSource As String
    sErrorDescription As String
    iBtnCap(3) As Integer
    iBitmap As Integer
End Type

Global gEStruc As ErrorType
Sub EmptyErrStruc_S(utEStruc As ErrorType)
  Dim i As Integer

  utEStruc.iErrNum = 0
  utEStruc.sHeadline = ""
  utEStruc.sProblemMsg = ""
  utEStruc.sResponseMsg = ""
  utEStruc.sErrorSource = ""
  For i = 0 To 2
    utEStruc.iBtnCap(i) = -1
  Next
  utEStruc.iBitmap = 1

End Sub
Function FillErrorStruct_F(EStruc As ErrorType) As Boolean
  'Must save error text before starting new error handler
  'in case we need it later
  EStruc.sProblemMsg = Error(EStruc.iErrNum)
  On Error GoTo vbDefaultFill

  EStruc.sHeadline = "Error " & Format$(EStruc.iErrNum)
  EStruc.sProblemMsg = EStruc.sErrorDescription
  EStruc.sErrorSource = EStruc.sErrorSource
  EStruc.sResponseMsg = "Contact the Company and tell them you received Error # " & Str$(EStruc.iErrNum) & ". You should write down the program function you were using, the record you were working with, and what you were doing."

   Select Case EStruc.iErrNum
       'Case Error number here
       'not sure what numeric errors user will ecounter, but can be implemented here
       'e.g.
       'EStruc.sHeadline = "Error 3265"
       'EStruc.sResponseMsg = "Contact tech support. Tell them what you were doing in the program."

     Case Else

       EStruc.sHeadline = "Error " & Format$(EStruc.iErrNum) & ": " & EStruc.sErrorDescription
       EStruc.sProblemMsg = EStruc.sErrorDescription

   End Select

   GoTo FillStrucEnd

vbDefaultFill:

  'Error Not on file
  EStruc.sHeadline = "Error " & Format$(EStruc.iErrNum) & ": Contact Tech Support"
  EStruc.sResponseMsg = "Contact the Company and tell them you received Error # " & Str$(EStruc.iErrNum)
FillStrucEnd:

  Exit Function

End Function
Function iErrorHandler_F(utEStruc As ErrorType) As Integer
  Static sCaption(3) As String
  Dim i As Integer
  Dim iMCursor As Integer

  Beep

  'Setup static array
  If Len(sCaption(0)) < 1 Then
    sCaption(CMD_IGNORE) = "&Ignore"
    sCaption(CMD_RETRY) = "&Retry"
    sCaption(CMD_CANCEL) = "&Cancel"
    sCaption(CMD_CONTINUE) = "Continue"
  End If

  Load frmErrors

  'Did caller pass error info?  If not fill struc with the needed info
  If Len(utEStruc.sHeadline) < 1 Then
    i = FillErrorStruct_F(utEStruc)
  End If

  frmErrors!lblHeadline.Caption = utEStruc.sHeadline
  frmErrors!lblProblem.Caption = utEStruc.sProblemMsg
  frmErrors!lblSource.Caption = utEStruc.sErrorSource
  frmErrors!lblResponse.Caption = utEStruc.sResponseMsg

  frmErrors.Show
  iErrorHandler_F = frmErrors.Tag   ' Save user response
  Unload frmErrors                  ' Unload and release form

  EmptyErrStruc_S utEStruc          ' Release memory

End Function

You may have errors that will be custom only to your application. This would typically be a short list of errors specifically only to your application. If you don't already have a constants module, create one that will contain an ENUM of your custom errors. (NOTE: Office '97 does NOT support ENUMS.). The ENUM should look something like this:

您可能会遇到仅针对您的应用程序自定义的错误。这通常是专门针对您的应用程序的错误的简短列表。如果您还没有常量模块，请创建一个包含自定义错误 ENUM 的模块。（注意：Office '97 不支持 ENUMS。）。ENUM 应如下所示：

Public Enum CustomErrorName
  MaskedFilterNotSupported
  InvalidMonthNumber
End Enum

Create a module that will throw your custom errors.

创建一个将抛出自定义错误的模块。

'********************************************************************************************************************************
'    MODULE: CustomErrorList
'
'   PURPOSE: For trapping custom errors applicable to this application
'
'INSTRUCTIONS:  To use this module to create your own custom error:
'               1.  Add the Name of the Error to the CustomErrorName Enum
'               2.  Add a Case Statement to the raiseCustomError Sub
'               3.  Call the raiseCustomError Sub in the routine you may see the custom error
'               4.  Make sure the routine you call the raiseCustomError has error handling in it
'
'
'     Date:    Name:           Description:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'03/26/2010    Ray       Initial Creation
'********************************************************************************************************************************
Option Explicit
Const MICROSOFT_OFFSET = 512 'Microsoft reserves error values between vbObjectError and vbObjectError + 512
'************************************************************************************************
'  FUNCTION:  raiseCustomError
'
'   PURPOSE:  Raises a custom error based on the information passed
'
'PARAMETERS:  customError - An integer of type CustomErrorName Enum that defines the custom error
'             errorSource - The place the error came from
'
'   Returns:  The ASCII vaule that should be used for the Keypress
'
'     Date:    Name:           Description:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'03/26/2010    Ray       Initial Creation
'************************************************************************************************
Public Sub raiseCustomError(customError As Integer, Optional errorSource As String = "")
  Dim errorLong As Long
  Dim errorDescription As String

  errorLong = vbObjectError + MICROSOFT_OFFSET + customError

  Select Case customError

    Case CustomErrorName.MaskedFilterNotSupported
      errorDescription = "The mask filter passed is not supported"

    Case CustomErrorName.InvalidMonthNumber
      errorDescription = "Invalid Month Number Passed"

    Case Else
      errorDescription = "The custom error raised is unknown."

  End Select

  Err.Raise errorLong, errorSource, errorDescription

End Sub

You are now well equipped to trap errors in your program. You sub (or function), should look something like this:

您现在可以很好地捕获程序中的错误。你子（或函数），应该是这样的：

Public Sub MySub(monthNumber as Integer)
  On Error GoTo eh  

  Dim sheetWorkSheet As Worksheet

  'Run Some code here

  '************************************************
  '*   OPTIONAL BLOCK 1:  Look for a specific error
  '************************************************
  'Temporarily Turn off Error Handling so that you can check for specific error
  On Error Resume Next
  'Do some code where you might expect an error.  Example below:
  Const ERR_SHEET_NOT_FOUND = 9 'This error number is actually subscript out of range, but for this example means the worksheet was not found

  Set sheetWorkSheet = Sheets("January")

  'Now see if the expected error exists

  If Err.Number = ERR_SHEET_NOT_FOUND Then
    MsgBox "Hey!  The January worksheet is missing.  You need to recreate it."
    Exit Sub
  ElseIf Err.Number <> 0 Then
    'Uh oh...there was an error we did not expect so just run basic error handling 
    GoTo eh
  End If

  'Finished with predictable errors, turn basic error handling back on:
  On Error GoTo eh

  '**********************************************************************************
  '*   End of OPTIONAL BLOCK 1
  '**********************************************************************************

  '**********************************************************************************
  '*   OPTIONAL BLOCK 2:  Raise (a.k.a. "Throw") a Custom Error if applicable
  '**********************************************************************************
  If not (monthNumber >=1 and monthnumber <=12) then
    raiseCustomError CustomErrorName.InvalidMonthNumber, "My Sub"
  end if
  '**********************************************************************************
  '*   End of OPTIONAL BLOCK 2
  '**********************************************************************************

  'Rest of code in your sub

  goto sub_exit

eh:
  gEStruc.iErrNum = Err.Number
  gEStruc.sErrorDescription = Err.Description
  gEStruc.sErrorSource = Err.Source
  m_rc = iErrorHandler_F(gEStruc)

  If m_rc = CMD_RETRY Then
    Resume
  End If

sub_exit:
  'Any final processing you want to do.
  'Be careful with what you put here because if it errors out, the error rolls up.  This can be difficult to debug; especially if calling routine has no error handling.

  Exit Sub 'I was told a long time ago (10+ years) that exit sub was better than end sub...I can't tell you why, so you may not want to put in this line of code.  It's habit I can't break :P
End Sub

A copy/paste of the code above may not work right out of the gate, but should definitely give you the gist.

上面代码的复制/粘贴可能无法立即生效，但绝对应该为您提供要点。

BTW, if you ever need me to do your company logo, look me up at http://www.MySuperCrappyLogoLabels99.com

顺便说一句，如果您需要我做您的公司徽标，请在http://www.MySuperCrappyLogoLabels99.com 上查找我

回答by sellC1964

I keep things simple:
At the module level I define two variables and set one to the name of the module itself.

我保持简单：
在模块级别，我定义了两个变量并将其中一个设置为模块本身的名称。

    Private Const ThisModuleName            As String = "mod_Custom_Functions"
    Public sLocalErrorMsg                   As String

Within each Sub/Function of the module I define a local variable

在模块的每个子/函数中，我定义了一个局部变量

    Dim ThisRoutineName                     As String

I set ThisRoutineName to the name of the sub or function

我将 ThisRoutineName 设置为子或函数的名称

' Housekeeping
    On Error Goto ERR_RTN
    ThisRoutineName = "CopyWorksheet"

I then send all errors to an ERR_RTN: when they occur, but I first set the sLocalErrorMsg to define what the error actually is and provide some debugging info.

然后我将所有错误发送到 ERR_RTN: 当它们发生时，但我首先设置 sLocalErrorMsg 来定义错误实际是什么并提供一些调试信息。

    If Len(Trim(FromWorksheetName)) < 1 Then
        sLocalErrorMsg = "Parameter 'FromWorksheetName' Is Missing."
        GoTo ERR_RTN
    End If

At the bottom of each sub/function, I direct the logic flow as follows

在每个子/功能的底部，我指导逻辑流程如下

    '
    ' The "normal" logic goes here for what the routine does
    '
    GoTo EXIT_RTN

    ERR_RTN:

        On Error Resume Next

    ' Call error handler if we went this far.
        ErrorHandler ThisModuleName, ThisRoutineName, sLocalErrorMsg, Err.Description, Err.Number, False

    EXIT_RTN:

        On Error Resume Next
     '
     ' Some closing logic
     '
    End If

I then have a seperate module I put in all projects called "mod_Error_Handler".

然后我有一个单独的模块，我把它放在所有名为“mod_Error_Handler”的项目中。

    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Subroutine Name:     ErrorHandler                                                     '
    '                                                                                       '
    ' Description:                                                                          '
    '   This module will handle the common error alerts.                                    '
    '                                                                                       '
    ' Inputs:                                                                               '
    '   ModuleName                String    'The name of the module error is in.            '
    '   RoutineName               String    'The name of the routine error in in.           '
    '   LocalErrorMsg             String    'A local message to assist with troubleshooting.'
    '   ERRDescription            String    'The Windows Error Description.                 '
    '   ERRCode                   Long      'The Windows Error Code.                        '
    '   Terminate                 Boolean   'End program if error encountered?              '
    '                                                                                       '
    ' Revision History:                                                                     '
    ' Date (YYYYMMDD) Author                Change                                          '
    ' =============== ===================== =============================================== '
    ' 20140529        XXXXX X. XXXXX        Original                                        '
    '                                                                                       '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '
    Public Sub ErrorHandler(ModuleName As String, RoutineName As String, LocalErrorMsg As String, ERRDescription As String, ERRCode As Long, Terminate As Boolean)
        Dim sBuildErrorMsg                 As String

    ' Build Error Message To Display
        sBuildErrorMsg = "Error Information:" & vbCrLf & vbCrLf

        If Len(Trim(ModuleName)) < 1 Then
            ModuleName = "Unknown"
        End If

        If Len(Trim(RoutineName)) < 1 Then
           RoutineName = "Unknown"
        End If

        sBuildErrorMsg = sBuildErrorMsg & "Module Name:        " & ModuleName & vbCrLf & vbCrLf
        sBuildErrorMsg = sBuildErrorMsg & "Routine Name:       " & RoutineName & vbCrLf & vbCrLf

        If Len(Trim(LocalErrorMsg)) > 0 Then
            sBuildErrorMsg = sBuildErrorMsg & "Local Error Msg:    " & LocalErrorMsg & vbCrLf & vbCrLf
        End If

        If Len(Trim(ERRDescription)) > 0 Then
            sBuildErrorMsg = sBuildErrorMsg & "Program Error Msg:  " & ERRDescription & vbCrLf & vbCrLf
            If IsNumeric(ERRCode) Then
                sBuildErrorMsg = sBuildErrorMsg & "Program Error Code: " & Trim(Str(ERRCode)) & vbCrLf & vbCrLf
            End If
        End If

        MsgBox sBuildErrorMsg, vbOKOnly + vbExclamation, "Error Detected!"

        If Terminate Then
            End
        End If

    End Sub

The end result is a pop-up error message teling me in what module, what soubroutine, and what the error message specifically was. In addition, it also will insert the Windows error message and code.

最终结果是一条弹出错误消息，告诉我在哪个模块、什么 soubroutine 中以及错误消息具体是什么。此外，它还将插入 Windows 错误消息和代码。

回答by D_Bester

Block 2 doesn't work because it doesn't reset the Error Handler potentially causing an endless loop. For Error Handling to work properly in VBA, you need a Resumestatement to clear the Error Handler. The Resumealso reactivates the previous Error Handler. Block 2 fails because a new error would go back to the previous Error Handler causing an infinite loop.

块 2 不起作用，因为它不会重置错误处理程序，这可能会导致无限循环。要使错误处理在 VBA 中正常工作，您需要一个Resume语句来清除错误处理程序。该Resume还重新激活之前的错误处理程序。块 2 失败，因为新错误会返回到前一个错误处理程序，从而导致无限循环。

Block 3 fails because there is no Resumestatement so any attempt at error handling after that will fail.

块 3 失败，因为没有Resume语句，因此在此之后的任何错误处理尝试都将失败。

Every error handler must be ended by exiting the procedure or a Resumestatement. Routing normal execution around an error handler is confusing. This is why error handlers are usually at the bottom.

每个错误处理程序都必须通过退出过程或Resume语句来结束。围绕错误处理程序路由正常执行是令人困惑的。这就是错误处理程序通常位于底部的原因。

But here is another way to handle an error in VBA. It handles the error inline like Try/Catch in VB.net There are a few pitfalls, but properly managed it works quite nicely.

但这是在 VBA 中处理错误的另一种方法。它处理内联错误，如 VB.net 中的 Try/Catch 有一些陷阱，但正确管理它可以很好地工作。

Sub InLineErrorHandling()

    'code without error handling

BeginTry1:

    'activate inline error handler
    On Error GoTo ErrHandler1

        'code block that may result in an error
        Dim a As String: a = "Abc"
        Dim c As Integer: c = a 'type mismatch

ErrHandler1:

    'handle the error
    If Err.Number <> 0 Then

        'the error handler has deactivated the previous error handler

        MsgBox (Err.Description)

        'Resume (or exit procedure) is the only way to get out of an error handling block
        'otherwise the following On Error statements will have no effect
        'CAUTION: it also reactivates the previous error handler
        Resume EndTry1
    End If

EndTry1:
    'CAUTION: since the Resume statement reactivates the previous error handler
    'you must ALWAYS use an On Error GoTo statement here
    'because another error here would cause an endless loop
    'use On Error GoTo 0 or On Error GoTo <Label>
    On Error GoTo 0

    'more code with or without error handling

End Sub

Sources:

资料来源：

• http://www.cpearson.com/excel/errorhandling.htm
• http://msdn.microsoft.com/en-us/library/bb258159.aspx

• http://www.cpearson.com/excel/errorhandling.htm
• http://msdn.microsoft.com/en-us/library/bb258159.aspx

The key to making this work is to use a Resumestatement immediately followed by another On Errorstatement. The Resumeis within the error handler and diverts code to the EndTry1label. You must immediately set another On Errorstatement to avoid problems as the previous error handler will "resume". That is, it will be active and ready to handle another error. That could cause the error to repeat and enter an infinite loop.

完成这项工作的关键是使用一个Resume语句，紧接着另一个On Error语句。该Resume是错误处理程序中，并转移代码的EndTry1标签。您必须立即设置另一个On Error语句以避免出现问题，因为前一个错误处理程序将“恢复”。也就是说，它将处于活动状态并准备好处理另一个错误。这可能会导致错误重复并进入无限循环。

To avoid using the previous error handler again you need to set On Errorto a new error handler or simply use On Error Goto 0to cancel all error handling.

为了避免再次使用以前的错误处理程序，您需要设置On Error为新的错误处理程序或简单地使用On Error Goto 0取消所有错误处理。