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

Question

我收到了 Rob Bovey 写的《Professional Excel Development》一书，它让我大开眼界。

我正在通过错误处理重新调整我的代码。然而，还有很多我不明白的地方。我特别需要知道如何在函数中正确使用它。我使用 Bovey 的错误处理程序的重新抛出版本(在底部)。当我开始时，我使用基本的 bool (非重新抛出)方法并将我的子例程转换为 bool 函数。 (P.S.我根据答案切换回 bool 方法。)

我需要有关如何将功能融入此方案的指导。我希望它们返回它们的实际值(字符串或 double 值，例如，如果在某些情况下失败，则返回 -1)，这样我就可以将它们嵌套在其他函数中，而不仅仅是返回错误处理 bool 值。

这就是入口点内对 bDrawCellBorders(myWS) 的典型子例程调用的样子。子调用似乎运行良好。 (即，它是一个子例程，仅转换为函数，以便它可以向错误处理方案返回 bool 值。)

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

但是，我不知道如何将其扩展到实际功能。这是基于书中为子例程编写的示例，我只是将其切换为函数。

问题:

• 我该如何调用它？是不是就像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

错误处理程序:

'
' 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

Answer 1

这是一本很棒的书，作者是 Rob。

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

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

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

1. 发生错误的行
2. 错误号
3. 错误消息
4. 重置事件(如果适用)

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

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

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

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

1. Line on which the error happened

有一个名为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

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

现在我知道错误发生在第 30 行，即 i = 1111111111

继续下一步

2. Error Number
3. Error Message

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

检查这个例子

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

当你运行这段代码时，你会得到类似这样的结果。

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

'~~> 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

进入下一个:)

Reset Events if applicable

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

如果您在上面的代码中注意到我们正在设置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

像 Philippe 一样，我也强烈建议您使用 MZ-Tools for VBA。我现在已经使用它很多年了......

希望这有帮助。