VBA Excel 错误处理 - 特别是在函数方面 - 专业 Excel 开发风格

VBA Excel Error Handling - especially in functions - Professional Excel Development Style

提问by Shari W

I got the book "Professional Excel Development" by Rob Bovey and it is opening up my eyes.

我得到了 Rob Bovey 的“Professional Excel Development”一书，它让我大开眼界。

I am refitting my code with error handling. However, there is a lot I don't understand. I especially need to know how to correctly use it in functions. I use Bovey's rethrow version of the error handler (at bottom). When I started, I was using the basic boolean (non-rethrow) method and turned my subroutines into boolean functions. (P.S. I am switching back to the boolean method based on the answer.)

我正在用错误处理重新调整我的代码。然而，有很多我不明白。我特别需要知道如何在函数中正确使用它。我使用 Bovey's rethrow 版本的错误处理程序（在底部）。当我开始时，我使用基本的布尔（非重新抛出）方法并将我的子例程转换为布尔函数。（PS 我正在根据答案切换回布尔方法。）

I need guidance on how to fit functions into this scheme. I want them to return their real values (a string or double, e.g., or -1 if they fail in some cases) so I can nest them in other functions and not just return an error handling boolean.

我需要有关如何将函数纳入此方案的指导。我希望它们返回它们的实际值（字符串或双精度值，例如，如果在某些情况下失败，则为 -1），因此我可以将它们嵌套在其他函数中，而不仅仅是返回错误处理布尔值。

This is what a typical subroutine call to bDrawCellBorders(myWS) would look like within an entry point. Sub calls seem to be working well. (I.e. it is a subroutine that was turned into a function only so it can return a boolean to the error handling scheme.)

这是对 bDrawCellBorders(myWS) 的典型子例程调用在入口点内的样子。子调用似乎运行良好。（即它是一个子程序，它被转变成一个函数，所以它可以向错误处理方案返回一个布尔值。）

Sub UpdateMe()  ' Entry Point

    Const sSOURCE As String = "UpdateMe()"

    On Error GoTo ErrorHandler

    Set myWS = ActiveCell.Worksheet
    Set myRange = ActiveCell
    myWS.Unprotect

' lots of code

    If Not bDrawCellBorders(myWS) Then ERR.Raise glHANDLED_ERROR    ' Call subroutine

' lots of code

ErrorExit:
    On Error Resume Next
    Application.EnableEvents = True
    myWS.Protect AllowFormattingColumns:=True
    Exit Sub

ErrorHandler:
    If bCentralErrorHandler(msMODULE, sSOURCE,,True) Then  ' Call as Entry Point
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Sub

However, I don't know how to extend this to real functions. This is based off an example in the book that was drawn up for a subroutine, and I just switched it to a function. Questions: * How do I call it? Is it simply like x = sngDoSomeMath(17) * Will its error handling function properly? * Where is the right place or places to call the error handling routine with bReThrow=true?

但是，我不知道如何将其扩展到实际功能。这是基于书中为子例程绘制的示例，我只是将其切换为函数。问题： * 我如何称呼它？就像 x = sngDoSomeMath(17) * 它的错误处理功能是否正常？* 使用 bReThrow=true 调用错误处理例程的正确位置在哪里？

Public Function sngDoSomeMath(ByVal iNum As Integer) As Single

Dim sngResult As Single

Const sSOURCE As String = "sngDoSomeMath()"

On Error GoTo ErrorHandler

' example 1, input did not pass validation. don't want to 
' go up the error stack but just inform the
' calling program that they didn't get a good result from this 
' function call so they can do something else
If iNum <> 42 Then
    sngResult = -1    'function failed because I only like the number 42
    GoTo ExitHere
End If

' example 2, true error generated
sngResult = iNum / 0

sngDoSomeMath = lResult

ExitHere:
    Exit Function
ErrorHandler:

' Run cleanup code
'  ... here if any

' Then do error handling

If bCentralErrorHandler(msMODULE, sSOURCE, , , True) Then ' The true is for RETHROW
    Stop
    Resume
End If

End Function

The Error Handler Routine:

错误处理程序：

'
' Description:  This module contains the central error
'               handler and related constant declarations.
'
' Authors:      Rob Bovey, www.appspro.com
'               Stephen Bullen, www.oaltd.co.uk
'
' Chapter Change Overview
' Ch#   Comment
' --------------------------------------------------------------
' 15    Initial version
'
Option Explicit
Option Private Module

' **************************************************************
' Global Constant Declarations Follow
' **************************************************************
Public Const gbDEBUG_MODE As Boolean = False    ' True enables debug mode, False disables it.
Public Const glHANDLED_ERROR As Long = 9999     ' Run-time error number for our custom errors.
Public Const glUSER_CANCEL As Long = 18         ' The error number generated when the user cancels program execution.


' **************************************************************
' Module Constant Declarations Follow
' **************************************************************
Private Const msSILENT_ERROR As String = "UserCancel"   ' Used by the central error handler to bail out silently on user cancel.
Private Const msFILE_ERROR_LOG As String = "Error.log"  ' The name of the file where error messages will be logged to.


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Comments: This is the central error handling procedure for the
'           program. It logs and displays any run-time errors
'           that occur during program execution.
'
' Arguments:    sModule         The module in which the error occured.
'               sProc           The procedure in which the error occured.
'               sFile           (Optional) For multiple-workbook
'                               projects this is the name of the
'                               workbook in which the error occured.
'               bEntryPoint     (Optional) True if this call is
'                               being made from an entry point
'                               procedure. If so, an error message
'                               will be displayed to the user.
'
' Returns:      Boolean         True if the program is in debug
'                               mode, False if it is not.
'
' Date          Developer       Chap    Action
' --------------------------------------------------------------
' 03/30/08      Rob Bovey       Ch15    Initial version
'
Public Function bCentralErrorHandler( _
       ByVal sModule As String, _
       ByVal sProc As String, _
       Optional ByVal sFile As String, _
       Optional ByVal bEntryPoint As Boolean, _
       Optional ByVal bReThrow As Boolean = True) As Boolean

    Static sErrMsg As String

    Dim iFile As Integer
    Dim lErrNum As Long
    Dim sFullSource As String
    Dim sPath As String
    Dim sLogText As String

    ' Grab the error info before it's cleared by
    ' On Error Resume Next below.
    lErrNum = ERR.Number
    ' If this is a user cancel, set the silent error flag
    ' message. This will cause the error to be ignored.
    If lErrNum = glUSER_CANCEL Then sErrMsg = msSILENT_ERROR
    ' If this is the originating error, the static error
    ' message variable will be empty. In that case, store
    ' the originating error message in the static variable.
    If Len(sErrMsg) = 0 Then sErrMsg = ERR.Description

    ' We cannot allow errors in the central error handler.
    On Error Resume Next

    ' Load the default filename if required.
    If Len(sFile) = 0 Then sFile = ThisWorkbook.Name

    ' Get the application directory.
    sPath = ThisWorkbook.Path
    If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"

    ' Construct the fully-qualified error source name.
    sFullSource = "[" & sFile & "]" & sModule & "." & sProc

    ' Create the error text to be logged.
    sLogText = "  " & sFullSource & ", Error " & _
               CStr(lErrNum) & ": " & sErrMsg

    ' Open the log file, write out the error information and
    ' close the log file.
    iFile = FreeFile()
    Open sPath & msFILE_ERROR_LOG For Append As #iFile
    Print #iFile, Format$(Now(), "mm/dd/yy hh:mm:ss"); sLogText
    If bEntryPoint Or Not bReThrow Then Print #iFile,
    Close #iFile

    ' Do not display or debug silent errors.
    If sErrMsg <> msSILENT_ERROR Then

        ' Show the error message when we reach the entry point
        ' procedure or immediately if we are in debug mode.
        If bEntryPoint Or gbDEBUG_MODE Then
            Application.ScreenUpdating = True
            MsgBox sErrMsg, vbCritical, gsAPP_NAME
            ' Clear the static error message variable once
            ' we've reached the entry point so that we're ready
            ' to handle the next error.
            sErrMsg = vbNullString
        End If

        ' The return vale is the debug mode status.
        bCentralErrorHandler = gbDEBUG_MODE

    Else
        ' If this is a silent error, clear the static error
        ' message variable when we reach the entry point.
        If bEntryPoint Then sErrMsg = vbNullString
        bCentralErrorHandler = False
    End If

    'If we're using re-throw error handling,
    'this is not the entry point and we're not debugging,
    're-raise the error, to be caught in the next procedure
    'up the call stack.
    'Procedures that handle their own errors can call the
    'central error handler with bReThrow = False to log the
    'error, but not re-raise it.
    If bReThrow Then
        If Not bEntryPoint And Not gbDEBUG_MODE Then
            On Error GoTo 0
            ERR.Raise lErrNum, sFullSource, sErrMsg
        End If
    Else
        'Error is being logged and handled,
        'so clear the static error message variable
        sErrMsg = vbNullString
    End If

End Function

采纳答案by Shari W

I needed a bit more help on this specific technique so I went right to the source and Mr. Bovey was gracious enough to reply. He gave me permission to post his response to the StackOverflow community.

我需要更多关于这个特定技术的帮助，所以我直接找到了来源，Bovey 先生很客气地回复了。他允许我将他的回复发布到 StackOverflow 社区。

The instructions below refer to his preferred method of error handling for functions the "boolean error handling" technique and not to the alternate "rethrow method", both described in his book "Professional Excel Development" 2nd edition.

下面的说明指的是他首选的函数错误处理方法“布尔错误处理”技术，而不是替代的“重新抛出方法”，这两种方法都在他的“专业 Excel 开发”第二版一书中有所描述。

---

---

Hi Shari,

嗨，莎莉，

In answer to your questions about error handling in functions, there are three error handling scenarios you can have with a function in VBA:

在回答有关函数中错误处理的问题时，您可以在 VBA 中使用函数进行三种错误处理方案：

1) The function is so trivial that is doesn't need an error handler. In the unlikely event an error occurs in a function like this it will spill over into the error handler of the calling procedure.

1) 该函数非常简单，不需要错误处理程序。在不太可能的情况下，在这样的函数中发生错误，它将溢出到调用过程的错误处理程序中。

2) A non-trivial function needs an error handler and uses the Boolean return value system described in the book. Any other values the function needs to return are returned through ByRef arguments. This case covers the vast majority of functions I write. There are some things you can't do with functions like this, feeding them directly into the argument of another function is one example, but I consider this a good tradeoff in order to achieve bullet proof error handling.

2) 一个非平凡的函数需要一个错误处理程序，并使用书中描述的布尔返回值系统。函数需要返回的任何其他值都通过 ByRef 参数返回。这个案例涵盖了我编写的绝大多数函数。有些事情你不能用这样的函数做，直接将它们输入另一个函数的参数就是一个例子，但我认为这是一个很好的权衡，以实现防弹错误处理。

3) A non-trivial function needs an error handler and must return a value not related to its error status. This is a rare situation because I can convert 99% plus of these into case 2 by restructuring my code. If you can't do this, your only choice is to select an arbitrary return value that is out of the range of normal return values and use this to indicate that an error has occurred. If the caller of this function sees this arbitrary error flag value it knows it can't continue.

3) 一个非平凡的函数需要一个错误处理程序，并且必须返回一个与其错误状态无关的值。这是一种罕见的情况，因为我可以通过重构我的代码将其中的 99% 以上转换为案例 2。如果你不能这样做，你唯一的选择就是选择一个超出正常返回值范围的任意返回值，并用它来指示发生了错误。如果这个函数的调用者看到这个任意的错误标志值，它就知道它不能继续了。

Rob Bovey Application Professionals http://www.appspro.com/

Rob Bovey 应用专家 http://www.appspro.com/

---

---

Code Example (Shari W)

代码示例 (Shari W)

---

---

' Show how to call a function using this error handling method.
Const giBAD_RESULT As Integer = -1

Function TestMath()   ' An Entry Point

    Dim sngResult As Single
    Dim iNum As Integer

    ' Call the function, actual result goes in sngResult but it returns the error handling boolean.
    ' A true error like Div 0 will go to error handler.

    ' Set Up Error Handling for Entry Point
    Application.EnableCancelKey = xlErrorHandler
    Dim bUserCancel As Boolean
    Const sSOURCE As String = "TestMath()"
    On Error GoTo ErrorHandler
    ' End Error Set Up

    iNum = 0   ' Try 0 to create error
    If Not bDoSomeMath(iNum, sngResult) Then ERR.Raise glHANDLED_ERROR
    ' If function does parameter checking and wants to return a bad input code, check for that.
    If sngResult = giBAD_RESULT Then
        MsgBox ("Bad input to bDoSomeMath " & iNum)
    Else
        MsgBox ("I believe the answer is " & sngResult)
    End If

ErrorExit:
    On Error Resume Next
    Exit Function

ErrorHandler:
    If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If
End Function
Function bDoSomeMath(ByVal iNum As Integer, ByRef sngResult As Single) As Boolean

    ' Error handling Set Up
    Dim bReturn As Boolean
    Const sSOURCE As String = "bDoSomeMath()"
    On Error GoTo ErrorHandler
    bReturn = True
    ' End Error Set Up

    If iNum < 0 Or iNum > 1000 Then
        sngResult = giBAD_RESULT   'function failed because I only like the numbers 0 to 1000
        GoTo ErrorExit
    Else
        sngResult = 100 / iNum   ' generate a true error by iNum = 0
    End If

ErrorExit:
    On Error Resume Next
    bDoSomeMath = bReturn
    Exit Function

ErrorHandler:
    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE, , , True) Then
        Stop
        Resume
    Else
        Resume ErrorExit
    End If

End Function

回答by Siddharth Rout

That is an amazing book by Rob.

这是罗布的一本了不起的书。

My two cents of Error Handling (Either for a procedure or a Function) is based on KISS(Keep it simple Silly)

我的两分钱错误处理（无论是过程还是函数）都是基于KISS（保持简单愚蠢）

Understand what do you want from your error handler?

了解您想从错误处理程序中得到什么？

This is usually what I want/expect from my error handler...

这通常是我想要/期望从我的错误处理程序...

1. Line on which the error happened
2. Error Number
3. Error Message
4. Reset Events if applicable

1. 发生错误的行
2. 错误编号
3. 错误信息
4. 如果适用，重置事件

Lets break the above. As you are by now already aware how your error handler looks like, Consider this example.

让我们打破上述。由于您现在已经知道您的错误处理程序的样子，请考虑这个示例。

Sub Sample()
    Dim i As Integer, j As Integer

    On Error GoTo Whoa

    Application.ScreenUpdating = False

    i = 1111111111

    For j = 1 To i
        Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value
    Next i

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

This is a very basic error handler but it's of very less help to me. So let's now tweak it to make it more useful. If you run the above code you get an error message like shown in the screenshot below and if you notice, it's not of much help.

这是一个非常基本的错误处理程序，但对我的帮助很小。所以现在让我们调整它以使其更有用。如果您运行上面的代码，您会收到一条错误消息，如下面的屏幕截图所示，如果您注意到，这并没有多大帮助。

Let's now tackle all the points that I mentioned in the Logicabove

现在让我们解决我在Logic上面提到的所有要点

1. Line on which the error happened

1. 发生错误的行

There is a property called ERLwhich very few people are aware of. You can actually use it to get the line number of the code where the error happened. For that you have to ensure you number your code. See this example.

有一种属性叫做ERL，很少有人知道。您实际上可以使用它来获取发生错误的代码的行号。为此，您必须确保对代码进行编号。请参阅此示例。

Sub Sample()
    Dim i As Integer, j As Integer

10      On Error GoTo Whoa

20      Application.ScreenUpdating = False

30      i = 1111111111

40      For j = 1 To i
50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value
60      Next j

LetsContinue:
70      Exit Sub
Whoa:
80      MsgBox Erl
90      Resume LetsContinue
End Sub

When you run the above code, you will get this

当你运行上面的代码时，你会得到这个

So now I know that the error happened on Line 30 which is i = 1111111111

所以现在我知道错误发生在第 30 行 i = 1111111111

Moving on to next

继续下一步

2. Error Number
3. Error Message

2. 错误编号
3. 错误信息

The error number and the error message can be retrieved from Err.Numberand Err.Descriptionrespectively. So now let's combine Erl, Err.Numberand Err.Description

错误编号和错误消息可以分别从Err.Number和 中检索Err.Description。所以现在让我们结合Erl,Err.Number和Err.Description

Check this example

检查这个例子

Sub Sample()
    Dim i As Integer, j As Integer

10      On Error GoTo Whoa

20      Application.ScreenUpdating = False

30      i = 1111111111

40      For j = 1 To i
50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value
60      Next j

LetsContinue:
70      Exit Sub
Whoa:
80      MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _
           "Error Message : " & Err.Description & vbNewLine & _
           "Error Number : " & Err.Number
90      Resume LetsContinue
End Sub

When you run this code, you will get something like this.

当你运行这段代码时，你会得到这样的东西。

You can choose to further customize the Error Message to make it more user friendly. For example

您可以选择进一步自定义错误消息以使其更加用户友好。例如

'~~> Message you want to deliver to the user in case the error happens
Const sMsg As String = "Please take a screenshot of this message and contact the developer for a resolution"
'~~> Title of your message box
Const sTitle As String = "Oopsie Daisies"

'~~> Change the above as applicable

Sub Sample()
    Dim i As Integer, j As Integer

10      On Error GoTo Whoa

20      Application.ScreenUpdating = False

30      i = 1111111111

40      For j = 1 To i
50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value
60      Next j

LetsContinue:
70      Exit Sub
Whoa:
80      MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _
           "Error Message : " & Err.Description & vbNewLine & _
           "Error Number : " & Err.Number & vbNewLine & vbNewLine & _
           sMsg, vbCritical, sTitle
90      Resume LetsContinue
End Sub

On to the next one :)

进入下一个:)

Reset Events if applicable

如果适用，重置事件

When you are working with events and an error occurs, if there is no error handling, the code breaks. Unfortunately that doesn't reset the events. It is very important that you reset the events in the Error handler.

当您处理事件并发生错误时，如果没有错误处理，代码就会中断。不幸的是，这不会重置事件。在错误处理程序中重置事件非常重要。

If you notice in the above code we are setting the Application.ScreenUpdating = False. When the code breaks, that event doesn't get reset. You will have to handle that in the Error handler LetsContinuein this case. See this example.

如果您在上面的代码中注意到我们正在设置Application.ScreenUpdating = False. 当代码中断时，该事件不会被重置。LetsContinue在这种情况下，您必须在错误处理程序中处理它。请参阅此示例。

'~~> Message you want to deliver to the user in case the error happens
Const sMsg As String = "Please take a screenshot of this message and contact the developer for a resolution"
'~~> Title of your message box
Const sTitle As String = "Oopsie Daisies"

'~~> Change the above as applicable

Sub Sample()
    Dim i As Integer, j As Integer

10      On Error GoTo Whoa

20      Application.ScreenUpdating = False

30      i = 1111111111

40      For j = 1 To i
50          Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value
60      Next j

LetsContinue:
70      Application.ScreenUpdating = True
80      Exit Sub
Whoa:
90      MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _
           "Error Message : " & Err.Description & vbNewLine & _
           "Error Number : " & Err.Number & vbNewLine & vbNewLine & _
           sMsg, vbCritical, sTitle
100     Resume LetsContinue
End Sub

Like Philippe, I also strongly suggest that you use MZ-Tools for VBA. I have been using it now for donkey years...

和 Philippe 一样，我也强烈建议您使用 MZ-Tools for VBA。我已经使用它驴年了......

Hope this helps.

希望这可以帮助。

回答by Philippe Grondier

a proposal for error handling management in VBA can be found here.

可以在此处找到有关 VBA 中错误处理管理的建议。

The very same tool (MZ-Tools) and method (standard/generic error handler, which could be used to build an automated error reporting system) will work with Excel.

完全相同的工具 (MZ-Tools) 和方法（标准/通用错误处理程序，可用于构建自动错误报告系统）将与 Excel 一起使用。