[讨论]谈谈关于错误捕捉的用法

VBA调试过程中，如何处理出现的系统错误提示往往是很头痛的事情。但是如果了解到错误产生的根源，错误捕捉反而成了我们编程时一个有力的工具。下面有几个例子可供大家参考，希望大家能把自己所知道的错误捕捉的技巧拿来一起分享。

范例1：判断工作簿内是否存在指定工作表

Public Function SheetExists(ByVal Sheetname As String, Optional ByVal wb As Workbook) As Boolean If IsMissing(wb) then set wb=ThisWorkbook On Error GoTo ErrHandler '出现错误的时候跳到ErrHandler的标签 Dim Result As Boolean, s As String Result = False s = wb.Sheets(Sheetname).Index '如果工作表不存在的话，会发生错误，跳到ErrHandler Result = True ErrHandler: SheetExists = Result End Function

范例2：判断工作簿内Sheet的类别

Public Function SheetType(ByVal wb As Workbook, ByVal wsName As String) As String

Dim intType As Integer, Temp With wb.Sheets(wsName) On Error GoTo D intType = .Type 'DialogSheet无Type属性，错误时跳至标签D If intType = -4167 Then SheetType = "Worksheet": Exit Function 'Worksheet的Type值为-4167 On Error GoTo C If intType = 3 Then Temp = .Cells(1, 1): SheetType = "MacroSheet": Exit Function 'MarcroSheet通ChartSheet的Type值都是3，这里利用能否应用表内单元格[A1]来判断 End With C: SheetType = "ChartSheet": Exit Function D: SheetType = "DialogSheet" End Function

范例3：统计一个数列里非重复元素的个数或返回一个非重复元素的数组，并可对对应项数据进行求和运算

Function UniqueItems(ByVal rngIndex As Range, Optional ByVal rngValue As Range, Optional ByVal Counter As Boolean) As Variant 'rngIndex为要索引的数列，rngValue为需求和的数列，Counter用来判断是否返回非重复项个数还是数组 Dim Coll, T, s Dim arrIndex, arrValue, myArr, Target(), Amt() Dim i%, x%, j% On Error GoTo 0 Set Coll = CreateObject("scripting.dictionary") '生成一个字典 arrIndex = rngIndex arrValue = rngValue ReDim Target(1 To UBound(arrIndex), 1 To UBound(arrValue, 2) + 1) 'Target数组的第一列为非重复元素的集合，之后的列用来储存rngValue里对应项的合计数 i = 1 Err.Clear '清除错误 For x = 1 To UBound(arrIndex, 1) On Error Resume Next '保证错误不影响程序继续执行 Coll.Add arrIndex(x, 1), i '在字典中添加元素 If Err.Number = 457 Then '如果发生编号457的错误时，表明关键元素已添加在字典中了，进行对应数值合计 s = Coll(arrIndex(x, 1)) s = CLng(s) For j = 1 To UBound(arrValue, 2) If Target(s, j + 1) <> "#Error" Then Target(s, j + 1) = IIf(IsNumeric(arrValue(x, j)), arrValue(x, j), "#Error") + Target(s, j + 1) '如果rngValue中存在文本，则返回#Error End If Next j Else '如未发生错误，表明该关键元素未在字典里发现重复项，则对其Value进行赋值 Target(i, 1) = arrIndex(x, 1) For j = 1 To UBound(arrValue, 2) Target(i, j + 1) = IIf(IsNumeric(arrValue(x, j)), arrValue(x, j), "#Error") Next j i = i + 1 End If Err.Clear '清除错误 On Error GoTo 0 '清除on error resume next设置 Next x If Counter Then UniqueItems = i - 1 Else UniqueItems = Target End Function

范例4：Excel帮助提供的用来捕捉错误的程序

Sub ErrPrompt()

Dim Msg ' 如果有错误发生，就构成一个错误信息 On Error Resume Next ' 改变错误处理的方式。 Err.Clear Err.Raise 6 ' 生成一个溢出（Overflow）的错误。 ' 检查错误代号，显示相关错误信息。 If Err.Number <> 0 Then Msg = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description MsgBox Msg, , "Error", Err.Helpfile, Err.HelpContext End If End Sub

综合来看，对错误发生时的控制，首先要确定错误发生的可能性，然后利用on error语句来控制发生错误后的处理，只要处理得当，系统错误就能为我所用。

嗯，学习借鉴ing。

学习了,谢谢!