Excel VBA错误处理代码

问题描述：

我创建了一个调用各种函数的宏，它们都返回字符串，并将结果显示在文本框中。Excel VBA错误处理代码

我开始阅读良好的错误处理实践，但有一段艰难的时间真的很了解它。我希望对我如何解决这个问题提出一些意见。

基本上，到目前为止我已经实现了错误处理的方法是在每个函数的开头放置一个错误处理程序，并对其进行设置，以便在该函数中发生错误时通知用户，但继续计算其他功能。

每一个我的功能将类似于此

Function fnGetNumbers() As String 

On Error Goto ErrorHandler 

// Code to extract numbers from text 

If NumbersInText = vbNullString Then 
    fnGetNumbers = vbNullString 
Else 
    fnGetNumbers = "The numbers in the text are: " & NumbersInText 
End If 

ErrorHandler: 
If Error <> 0 Then 
    fnGetNumbers = "An error occurred while extracting numbers from the text." 
End If 
End Function

任何想法和/或建议，将不胜感激！

答

错误处理（在我看来）真的要归结为你正在写的宏，谁在使用它们，谁在调试它们。虽然正确和彻底的错误处理是最佳实践，但如果您是唯一一个在进行调试的人，那么您将是唯一一个需要自定义错误的人。这取决于你的组织，但这取决于你的需要。

话虽这么说，在你的代码的一些注意事项：

Function fnGetNumbers() As String 
' Instead of returning a string, you can return a boolean and pass in a 
' holder string for returning the value. This allows you to check TRUE/FALSE 
' instead of checking if a string holds an error. 

On Error Goto ErrorHandler 

// Code to extract numbers from text 

If NumbersInText = vbNullString Then 
    fnGetNumbers = vbNullString 
Else 
    fnGetNumbers = "The numbers in the text are: " & NumbersInText 
End If 

Exit Function ' Always have this before your error block. 

ErrorHandler: 
    fnGetNumbers = "An error occurred while extracting numbers from the text." 
Exit Function ' While not necessary if 
       ' it is the only error handling block, it can be good practice. 
End Function

这也是最好返回某种值，可用于调试。返回一个简单的字符串是没用的，而返回一个描述错误类型的值更有用。

请参阅BrettDJ的回答评论。感谢一堆！ –

答

建议，你会不会在默认情况下这个运行的ErrorHandler部分，即无论是

然后获得一个有效的答案退出功能
得到一个错误（下面Err.Raise 999测试）

如果我正在运行一个具有清理的长子程序（即恢复Application设置，那么我将使ErrorHandler高于默认清理程序（以及处理发生错误的事实）

所以也许这（无法抗拒轻微的调整到IF以及 - fnGetNumbers为空默认因此无需设置）

代码

Function fnGetNumbers() As String 

On Error GoTo ErrorHandler 

`test the error 
Err.Raise 999 

If NumbersInText <> vbNullString Then fnGetNumbers = "The numbers in the text are: " & NumbersInText 
Exit Function 

ErrorHandler: 
    fnGetNumbers = "An error occurred while extracting numbers from the text." 
End Function

真棒，太感谢你了！我讨厌在这里选择一个评论作为“答案”，因为你们都非常清楚地帮助我更好地理解事情。我不知道“退出功能”这一行！现在事情变得更有意义了。 –

没问题，thx因此很快关闭并响应。很高兴它是有帮助的！ – brettdj

答

我会告诉你我通常做什么。我有一个专门用于退出宏（我称之为exitPoint）的子例程，并且我有一个子例程控制流（我称之为main），在main的开始处我有一个名为badExit的布尔值设置为true，并且在主要结束我把它设置为false，然后最后调用exitPoint。每个子例程或函数都有一个错误陷阱，它会将控制权交给ExitPoint，并使用一个字符串来指明错误发生在哪个例程中。然后exitPoint运行一系列清理错误处理代码，具体取决于badExit是true还是false。

基本上这个想法是我会提供支持，如果它是一个宏，我将它交给别人再也看不到它，我会在那里放置更多的防御性编码和有用的错误信息 - 你可以测试为一个错误号码，并给出一个特定的消息，例如。

因此，像这样（这是我剪了大量的代码出的实际宏只是为了说明）：

Option Explicit 
Option Private Module 

... 

Private mbBadExit  As Boolean 
Private msMacroWbName As String 
Private msMacroWbPath As String 
Private miSaveFormat As String 
Private miSheetsInNewWb As String 
Private mcolWorkbooks As New Collection 
Private mwbkNew   As Workbook 

... 

Sub Main() 
' --------------------------------------------------------------------- 
' Control procedure 
' --------------------------------------------------------------------- 

    Debug.Print "Main Start " & Time 

    'set exit state 
    mbBadExit = True 

    'set macro document name and path 
    msMacroWbName = ThisWorkbook.Name 
    msMacroWbPath = ThisWorkbook.Path 

    miSaveFormat = Application.DefaultSaveFormat 
    miSheetsInNewWb = Application.SheetsInNewWorkbook 

    'disable some default application behaviours for macro effeciency 
    With Application 
     .Calculation = xlCalculationManual 
     .ScreenUpdating = False 
     .DisplayAlerts = False 
     .EnableEvents = False 
     .DisplayStatusBar = False 
     .DefaultSaveFormat = xlOpenXMLWorkbook 'for excel 2007 compatability 
     .SheetsInNewWorkbook = 3 
    End With 

    Debug.Print "AddNew Start " & Time 
    AddNew    'creates new workbook which the rest of the macro works with 
    Debug.Print "Import Start " & Time 
    Import    'import bobj CP_Import file and scalepoint data 
    Debug.Print "Transform Start " & Time 
    Transform   'various data munging to final state 
    mbBadExit = False 'set exit state for clean exit 
    Debug.Print "ExitPoint Start " & Time 
    ExitPoint   'single exit point 

End Sub 

Private Sub ExitPoint(Optional ByVal sError As String) 
' --------------------------------------------------------------------- 
' Single exit point for macro, handles errors and clean up 
' --------------------------------------------------------------------- 

    Dim mwbk As Workbook 

    'return application behaviour to normal 
    On Error GoTo 0 
    With Application 
     .DisplayAlerts = True 
     .Calculation = xlCalculationAutomatic 
     .ScreenUpdating = True 
     .EnableEvents = True 
     .DisplayStatusBar = True 
     .DefaultSaveFormat = miSaveFormat 
     .SheetsInNewWorkbook = miSheetsInNewWb 
    End With 

    'handle good or bad exit 
    If mbBadExit = False Then 'no problem 
     MsgBox "Process complete" 
     'close this workbook, leaving result workbook open 
     Application.DisplayAlerts = False 
     Set mcolWorkbooks = Nothing 'destroy collection object 
     Workbooks(msMacroWbName).Close 'close macro wbk 
     Application.DisplayAlerts = True 
    Else 'an error occured 
     'show user error details 
     MsgBox prompt:="Macro process has ended prematurely. Contact ... for support." _ 
      & IIf(sError <> vbNullString, vbCrLf & sError, vbNullString) & vbCrLf _ 
      & Err.Description, Title:="Error " & IIf(Err.Number <> 0, Err.Number, vbNullString) 
      On Error Resume Next 
     'clean up open workbooks 
     For Each mwbk In mcolWorkbooks 
      mwbk.Close 
     Next 
    End If 

    Debug.Print "Finish " & Time 

    End 

End Sub 

Private Sub AddNew() 
' --------------------------------------------------------------------- 
' Creates new workbook which is the base workbook for 
' The rest of the macro 
' --------------------------------------------------------------------- 

    On Error GoTo errTrap 

    Set mwbkNew = Workbooks.Add 
    mcolWorkbooks.Add mwbkNew 
    With mwbkNew 
     .Title = "CP HR Import" 
     .Subject = "CP HR Import" 
    End With 

    Exit Sub 

errTrap: 

    ExitPoint ("Error in AddNew sub routine") 'pass control to error handling exitpoint sub 

End Sub 

Private Sub Import() 
' --------------------------------------------------------------------- 
' Connect to source file (xlsx) with ADO, pull data into a recordset 
' with SQL, then pull data to the workbook from the recordset to a 
' querytable. Kill connection etc.. 
' --------------------------------------------------------------------- 

    On Error GoTo errTrap 

    ...Code here... 


    Exit Sub 

errTrap: 

    ExitPoint ("Error in Import sub routine") 'pass control to error handling exitpoint sub 

End Sub 

Sub Transform() 
' --------------------------------------------------------------------- 
' Looks for records with an increment date and inserts a new record 
' showing the new scalepoint from the increment date with the new 
' salary 
' --------------------------------------------------------------------- 

    On Error GoTo errTrap 

    ...code here... 

    Exit Sub 

errTrap: 

    ExitPoint ("Error in Transform sub routine") 'pass control to error handling exitpoint sub 

End Sub 

Sub ColumnsToText(rngColumns As Range) 
' --------------------------------------------------------------------- 
' Takes a column as a range and converts to text. UK date safe but 
' not robust, use with care. 
' --------------------------------------------------------------------- 

    Dim avDates  As Variant 

    avDates = rngColumns.Value 
    With rngColumns 
     .NumberFormat = "@" 
     .FormulaLocal = avDates 
    End With 

    Exit Sub 

errTrap: 

    ExitPoint ("Error in ColumnsToText sub routine") 'pass control to error handling exitpoint sub 

End Sub

Excel VBA错误处理代码

相关推荐