Excel/VBA 的错误处理

作者: , 共 6332 字

如果不提供错误处理方法,VBA在出错时会停留在出错之处。Excel/VBA 提供On Error关键词来处理程序运行过程中的错误,具体有下面两种用法:

  1. On Error Resume Next:当出错时跳到下一行继续运行。
  2. On Error Goto Line:出错时跳到行号Line。这里行号Line可以为数字(不为 0 和-1),也可以为字符串。

其它与之相关的还有:

  1. On Error Goto 0:运行后,对错误的捕获被关闭。程序出错时将自动中止。
  2. On Error Goto -1:运行后,「Resume」和「Resume Next」将失效。
  3. Resume:跳回并重新运行出错的行
  4. Resume Next:跳回并运行出错位置的下一行。

上面各种语句的组合可以非常复杂。下面是我半天时间的研究成果。

1. VBA 处理出错的两种方式

设置On Error Resume Next在出错时直接运行下一行,然后可以通过Err.Number来判断是否出错然后进行相关处理。

Sub Demo1()
    On Error Resume Next

    Call doing_thing1
    If Err.Number > 0 Then
        Call err_handler1   ' err handler deals with err in doing_thing2
        Err.Clear
    End If

    Call doing_thing2
    If Err.Number > 0 Then
        Call err_handler2   ' err handler deals with err in doing_thing2
        Err.Clear
    End If

    On Error GoTo 0
    Call doing_next_thing
End Sub

On Error Goto Line的方法更强大,在错误处理完毕之后还可以通过ResumeResume Next返回原出错点或出错点的下一行。

Sub Demo2()
    On Error GoTo err_handler_line1
    Call doing_thing1

    On Error GoTo err_handler_line1
    Call doing_thing2

    On Error Resume Next    ' On Error Resume Next和 On Error Goto可以混着用
    Call doing_next_thing

    Exit Sub

err_handler_line1:
    Call err_handler1
    Resume        ' return and re-run the error line
    ' 上面一行Resume、Resume Next、Exit Sub可根据情况任选其一
    ' 而且必须选一个,否则doing_thing1出错时,下面的Err_hander2也会被运行
err_handler_line2:
    Call err_handler2
    Resume Next   ' resume to next of the error line
End Sub

2. Err 变量

VBA 有一个全局变量Err,它保存了程序运行过程中出现的最后一个错误的相关信息(比如错误编码、错误描述等)。一般我们可以通过Err.Number > 0来判断是否出错;通过Err.Description查看具体出错信息。

Err可以通过Err.Clear手工清除。

VBA在每次碰到On Error Resume NextOn Error GotoResumeResume Next都会自动清空Err。我们需注意重复设置错误处理代码的副作用。比如在上面Demo2添加一行,后面的错误处理程序就失效了:

Sub Demo3()
    On Error Resume Next
    Call doing_thing        ' 如果此处出错,Err将保存错误信息

    On Error Resume Next    ' 此处Err对象被清空

    If Err.Number > 0 Then
        Call err_handler    ' 由于Error被清空,此处错误处理程序已经失效。
        Err.Clear
    End If

    Call doing_next_thing
End Sub

Err 是全局变量,母函数的错误信息会带入到子函数,子函数的错误信息也会被返回母函数。但实际表现非常复杂,具体请参考本文第五部分 - 子函数和母函数。

3. isErrorHanderEnabled

VBA 里对错误处理有两个状态。一个是isErrorHanderEnabled,另一个是isErrorHanderActive。这两个变量名不是真实的变量,只是为了更好解释这个问题。

isErrorHanderEnabled指目前是否捕获程序发生的错误,具体而言即是否设置了On Error Resume NextOn Error Goto Line。如果isErrorHanderEnabled == False,一旦某行代码出错, VBA 便会中止运行,并提示出错。如果isErrorHanderEnabled == True,则按照设置方法,VBA在出错后直接跳转或者继续运行下一行。

On Error Goto 0相当于取消前面设置的On Error Resume NextOn Error Goto Line,即重设isErrorHanderEnabled = False。接下来代码运行过程中一旦出错,便会中止运行。

isErrorHanderEnabled是一个局部变量,即子函数和母函数的状态互不影响。每个函数刚开始时,isErrorHanderEnabled的默认状态都是False

4. isErrorHanderActive

isErrorHanderActive是指VBA现在是否正在处理错误。如果设置了On Error Goto Line,然后程序出错时,此时程序自动跳转到Line位置开始运行,并设置isErrorHanderActive = True

On Error Goto -1用来告诉 VBA 目前错误已经处理完毕, VBA 应该回到正常运行状态,并设置isErrorHanderActive = False

isErrorHanderActive一样,isErrorHanderEnabled也是一个局部变量,即子函数和母函数的状态互不影响。每个函数刚开始时,isErrorHanderActive的默认状态也是False

需注意isErrorHanderActiveErr.Number>0并无直接关系,比如下面例子:

Sub Demo5()
    Dim i As Long
    On Error Resume Next
    i = 1 / 0        ' err occurs
    MsgBox "Here: Err.Number > 0 but isErrorHanderActive = False"

    On Error GoTo err_handler:
    i = 1 / 0        ' err occurs, set isErrorHanderActive = True

err_handler:
    On Error Resume Next        ' this will clear err
    MsgBox "Here: Err.Number = 0 but isErrorHanderActive = True"
End Sub

5. 函数调用时发生什么?

VBA 的子函数和母函数在处理错误之间的关系比较复杂。一般情况尽量让各自函数处理各自的问题,避免子函数将错误抛给母函数。

在子函数和母函数处理错误时,有两个问题值得讨论: 1 )子函数的 Err 变量信息是否传回给母函数; 2 )子函数的错误是否会触发母函数的错误处理程序。

结合MSDN 相关文档和各种测试案例,我发现:

  • 母函数在进入子函数时, Err 变量保存不变,错误信息传入子函数,但不会触发子函数的错误处理程序。
  • 子函数在On Error Resume Next后形成的错误信息会将向上传递给母函数,但不会触发母函数的错误处理。
  • 子函数在On Error Goto Line后形成的错误信息不会向上传递给母函数,也不会触发母函数的错误处理。
  • 子函数在On Error Goto Line跳转后处理错误过程中一旦形成新错误,将触发母函数的错误处理程序;同时如果母函数的错误处理方式是On Error Resume Next,子函数继续运行,并且子函数的错误信息将传给母函数;如果母函数错误方式是On Error Goto Line,子函数终止运行,跳回母函数的错误处理处,但子函数的错误信息并不会向上传递给母函数(即这种情况下,母函数只知道子函数有错位未被处理,但不知道任何错误信息)。

第四种情况子函数错误触发母函数的错误处理程序的原因是当子函数的isErrorHanderActive == True时,子函数的错误处理将被母函数接管,此时子函数一旦出错,错误上传的方式和第二条第三条的一样的,只不过决定于母函数的错误处理方式是「On Error Resume Next」还是「On Error Goto Line

下面这几个测试案例展示了上述行为特征:

Sub Demo4()
    On Error GoTo err_hander1
    Call Demo4_Sub1
    Debug.Print "Err in sub1:" & Err.Description ' Err description = ""

    On Error GoTo err_hander2
    Call Demo4_Sub2
    Debug.Print "Err in sub2:" & Err.Description ' Err description <> ""

    On Error GoTo err_hander3
    Call Demo4_Sub3
    Debug.Print "Err in sub3:" & Err.Description ' Err description = ""

    On Error Resume Next
    Call Demo4_Sub4
    Debug.Print "Err in sub4:" & Err.Description ' Err description <> ""

    Exit Sub
err_hander1:
    Debug.Print "err_hander1 incurred"  ' not incurred
    Resume Next

err_hander2:
    Debug.Print "err_hander2 incurred" ' not incurred
    Resume Next

err_hander3:
    Debug.Print "err_hander3 incurred" ' incurred
    Resume Next

err_hander4:
    Debug.Print "err_hander4 incurred" ' incurred
    Resume Next
End Sub

Sub Demo4_Sub1()
    On Error GoTo err_hander

    Dim i As Long
    i = 1 / 0

    Exit Sub
err_hander:
    Resume Next
End Sub

Sub Demo4_Sub2()
    On Error Resume Next

    Dim i As Long
    i = 1 / 0
End Sub

Sub Demo4_Sub3()
    On Error GoTo err_hander

    Dim i As Long
    i = 1 / 0

    Exit Sub
err_hander:
    i = 1 / 0
    Resume Next
End Sub

Sub Demo4_Sub4()
    On Error GoTo err_hander

    Dim i As Long
    i = 1 / 0

    Exit Sub
err_hander:
    i = 1 / 0
    Resume Next
End Sub

6. 其它一些注意点

6.1. 警惕 On Error Resume Next的副作用

一旦启用On Error Resume Next,所有错误都会被自动略过。除非有足够信心,不要用它。启用之后也尽快使用 On Error Goto 0 关闭它。

另外还要注意副作用。比如If的条件一旦出错,If内部的语句将被执行,这和直观想象并不相符。

Sub Demo6()
    On Error Resume Next
    Dim i As Long
    i = 0
    If 0 / 0 Then i = 1

    MsgBox i  ' i = 1
End Sub

6.2. 单元格错误也会触发VBA错误

在 VBA 中引用单元格的值,如果单元格里是一个公式并且公式出错,那么 VBA 中连带会抛出一个错误。

6.3. 选项中可选强制发生错误时中断程序

在 VB 编辑器的 工具 - 选项 - 通用 - 错误捕获 选项里,可以强制在程序发生错误时中断程序。这在调试程序时非常有用。

6.4. 编程小细节

1 )在错误处理程序之前添加 Exit Sub 或 Exit Function ,避免程序位出错时也运行错误处理程序。

2 )在每个错误程序后都添加 Resume、Resume Next、Exit Sub 或 Exit Function ,避免从当前错误处理之后继续运行后面的错误处理程序。

Q. E. D.

类似文章:
编程 » Excel, VBA
某些时候需要打开 Excel 文件来获取或者写入数据,但又不希望跳出打开的 Excel 文件窗口,可以用下面的代码:
相似度: 0.161
编程 » Excel, VBA
Excel VBA 出错时给出的错误信息极少,需要充分利用各种工具来进行调试。
更新一下之前写的Excel 的数据库类,将其改成函数的形式,调用更简单(省却了生成类实例的步骤)。现在这个代码在工作中用了一年多,已经比较健壮。若有问题,请留言指出或与我联系。
编程 » Shell, VBA
某些时候,我们需要在 Excel 中调用命令行或者 Bash 脚本, VBA 可以通过 Shell 函数很方便地做到这一点。以下用ipconfig /all来举例,这条命令行语句用来获得机器的网路配置信息,包括 IP、网关等信息。它可以替换成任何一个 bash 脚本和命令行代码。
编程 » Excel, VBA
无意中发现一个 Excel VBA 对待参数的一个"不正常"现象。这种处理方式可能无意中导致程序结果错误,而且你很难发现你的错误所在:
编程 » Excel, VBA
VBA 的 Date 类型比较奇怪。
编程 » Excel
在编辑 Excel 文件时经常遇到的一个问题是,我这边用得好好地,换台机器就变了个样,或者根本用不了。下面是我在日常工作中总结的一些避免这些情况的小技巧。
编程 » Excel, lotus, VBA
公司使用 Lotus ,我每天都通过 Excel 编写 VBA 代码自动调用 Lotus 发送邮件,发现一个奇怪的现象。一般我们通过下面的 VBA 代码指定邮件收件人:
以前发过一个编辑和发送 Outlook 邮件的 Excel/VBA 脚本。最近公司不让用 Outlook ,强制使用 IBM Lotus Notes ,我又写了一个编辑和发送 Lotus 邮件的 VBA 脚本。
编程 » Excel, VBA
最近学到一招,效果不错~
下图是过去两年银行间隔夜回购的成本和交易所隔夜回购成本的对比图。
这两天读了一些《量子江湖》。这本书有武侠、有科幻、有悬疑,情节架构和金庸武侠以及 20 世纪初的物理学进展互相关联,是一部非常有阅读快感的休闲读物。网上传闻作者陈怅毕业与哥伦比亚大学的金融工程专业,也是华尔街的一名 Quant ,可算同行也感到亲切。