|
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语句来控制发生错误后的处理，只要处理得当，系统错误就能为我所用。 | 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2005-11-16 11:49
楼主
