1.2 程序防错要点
在编写VBA代码前，需要了解程序通常会产生哪些错误；在编写程序时，需要对程序可能出现的错误进行防范，以及指定出错时需要采取的措施。本节对 VBA 程序出错进行常见的原因分析，以及罗列所有错误的解释，并通过实例演示防错手法及“错误”的利用方案。
疑难4 程序出错有哪些原因
程序在运行前或者运行时都可能会出错，那么程序出错有哪些常见的原因呢？
解决方案
本例专注于程序出错的理论分析，辅助简单案例进行讲解。读者在编写代码时可以从文中提到的几个方面检查自己的代码是否存在疏漏。
操作方法
※ 常见错误分析 ※
程序出错包括编译错误和运行时错误。常见的编译错误包括命名错误、拼写错误、语法错误（不完整）、参数不完整。图1-9所示属于过程名称错误，Sub过程名、Function名及Names的名称不能以数字开始；图1-10所示则是方法“Shell”的拼写有误，多了一个“l”；图1-11所示属于IF…End IF语句缺少结束语句所致；图1-12所示属于参数不完整，DateSerial函数有三个必需参数，缺一不可。另一个比较常见的编译错误是“二义性的名称”，它表示模块中存在多个同名的过程，手工重命名Sub过程名或者Function过程名即可。
█ 图1-9 命名错误
█ 图1-10 拼写错误
█ 图1-11 语法不完整
█ 图1-12 参数不完整
运行时错误种类相当多，其中最常见的是代码中的值超过了允许的范围。例如工作簿中仅仅有3个工作表时调用“Sheets(4)”，则产生“下标越界错误”；而图1-13所示对行高的赋值超过了允许的范围0～409；图1-14所示的单元格列坐标105超过了允许的最大值16384；图1-15所示是因为变量赋值时超过了Byte型的有效范围0～255；而图1-16所示则因为名称的引用超过了1～255范围而出错，同类的还有数据有效性的公式引用表达式不能超过255个字符，一次创建工作表不能超过255个，字体大小只能在1～409，字体颜色ColorIndex只能在0～56等。对于Open方法打开不存在的文件名也属于超出范围，保存文件到不存在的磁盘等都可以算超过有效范围，在代码中需要加入判断才能防错。
█ 图1-13 行高超过有效范围0～409
█ 图1-14 单元格列坐标超过有效范围16384
█ 图1-15 对变量赋值时超过了变量的范围
█ 图1-16 名称的引用超过最大值255
另外比较常见的还有“被零除”错误。例如图1-17所示为C2为0时就会出错，而图1-18所示为a2:a9没有数值时仍然是因零除数而出错。
█ 图1-17 C2为0时产生被零除错误
█ 图1-18 当a2:a9没有数值时出错
原理分析
VBA中程序出错的可能性极多，但通常都有规律可循。其中编译错误最好处理，VBA会自动找出出错的语句并告知出错原因；运行时错误最常见的是读出和写入时超过有效范围。而所有有效范围皆有据可查，可以从帮助中获取所有信息。只要掌握其规律，编写程序时出错的几率就会大大减少。
知识扩展
本文罗列的是代码本身的错误，而实际工作中还会有思路错误，即代码完全正确，但无法获取需要的结果，这不在本书讨论的范畴。
程序的错误也有可以利用的时候，即故意通过程序代码来获取某些错误信息，在后面的很多案例中都将涉及到。
疑难5 如何获取VBA所有运行时错误的详细说明
将VBA中的运行时错误罗列到工作表中，方便学习，而且可以查看详细说明。
解决方案
要获取所有错误的编号及信息，可以利用Err对象的Raise方法人为制造错误，然后利用Err.Number取出错误编号，而用Err.Description属性返回错误信息。如果需要返回更详细的信息，则借用 Err.HelpFile 得到错误对应的帮助文件，以及 Err.HelpContext 得到错误主题的 ID号，最后用Application.Help方法可以根据错误ID号打开对应的帮助文件。
操作方法
步骤1 按【Alt+F11】组合键打开VBE窗口。选择菜单“插入”→“模块”，并输入以下代码：
Sub 获取所有错误类型编码及含义() '人工造成错误并获取错误信息
On Error Resume Next '错误时继续执行
Dim arr(), i As Integer
ReDim arr(1 To 1000, 1 To 2) '声明一个二维数组，1000行2列
Range("A1:B1") = Array("错误ID", "错误描述(单击查看详细描述)") '标题赋值
Range("A1:B1").Interior.ColorIndex = 3 '添加背景色
VBA.Err.Raise 1 ' 故意产生运行时错误，方便后面的操作
Cells(2, 1) = 1 '错误编号
Cells(2, 2) = Err.Description '错误类型
For i = 2 To 1000 '提取2到1000之间的错误类型
VBA.Err.Raise i '产生一个编号为i的错误（其中有重复）
'如果其错误类型不和编号1相同则取其编号与错误描述
If Error(i) <> "应用程序定义或对象定义错误" Then
arr(i + 1, 1) = Err.Number '将错误编号存入数组
arr(i + 1, 2) = Err.Description '将错误描述存入数组
End If
Err.Clear '清除错误，方便取下一个错误的相关信息
Next i
Range("A3:B1001") = arr '将数组写入单元格
Columns("A:B").AutoFit '自动调整列宽
'将A1:A1001中空单元格整行删除
Range("A1:A1001").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
步骤2 如果没有显示工程资源管理器则按【Ctrl+R】组合键打开工程资源管理器，双击当前工程中的“ThisWorkbook”，并在右边的代码窗口中输入以下代码：
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Column = 2 Then '仅在选择第二列时执行
On Error Resume Next '防错
Err.Raise Target(1).Offset(0, -1) '产生一个错误，错误号为A列的值
Application.Help Err.HelpFile, Err.HelpContext '打开对应的帮助文件
End If
End Sub
步骤3 双击工程资源管理器中的“模块1”，光标定位于过程“获取所有错误类型编码及含义”中任意位置，按【F5】键执行程序。
步骤4 关闭VBE窗口返回工作表界面，在A:B区域中已罗列出1到1000之间的运行时错误编号及错误描述，如图1-19所示。单击B4单元格，可以打开编号为A4单元格的错误信息的详细描述，如图1-20所示：
█ 图1-19 罗列所有运行时错误
█ 图1-20 编号为5的错误信息描述
原理分析
VBA中的ERR对象提供了一整套关于错误信息管理的完整方案，包括Err.Raise方法人工产生错误、Err.Number属性获取错误编号、Err.Description属性获取错误信息、Err.HelpFile属性获取帮助文件名称、Err.HelpContext 属性获取在帮助文件中的主题的上下文 ID，以及Err.Clear方法清除错误。将以上各属性、方法组合并循环即可获取所有运行时错误的帮助信息。
除了表中罗列的错误以外还有很多，不过它们的错误信息都和第一条一致，所以代码中利用“If Error(i) <> "应用程序定义或对象定义错误"”将它们排除了。
知识扩展
※ 通过Error函数获取错误信息 ※
Error函数也可以取得错误信息，和Err.Description属性功能相近。不过Error函数可以在任何时候获取指定编号的错误信息，而 Description 属性只有在程序出错时才能获取当前错误的信息描述。
利用Error函数取代Description属性也可以完成，且代码更简单，完整代码如下：
Sub 获取所有错误类型编码及含义2() '不产生错误，而利用Error函数来获取错误信息
Dim arr(), i As Integer
ReDim arr(1 To 1000, 1 To 2) '声明一个二维数组，1000行5列
Range("A1:B1") = Array("错误ID", "错误描述(单击查看详细描述)") '标题赋值
Range("A1:B1").Interior.ColorIndex = 3 '添加背景色
Cells(2, 1) = 1 '错误编号
Cells(2, 2) = Error(1) '错误类型
For i = 2 To 1000 '提取2到1000之间的错误类型
'如果其错误类型不和编号1相同则取其编号与错误描述
If Error(i) <> "应用程序定义或对象定义错误" Then
arr(i + 1, 1) = i '将错误编号存入数组
arr(i + 1, 2) = Error(i) '将错误描述存入数组
End If
Next i
Range("A3:B1001") = arr '将数组写入单元格
Columns("A:B").AutoFit '自动调整列宽
'将A1:A1001中空单元格整行删除
Range("A1:A1001").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
疑难6 如何在执行完毕后报告出错次数及原因
VBA 代码出错时，如果未通过代码控制程序，通常在第一次出错时中断程序；如果利用“On Error Resume Next”语句忽略错误又会让用户不知道是否产生过错误。那么如何实现当程序中有错误时就报告错误的次数及其原因呢？以图1-21所示的多工作表求平均成绩且报告最小值为例讲述设计思路。
█ 图1-21 成绩表
解决方案
利用On Error Goto Line让程序出错时执行指定标签处的语句，然后通过err. Description提取错误信息，再配合“Resume Next”语句执行下一句，直到记录所有错误。
操作方法
步骤1 按【Alt+F11】组合键打开VBE窗口。选择菜单“插入”→“模块”，并输入以下代码：
Sub 每个班求平均() '对每个班级每个人员计算平均成绩，且报告每个最差成绩（罗列每一次
错误信息的技术应用）
Dim ShtCount As Byte, RowCount As Byte, total, MinValue As Byte, ErrStr As String
On Error GoTo err '出现错误时执行Err标签后的语句
For ShtCount = 1 To 5 '循环五次'如果A列空白则中转到Line标签
If WorksheetFunction.CountA(Sheets(ShtCount).[a:a]) = 0 Then GoTo Line
For RowCount = 2 To Sheets(ShtCount).Cells(Rows.Count, 1).End(xlUp).
Row '循环每一行
'在第五列返回平均值，即每个学生的平均成绩
Sheets(ShtCount).Cells(RowCount, 5) = WorksheetFunction.Average
(Sheets(ShtCount).Cells(RowCount, 4).Offset(0, -2).Resize(1, 3))
'记录每个学生的总成绩
total = Application.Sum(Sheets(ShtCount).Cells(RowCount, 4).Offset
(0, -2).Resize(1, 3))
If MinValue = 0 Then MinValue = total
'如果MinValue变量为0则赋值第一个总成绩
If MinValue > total Then MinValue = total
'如果变量大于总成绩则对变量赋值为总成绩
Next RowCount
'记录每个班级（工作表名）的最小总成绩
TempStr = TempStr & Chr(10) & Sheets(ShtCount).Name & MinValue
MinValue = 0 '循环完一个工作表后将变量重置为0
Line:
Next
MsgBox TempStr '程序结束前报告每个班最小总成绩
If i > 0 Then MsgBox "出错" & i & "次，原因分别为：" & ErrStr
'如果有错误则报告次数和原因
Exit Sub '退出程序
err:
Application.StatusBar = err.Description '在状态栏显示错误原因
i = i + 1 '累加变量，该变量表示出错次数
ErrStr = ErrStr & Chr(10) & err.Description '将所有错误串连成一个字符串
err.Clear '清除当前错误
Resume Next '执行下一句代码
End Sub
步骤2 光标定位于代码中任意位置并按【F5】键执行过程，程序将对每个工作表中所有学生成绩计算平均值，并弹出图1-22所示的最差成绩列表和图1-23所示的错误次数及原因。
█ 图1-22 每个班级最差成绩列表
█ 图1-23 报告出错次数及原因
原理分析
※ 捕捉所有错误信息 ※
记录错误次数和原因主要基于“On Error GoTo err”、“err.Clear”和“Resume Next”三者的配合，缺一不可。“On Error GoTo err”表示遇到错误就执行“err”标签后的语句，而在该标签处提取错误信息后，通过“err.Clear”立即清除当前错误，然后利用“Resume Next”语句继续执行错误语句后面的代码。本例中“不能取得类 WorksheetFunction 的 Average 属性”是因为有一学生成绩为空白所致，修改方法是利用 Count 函数和 IF 判断是否存在数值，如果没有则略过；而两次“溢出”则由“MinValue As Byte”这个错误的变量声明造成，因为成绩大于255，可以将“Byte”修改成“Integer ”来解决；而“下标越界”则因为工作表总数为4，For...Next循环的上限为5。可以将5修改为“Sheets.count”，即自动计算工作表数。
知识扩展
Resume用于在错误处理程序结束后恢复原有的运行，通常搭配Next使用。
Application.StatusBar表示状态栏显示的文字，可读写，通常用它展示程序进度。
疑难7 如何开发完善的程序
编写代码解决一个工作问题是很简单的，但是如何让程序完善，可以适应所有环境，且通用、兼容、可防错，这是一门相当复杂的学问。那么如何开发一个完善的程序，程序开发常会有什么错误及如何防范呢？
解决方案
通过常规思路开发一段程序完成基本需求；然后查找存在的问题并进行完善；再对新的过程审核是否有新问题，继续完善，直到没有任何问题。
操作方法
步骤1 让用户从对话框输入一个值，并对该值开平方后写入活动单元格。根据题目需求，编写以下代码：
Sub 获取平方根1()
Dim Value As Long
Value = InputBox("请输入数值：", "待开方之数值", 0)
ActiveCell.Value = Sqr(Value)
End Sub
步骤2 执行以上过程，并输入100或者789、123.455等数据测试，可以发现实现了需求的功能。然而如果用户在对话框上单击“取消”按钮，那么程序会出错。修改代码如下：
Sub 获取平方根2() '解决单击“取消”按钮问题
Dim Value As Variant
Value = InputBox("请输入数值：", "待开方之数值", 0)
If Len(Value) = 0 Then Exit Sub
ActiveCell.Value = Sqr(Value)
End Sub
步骤3 再执行程序验证，单击“取消”按钮后程序会自动退出，具有了防错功能。然而输入一个负数，程序仍然会出错。继续改进代码：
Sub 获取平方根3() '解决负数问题
Dim Value
Value = InputBox("请输入数值：", "待开方之数值", 0)
If Len(Value) = 0 Then Exit Sub
If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox
"不能小于0"
End Sub
步骤4 当输入负数后，程序会提示用户，然后退出。但如果用户输入文本，程序仍然会出错，所以再次对代码做优化：
Sub 获取平方根4() '解决文字问题
Dim Value
Value = InputBox("请输入数值：", "待开方之数值", 0)
If Len(Value) = 0 Then Exit Sub
If VBA.IsNumeric(Value) Then
If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
不能小于0"
Else
MsgBox "不能输入文本", 64, "提示"
End If
End Sub
步骤5 如果输入文本，程序具有了识别并警告用户的功能。然而，活动表是图表时，执行程序仍然会出错。完善的程序需要处理所有意外，那么程序可以做如下改进：
Sub 获取平方根5() '解决图表问题
Dim Value
If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要选择图表": Exit Sub Value = InputBox("请输入数值：", "待开方之数值", 0)
If Len(Value) = 0 Then Exit Sub
If VBA.IsNumeric(Value) Then
If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
不能小于0"
Else
MsgBox "不能输入文本", 64, "提示"
End If
End Sub
步骤6 如果工作表被保护状态下执行以上程序仍然会出错，继续完善代码：
Sub 获取平方根6() '解决工作表保护问题
Dim Value
If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要选择图表": Exit Sub
If ActiveSheet.ProtectContents Then MsgBox "工作表已保护": Exit Sub
Value = InputBox("请输入数值：", "待开方之数值", 0)
If Len(Value) = 0 Then Exit Sub
If VBA.IsNumeric(Value) Then
If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
不能小于0"
Else
MsgBox "不能输入文本", 64, "提示"
End If
End Sub
步骤7 如果活动单元格处于数组区域之间，程序仍然会产生错误，所以最后将代码优化为：
Sub 获取平方根7() '解决数组区域问题
Dim Value
If TypeName(ActiveSheet) = "Chart" Then MsgBox "不要选择图表": Exit Sub
If ActiveSheet.ProtectContents Then MsgBox "工作表已保护": Exit Sub
Value = InputBox("请输入数值：", "待开方之数值", 0)
If Len(Value) = 0 Then Exit Sub
If VBA.IsNumeric(Value) Then
On Error Resume Next
Debug.Print ActiveCell.CurrentArray
If Err = 0 Then MsgBox "请不要选择数组区域": Exit Sub
If Not Value < 0 Then ActiveCell.Value = Sqr(Value) Else MsgBox "
不能小于0"
Else
MsgBox "不能输入文本", 64, "提示"
End If
End Sub
原理分析
编程的基本条件是准确性。然而程序除了准确以外，还必须具备防错、通用的功能，否则代码在当前状态下正确执行，环境稍加变化就出现错误，将会增加很多维护成本。一个好的程序应该尽量通用于所有状况，而本例正是通过一个典范来展示程序的完善过程，让读者对程序可能出现的错误进行了解，并提供所有错误的解决之道。在实际工作中，都应该严格按此思路编写代码，提升程序的准确性、纠错性，同时也减少维护成本。
知识扩展
※ 数组区域对VBA程序的影响 ※
区域数组公式是同时存在于多个连续单元格中的带有“{}”标志的公式，将它输入到工作表后就将占据一个区域，而非一个单元格，该区域即为数组区域。它的特性是不能单独修改区域中任意一个单元格，如果代码修改其中一个单元格，程序会中断。
本例中也可以利用“On Error Resume Next”一次性解决所有问题，其代码如下。不过如果执行程序后得不到结果，就无法知道出错的原因。
Sub 获取平方根8() '解决所有问题
On Error Resume Next
ActiveCell.Value = Sqr(Application.InputBox("请输入数值：", "开平方",
0, , , , , 1))
End Sub
注意
本书中编程的主题是准确性、效率、防错性和兼容性，代码一定要对所有错误进行防范，确保程序通用。然而为了节约篇幅，让书中展现更多内容，所以尽量减少重复代码，对于工作表是否保护、当前表是否为图表，以及活动单元格是否在数组区域之间就不再对每个案例都进行判断了，只对其他出错的可能性进行防错。但读者在实际工作中应该全面进行防错。
疑难8 防错语句在程序或者函数中应用有哪些优势
运行代码时出现错误绝非程序员喜闻乐见之事。然而在特殊情况下，正如蛇的毒液也有它的良性作用一样，编程时也会有意地利用代码错误来工作。那么如何在编辑过程中体现防错语句的优势呢？
解决方案
本例通过一个SUB过程和一个Function过程展示如何利用错误让程序更完善。有意地制造错误，并根据错误来判断工作表是否存在及单元格是否存在数据有效性设置。
操作方法
步骤1 以指定日期为基数，创建一个月的工作表，每个工作表按每天的日期命名，代码如下：
Sub 按月创建新表() '创建一个月的工作表
Dim Date1 As Date '声明一个日期变量
Date1 = #1/15/2009# '指定基数
'从基数日期开始，到下月同日的前一天，
For i = Date1 * 1 To DateAdd("m", 1, Date1) * 1 - 1
Sheets.Add after:=Sheets(Sheets.Count) '创建一个新表，放到末尾
ActiveSheet.Name = Format(i, "mm月dd日") '命名为日期
Next
End Sub
步骤2 以上程序基本可以实现需要的功能，然而程序并不完善，如果工作簿中有某个工作表与该时间段的日期同名，那么程序会出错并中断执行。为了提升程序兼容性，应该判断是否存在同名工作表，如果没有就新建工作表并命名，否则忽略该日期。优化后的代码如下：
Sub 按月创建新表2() '创建一个月的工作表
Dim Date1 As Date '声明一个日期变量
Date1 = #1/15/2009# '指定基数
On Error Resume Next'错误时继续执行下一句
'从基数日期开始，到下月同日的前一天
For i = Date1 * 1 To DateAdd("m", 1, Date1) * 1 - 1
Debug.Print Sheets(Format(i, "mm月dd日")).Name '输出工作表名
'如果无错误（表示存在名为Format(i, "mm月dd日")的工作表），则执行line后面的语句
If Err = 0 Then GoTo line
Sheets.Add after:=Sheets(Sheets.Count) '创建一个新表，放到末尾
ActiveSheet.Name = Format(i, "mm月dd日") '命名为日期
line: '指定一个标签，在指定条件时可以跳转至此处
Next
End Sub
步骤3 在自定义函数中，错误同样有利用价值。例如判断单元格是否设置了数据有效性，没有直接的函数或者属性来判断，例如，HasFormula可以一步直接判断单元格是否有公式。本例以开发一个判断单元格是否存在有效性设置来演示防错的应用，代码如下：
'定义一个函数，判断单元格是否有数据有效性设置，结果为True或者False Function IsValidation(rng As Range) As Boolean On Error Resume Next '错误时继续执行 Dim Str As String '提取单元格的有效性公式，如果参数为区域则取左上角单元格 Str = rng(1).Validation.Formula1 IsValidation = (Err.Number = 0) '公式结果由是否产生错误来决定 End Function
步骤4 对单元格A1设置任意数据有效性，并在B1输入公式“=IsValidation(A1)”，那么公式的结果将为“TRUE”，表示有数据有效性设置，如图1-24所示；而图1-25所示的新工作表则是执行过程“按月创建新表”的结果，创建了数量等于一个月天数的新工作表，且以日期命名。
█ 图1-24 验证函数IsValidation
█ 图1-25 创建一个月的工作表
原理分析
单元格是否有公式、是否保护等有现成的属性可以直接判断，而是否有批注或者是否设置数据有效性则没有现成的函数或者属性进行判断。
如果单元格有数据有效性设置，那么读取有效性设置的 Formula1属性会失败。基于这一特性，那么在代码中让程序出错时继续执行，然后检查Err.Numner属性是否等于0就可以判断是否存在有效性了。而检查指定名称的工作表、工作簿是否存在也基于这个道理。
知识扩展
※ 利用Err.Number属性判断错误类型 ※
程序中没有“运行时错误”时，Err.Number属性值为0；而如果产生了错误，则根据错误类型会产生不同的编号，可能是正数，也可能是负数。
如果程序有多个错误，要注意Err.Number取最近一次错误编号。那么有可能该错误不是预想中的错误，此时需要判断具体的Err.Number，而不是用“<>0”来判断结果。