有句俗话说的好,一段优秀的代码,三分靠编写七分靠调试。今天我就给大家聊一下VBA代码调试的问题:一段代码写完了,运算结果却不对,到底应该如何发现并改正错误?
本章概要如下:
❶ 语法检查
❷ 逻辑检查
❸ 样本调式
❹ 运行调试
❺ 错误处理
1、语法错误
对于新手而言,初期编写的VBA代码并不会有复杂的逻辑,最常见的错误就是语法错误。
典型有以下3种。
1) 声明对象变量,漏了关键字Set。
Sub t()
Dim sht As Worksheet
sht = Worksheets(1)
MsgBox sht.Name
End Sub
以上代码第3行将第1个工作表赋值变量sht,但由于并未使用关键字Set,代码会返回下图所示的错误信息:对象变量或with块变量未设置。
正确代码如下:
Sub t()
Dim sht As Worksheet
Set sht = Worksheets(1)
MsgBox sht.Name
End Sub
2)循环或判断语句不完整。
当有多层循环语句或者条件判断语句嵌套时,新手朋友容易遗漏Next或者End If语句。需要注意的是,当If语句嵌套在循环语句中时,如果缺少End If,系统会提示”编译错误,Next没有For”。这提示张冠李戴的不要太明显。
以下代码缺失End If语句。
Sub t3()
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name = "看见星光" Then
If sht.Cells(1, 1) = "excel" Then
MsgBox "对"
'这里少了End If 你发现了吗?
Next
End Sub
运行后提示错误如下:
解决此类错误,最好是养成代码缩进与提前输入结构语句的习惯。关于代码缩进的规则,VBA系列教程里有详细的讲述,这里不再啰嗦。而输入结构语句是指…
写了For语句后,立刻空两行写Next语句,再在循环体中编写其它语句。
For Each sht In Worksheets
Next
写了If语句后,也空两行写End If语句。
If sht.Name = "看见星光" Then
End If
3)工作表对象缺失
这个错误基本上每个VBA学员都遇见。
有段代码如下:
代码看不全可以左右滑动…
Sub t4()
Dim arr
arr = Worksheets(1).Range("a1:b" & Cells(Rows.Count, 1).End(xlUp).Row)
End Sub
第3行代码将第1个工作表的A:B列数据区域赋值数组arr。其中
Cells(Rows.Count, 1).End(xlUp).Row部分,
本意是返回Worksheets(1)第1列最后一个存在数据的单元格的行号,这代码看起来似乎正常无误。
但是,我们在VBA教程里讲过,如果单元格前未指定工作表对象,则默认为当前活动工作表——当前活动工作表,未必就是Worksheets(1),代码运行后,arr数组也就未必会返回正确的结果。
正确代码参考如下:
Sub t4()
Dim arr
With Worksheets(1)
arr = .Range("a1:b" & .Cells(Rows.Count, 1).End(xlUp).Row)
End With
End Sub
注意Cells前有个.代表With所引用的Worksheets(1)对象。
2、逻辑错误
相比于语法错误,麻烦的是逻辑错误。
代码运算的逻辑,有些来源于数据分析与处理的基本逻辑,有些来源于公司的业务逻辑。对于后者,往往只有行业内的人才能通过你的描述快速理解。
这时就有可能发生这样的情景:有的朋友发出来一段代码,也不说运算逻辑,就问为什么代码运行后不提示错误,但结果并不对……
坦白的说,这种行为就给有人问为什么输入公式1+1不提示错误,但结果也不等于预想的3,差不了多少——就让人很无语。
如何梳理逻辑错误呢?
首先,正如我们一直强调的,所谓编程,就是顺序、分支和循环。顺序就是运算的先后顺序,分支就是运算的条件层次,循环就是遍历数据,所以请养成做思维导图的习惯,通过思维导图梳理清楚代码运算的顺序和条件层次——相信我,这非常有助于你快速而准确的编写代码。
然后,在代码中尽量增加注释。注释的好处我们在VBA系列教程中编写VBA代码有哪些注意事项里有详细解释,像我这么傲骄的人,这里不再重复,你懂得图片。
最后,请继续往下看(*^▽^*)
3、样本调试
不论是检查代码的语法错误还是逻辑错误,都离不开样本调试;也就是用一个样本数据逐步运行代码,发现并修正错误。上面这句话包含了两个重点词汇:样本数据、逐步运行。
样本数据要求小而全。
小是指数据量必须小,比如,你需要从如上图所示的10万行数据中查找A列包含关键字”上海”、”福建”、”广东”,同时B列性别等于男的结果表,你不能拿10万个数据一个一个去测试,这样你不是风儿也是沙;实际上,有3条左右的样本数据就足够了。
全是指数据的代表性需全面,依然以上图所示数据为例,C列的性别就不能只有男的,没有女的,当然,也不能只有女的,没有男的。
参考代码如下:
Sub t()
Dim aData, aRes, aRef, s
Dim i As Long, j As Long, k As Long
aData = Worksheets("数据源").Range("a1").CurrentRegion
ReDim aRes(1 To UBound(aData), 1 To UBound(aData, 2))
aRef = Array("上海", "福建", "广东")
For i = 1 To UBound(aData)
If aData(i, 3) = "男" Then '判断性别是否为男
For Each s In aRef '判断是否包含城市关键字
If InStr(aData(i, 1), s) Then
k = k + 1
For j = 1 To UBound(aData, 2)
aRes(k, j) = aData(i, j)
Next
Exit For '退出循环
End If
Next
End If
Next
Worksheets("结果表").Select
Cells.ClearContents
Range("a1").Resize(1, UBound(aData, 2)) = aData '读取标题
Range("a2").Resize(k, UBound(aRes, 2)) = aRes
MsgBox "ok"
End Sub
4、代码调试
重点说一下代码逐步调试,这包含了逐语句调试、断点调试等情况。
逐语句调试是指以语句为单位分步运行代码。按一次键,VBA将运行当前过程,然后高亮显示下一个语句并进入中断模式。按多次键,即可逐语句运行代码。
当代码逐语句运行时,我们可以通过本地窗口,实时查看变量内容是否符合计算预期。
断点调试就是在程序中设置代码暂时停止运行的位置,这个位置被称为断点。当代码运行到断点所在的语句时,程序会进入中断模式,同时高亮显示断点代码行。
设置断点最常用的方法是将鼠标指针悬停在【代码窗口】左侧灰色区域内,当鼠标指针显示为指向左上方的箭头时,单击即可设置该代码行为断点。
断点设置完成后,会出现一个红色大圆点,单击该断点标识,即可删除断点。
断点可以存在多个,如果存在断点,按键后,VBA将运行代码直至断点处进入中断模式。此时,通过本地窗口,或搭配运行MsgBox语句,可以查看代码中的变量值是否运行有误。
除此之外,使用Stop语句也可以实现断点调试的效果。
以上述代码为例,如果需要查看变量K的累加过程,可以在语句k=k+1后添加一行Stop语句,代码运行到Stop语句时将自动进入中断模式,再通过本地窗口,即可查看相关变量的数据。
不管是逐语句调试还是断点调试,都是为了查看代码的运算过程,以及变量的值是否正确。
查看变量优先推荐使用本地窗口,但有时候本地窗口的变量过多,如果只是查看个别变量,使用起来就不是很方便,相比之下,使用Msgbox语句更为合适。
以上述案例为例,如果需要查看第1条符合查询规则的行号,可以在If判断语句后增加以下两行代码。
…
MsgBox i
Stop
….
代码运行后返回结果如下图所示。
本地窗口和Msgbox语句都是显示某个运算环境下的特定值,如果需要查看特定变量在整个过程中的全部值,可以使用Debug对象的Print方法,该方法可以在【立即窗口】打印不同类型的数据。
还是举个例子。
在第10行If语句后,增加一行Debug.Print (i)语句,然后运行过程,可以在立即窗口查看所有符合条件的所有行号。
Debug.Print 比较常用的一个情景是测试不同代码的运行速度。
以下代码测试了将10000个数据写入工作表的两种方式的时间差异,这两种方式一个是逐个单元格写入,另一个是数组批量写入。
Sub t2()
Dim i As Long, arr, t
t = Timer
For i = 1 To 10000 '在1万个单元格中写入数据
Cells(i, 1) = i
Next
Debug.Print ("逐个单元格写入的时间是:" & Timer - t)
t = Timer
ReDim arr(1 To 10000, 1 To 1)
For i = 1 To 10000
arr(i, 1) = i
Next
Range("b1:b" & UBound(arr)) = arr
Debug.Print ("数组写入的时间是:" & Timer - t)
End Sub
运行代码后结果如下图所示:
最后需要补充说明两点:
1)当过程重复运行时,立即窗口的内容并不会自动清除。
2)除了将变量数据写入立即窗口,也可以将其写入工作表中,两者各有优劣,看个人习惯和实际需求。
5、错误处理
无论你如何认真的编写代码,程序运行时仍然有可能出现错误,这也许会让初学编程的你感到困惑,但从某种角度来说,错误确实是程序不可或缺的一部分,所以请躺平微笑面对错误,并坚定不移的抱有三种态度:忽视它、捕捉它、反馈它。
使用On Error Resume Next语句,可以忽视程序中的错误,继续运行错误语句后的代码。
以下代码删除名称为”数据”的工作表。为了防止工作簿不存在相关名称的工作表,造成第4行删除工作表的代码运行错误,第3行代码使用容错语句。
Sub t3()
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("数据").Delete
MsgBox "名称为数据的工作表已删除"
Application.DisplayAlerts = True
End Sub
捕捉和反馈错误可以使用Err对象。
举个例子,还是删除名称为”数据”的工作表,示例代码如下:
Sub t4()
Dim d As Object
Application.DisplayAlerts = False
Set d = CreateObject("scripting.dictionary") '演示释放变量
On Error GoTo ErrHander
Worksheets("数据").Delete
MsgBox "名称为数据的工作表已删除"
Application.DisplayAlerts = True
errExit:
Set d = Nothing
Exit Sub
ErrHander:
MsgBox "程序发生错误。" & vbCrLf & _
"错误编号:" & Err.Number & vbCrLf & _
"错误内容:" & Err.Description
Resume errExit
End Sub
第5行代码是On Error GoTo line语句。它可以跳转到指定的错误处理程序入口,line代表代码行标签或行号,本例为ErrHander。
第12至第16行代码是ErrHander标签。第14行代码使用Err对象的Number属性返回错误的编号,第15行代码使用Err对象的Description返回错误的描述内容(这描述大部分时候不讲人话,如下图所示,就凑合用吧)。
第9至第11行代码是errExit标签,作用是释放指定对象的内存。
使用Err.Number属性可以判断程序是否存在错误。
以下代码删除名称为”数据”的工作表。如果不存在相关工作表,则告知用户。
Sub t5()
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("数据").Delete
If Err.Number = 0 Then
MsgBox "名称为数据的工作表已删除"
Else
MsgBox "不存在名称为数据的工作表"
End If
Application.DisplayAlerts = True
End Sub
第5行代码判断当前程序是否存在错误,当程序不存在错误时,Err的Number属性为0。当程序存在错误时,Number属性可能是正数也可能是负数,有学员将判断条件写成Err.Number > 0是错误的。
最后,使用Err.Clear可以清除Err对象的所有属性,即清除错误。
假设需要删除工作表名称为”工作表1″, “工作表2”, “工作表3″,并将删除的和不存在的分别弹窗告诉用户,可以参考下代码遍历删除。
Sub t6()
Dim aData, strName
Dim strDelName As String, strErrName As String
On Error Resume Next
Application.DisplayAlerts = False
aData = Array("工作表1", "工作表2", "工作表3")
For Each strName In aData
Err.Clear
Worksheets(strName).Delete
If Err.Number = 0 Then
strDelName = strDelName & "," & strName
Else
strErrName = strErrName & "," & strName
End If
Next
MsgBox "以下工作表已删除:" & vbCrLf & Mid(strDelName, 2) & vbCrLf & _
"以下工作表不存在:" & vbCrLf & Mid(strErrName, 2)
Application.DisplayAlerts = True
End Sub
第4行代码忽视程序运行中的错误。
第8行代码在每次删除工作表前都清除Err对象的所有属性。第10行代码判断Err对象的编号是否为0,如果为0,说明工作表成功删除,否则,就假设工作簿中不存在相关工作表(摊手,是的,事实上,也有可能是工作簿结构被保护了)。
代码运行后返回结果如下:
6、小结
同志们呐,代码调试是一个需要保持耐心和细心的过程,这里重复一句话(小学老师说过这叫首尾呼应),一段优秀的代码三分在编写七分在调试,写一段代码你可能只需要十分钟,而调试却需要1小时——这都是很正常的。最后,用大老板的一句话勉励大家:
“唯有不忘初心、牢记使命,戒骄戒躁、砥砺前行,方能行稳致远。”
来源:https://mp.weixin.qq.com/s/YY0_eHG1ppS17AKrlnC7wA