VBA错误处理

时　间:2012-07-02 16:52:57
作　者:王度兰亭   ID：26851  城市：太原
摘　要:错误处理
正　文:

Error Handling In VBA
 
VBA错误处理 

Introduction

介绍

Error handling refers to the programming practice of anticipating and coding for error conditions that may arise when your program runs. Errors in general come in three flavors: compiler errors such as undeclared variables that prevent your code from compiling; user data entry error such as a user entering a negative value where only a positive number is acceptable; and run time errors, that occur when VBA cannot correctly execute a program statement.  We will concern ourselves here only with run time errors.  Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero. The example code in this article will use the division by zero error (Error 11) when we want to deliberately raise an error.

错误处理指的是对程序运行时可能出现的错误预先进行编码处理。错误通常有三种：编译错误，比如未声明的变量会导致代码无法编译；用户数据录入错误，比如在只能输入正值的地方输入了负值；和运行时错误，发生在VBA无法正确执行程序语句的时候。我们仅考虑运行时错误的情况。标准的运行时错误包括试图存取不存在的工作表或工作簿，或试图被0除。本文中如果需要产生一个错误，我们使用被零除错误（错误11）。

Your application should make as many checks as possible during initialization to ensure that run time errors do not occur later.  In Excel, this includes ensuring that required workbooks and worksheets are present and that required names are defined.  The more checking you do before the real work of your application begins, the more stable your application will be. It is far better to detect potential error situations when your application starts up before data is change than to wait until later to encounter an error situation.

程序执行前多做检查，把需要的文件准备好，放置在正确的位置，把需要的变量定义好。事前多做准备，强过出问题之后去收拾烂摊子。

If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. While this may be acceptable, even desirable, in a development environment, it is not acceptable to the end user in a production environment. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method.  Your goal should be to prevent unhandled errors from arising.

假设你没有错误处理，如果发生了运行时错误，VBA会显示标准的运行时错误对话框。这在开发期间是可以接受的，但是在程序作为产品交付用户使用时就不能接受了。良好的错误处理的目的是预先处理可能发生的错误，处理运行时的错误，或者用可控的、优雅的方式终止代码运行。你要尽可能防止未处理的错误发生。

A note on terminology: Throughout this article, the term procedure should be taken to mean a Sub, Function, or Property procedure, and the term exit statement should be taken to mean Exit Sub, Exit Function, or Exit Property.  The term end statement should be taken to mean End Sub , End Function,  End Property, or just  End.   

术语说明：本文中，procedure表示Sub，Function或Property procedure，exit statement表示Exit Sub，Exit Function或Exit Property。end statement表示End Sub，End Function，End Property，或End。

The On Error Statement

On Error 语句

The heart of error handling in VBA is the On Error statement. This statement instructs VBA what to do when an run time error is encountered.  The On Error statement takes three forms.

VBA错误处理的核心是 On Error 语句。该语句告诉VBA当遇到运行时错误时该怎么办。On Error语句有三种形式：

On Error Goto 0
 On Error Resume Next
 On Error Goto <label>:

The first form, On Error Goto 0, is the default mode in VBA.  This indicates that when a run time error occurs VBA should display its standard run time error message box, allowing you to enter the code in debug mode or to terminate the VBA program. When On Error Goto 0 is in effect, it is the same as having no enabled error handler.  Any error will cause VBA to display its standard error message box.

第一种形式，On Error Goto 0，是VBA的默认模式。这种模式下，当运行时错误发生时，VBA将显示标准的运行时错误对话框，允许你在调试模式下输入代码或终止VBA程序。使用On Error Goto 0，和没有启用错误处理是一样的。任何错误都会导致VBA显示标准的错误信息框。

The second form, On Error Resume Next , is the most commonly used and misused form.  It instructs to VBA to essentially ignore the error and resume execution on the next line of code. It is very important to remember that On Error Resume Next does not in any way "fix" the error. It simply instructs VBA to continue as if no error occured. However, the error may have side effects, such as uninitialized variables or objects set to Nothing.  It is the responsibility of your code to test for an error condition and take appropriate action.  You do this by testing the value of Err.Number  and if it is not zero execute appropriate code.  For example,

第二种形式，On Error Resume Next，是最常用并且最常被误用的形式。它告诉VBA有效地忽略错误并重新执行下一行代码。必须记住的要点是，On Error Resume Next不会修复错误。它只是告诉VBA在错误发生以后继续执行。然而，错误可能会有副作用，比如未初始化的变量或对象会被设置为nothing。你的代码需要测试这些错误情况并采取相应的动作。你应该测试Err.Number的值,如果不是0那就执行合适的代码.看例子:

    On Error Resume Next
     N = 1 / 0    ' cause an error
     If Err.Number <> 0 Then
        N = 1
     End If

This code attempts to assign the value 1 / 0 to the variable N. This is an illegal operations, so VBA will raise an error 11 -- Division By Zero -- and because we have On Error Resume Next in effect, code continues to the If statement. This statement tests the value of Err.Number  and assigns some other number to N.

上面的代码试图将1/0的结果赋值给变量N.这是不合法的,所以VBA会抛出一个错误error 11--被零除--,因为我们使用了On Error Resume Next,程序将继续运行,执行后面的 if 语句.这个语句测试Err.Number的值,并给N赋一个合适的值.

The third form On Error of  is  On Error Goto <label>:which tells VBA to transfer execution to the line following the specified line label. Whenever an error occurs, code execution immediately goes to the line following the line label.  None of the code between the error and the label is executed, including any loop control statements.

第三种形式 On Error Goto <label>. 它会告诉VBA转到 label 指定的代码行去运行. 当错误发生时,会立即执行 label 之后的代码. 发生错误的位置和 label 之间的代码都不会执行,包括 loop 控制语句.

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

Enabled And Active Error Handlers

使能的和激活的错误处理

An error handler is said to be enabled when an  On Error statement is executed.  Only one error handler is enabled at any given time, and VBA will behave according to the enabled error handler.  An active error handler is the code that executes when an error occurs and execution is transferred to another location via a On Error Goto <label>: statement. 

On Error语句执行之后, 错误处理就处于使能状态. 任何时间只能有一个错误处理处于使能状态, VBA 将依据使能的错误处理运作. 激活的错误处理是指错误发生时正被执行的错误处理, On Error Goto <label>语句将使程序转到别处继续执行.

Error Handling Blocks And On Error Goto

错误处理块和 On Error Goto

An error handling block, also called an error handler, is a section of code to which execution is tranferred via a On Error Goto <label>:  statement. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. You can't use  to the On Error Goto <label>:  statement merely skip over lines. For example, the following code will not work properly:

错误处理块,也叫错误处理句柄, 是一段代码, 通过 On Error Goto <label> 语句可以转向执行这段代码. 这段代码应该用于修复错误并重新返回主代码块执行,或是终止过程的执行. 你不能只是用On Error Goto <label> 来跳转.例如,下面的代码不能正常工作:

    On Error GoTo Err1:
     Debug.Print 1 / 0
     ' more code
 Err1:
     On Error GoTo Err2:
     Debug.Print 1 / 0
     ' more code
 Err2:

When the first error is raised, execution transfers to the line following Err1:. The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement.

 当第一个错误发生时, 程序转到 Err1: 之后执行. 当第二个错误发生的时候,第一个错误处理句柄依然处于激活状态,因此第二个 On Error 语句无法捕获第二个错误.

The Resume Statement

Resume 语句

The Resume statement instructs VBA to resume execution at a specified point in the code.  You can use  Resume only in an error handling block; any other use will cause an error. Moreover, Resume is the only way, aside from exiting the procedure, to get out of an error handling block. Do not use the Goto statement to direct code execution out of an error handling block.  Doing so will cause strange problems with the error handlers.

 Resume语句告诉VBA从指定的位置继续执行。只能在错误处理块中使用Resume，其它任何地方使用都会引起错误。此外，Resume是除了退出过程之外从错误处理块跳出的唯一方法。不要用Goto语句直接从错误处理块跳出，那会导致奇怪的问题。

The Resume statement takes three syntactic form:

Resume语句有三种形式：

Resume
 Resume Next
 Resume <label>

Used alone, Resume causes execution to resume at the line of code that caused the error.  In this case you must ensure that your error handling block fixed the problem that caused the initial error. Otherwise, your code will enter an endless loop, jumping between the line of code that caused the error and the error handling block.  The following code attempts to activate a worksheet that does not exist. This causes an error (9 - Subscript Out Of Range), and the code jumps to the error handling block which creates the sheet, correcting the problem, and resumes execution at the line of code that caused the error.

第一种形式，程序会转到引发错误的代码行继续执行。这种情况下你需要保证在错误处理块中修复了引发错误的问题。否则，程序会陷入死循环，在错误代码和错误处理块之间不停的来回跳转。下面的代码试图激活一个不存在的工作表。这会导致一个错误（9-下标越界），代码跳转到错误处理块，在那里创建工作表，修复问题，然后回到引发错误的位置继续运行。

    On Error GoTo ErrHandler:
     Worksheets("NewSheet").Activate
    Exit Sub

     ErrHandler:
     If Err.Number = 9 Then
         ' sheet does not exist, so create it
         Worksheets.Add.Name = "NewSheet"
         ' go back to the line of code that caused the problem
         Resume
     End If

 The second form of Resume is Resume Next . This causes code execution to resume at the line immediately following the line which caused the error.  The following code causes an error (11 - Division By Zero) when attempting to set the value of N. The error handling block assigns 1 to the variable N, and then causes execution to resume at the statement after the statement that caused the error.

第二种形式是Resume Next。 这会使程序立即转到错误语句的下一条语句继续运行。下面的代码导致一个错误（11-被零除）。错误处理块将1赋值给N，然后转到错误语句的下一条语句继续执行。

    On Error GoTo ErrHandler:
     N = 1 / 0
     Debug.Print N
     Exit Sub

     ErrHandler:
     N = 1
     ' go back to the line following the error
     Resume Next

 The third form of Resume is Resume <label>: . This causes code execution to resume at a line label. This allows you to skip a section of code if an error occurs. For example,

第三种形式是Resume <label>：。程序将转到指定的位置继续运行。这使你可以在错误发生后跳过一段代码继续执行。例如：

     On Error GoTo ErrHandler:
     N = 1 / 0
     '
     ' code that is skipped if an error occurs
     '
     Label1:
     '
    ' more code to execute
     '
     Exit Sub

     ErrHandler:
     ' go back to the line at Label1:
     Resume Label1:

 All forms of the Resume clear or reset the Err object.

这三种形式都会清除或重置Err对象。

Error Handling With Multiple Procedures

多过程错误处理

Every procedure need not have a error code. When an error occurs, VBA uses the last On Error statement to direct code execution. If the code causing the error is in a procedure with an On Error statement, error handling is as described in the above section. However, if the procedure in which the error occurs does not have an error handler, VBA looks backwards through the procedure calls which lead to the erroneous code. For example if procedure A calls B and B calls C, and A is the only procedure with an error handler, if an error occurs in procedure C, code execution is immediately transferred to the error handler in procedure A, skipping the remaining code in B.

不是每个过程都必须有错误处理代码。当错误发生时，VBA使用最后一个On Error语句来指导代码运行。如果引发错误的过程有On Error语句，错误处理将按照上面所讲的方式进行。然而，如果发生错误的过程没有错误处理代码，VBA将回溯过程调用的链条。举个例子，比如过程A调用了B，B又调用了C，只有A中有错误处理代码，如果C的代码发生了错误，程序会立即转到A的错误处理块，跳过B中的其它代码。 

A Note Of Caution

注意事项

It is tempting to deal with errors by placing an On Error Resume Next statement at the top of the procedure in order to get the code to run without raising an error.  This is very bad coding practice. Remember that using On Error Resume Next  does not fix errors.  It merely ignores them.

为了避免运行时的错误提示，可以在代码的开始位置放上On Error Resume Next,这是一种非常诱人的做法。但是这是非常糟糕的编程习惯。On Error Resume不会修复任何错误，它只是忽略了错误而已。

The Future Of Error Handling In VBA

VBA错误处理的未来

Error handling in VB6 and VBA is based on the On Error statement, which leads to awkward code structure. Languages like C++ provide a code structure call Try/Catch that allows much more granularity and control.  At some point, Microsoft will introduce their NET framework in to Office, and when this happens, VBA programmers will have at their disposal the language features of Try/Catch/Finally code structure that VB.NET developers already enjoy.

VB6和VBA的错误处理基于On Error 语句，这使程序的结构不怎么优美。类C++语言使用的Try/Catch结构在粒度和可控性方面就要强的多。或许某一天，微软会把NET框架引入Office，那时，VBA程序员就可以想VB.NET开发者一样使用Try/Catch/Finally这样的结构了。