1.VBA中可能会发生的错误
编译错误
如果编译时不遵循VBA的代码规则,如未定义比那辆、函数或属性名称拼写错误、语句不匹配(比如有If但漏掉End If,有For但漏掉Next)等,都会引起编译错误。
存在编译错误的程序,运行时系统会显示一个提示对话框,程序不会被执行。
运行时错误
如果程序在运行过程中视图完成一个不可能完成的操作,如除以0、打开一个不存在的文件、删除一个打开的文件等都会发生运行时错误。
运行存在运行时错误的程序,执行到错误代码所在行时,Excel会显示一个错误提示对话框。
逻辑错误
当程序中的代码没有语法问题,程序运行时也没有不能完成的操作,但程序运行结束后却无法得到预期的效果,这样的错误大概率时逻辑错误。
比如,用户想法是要把1到10的自然数依次写入A1:A10单元格,如果程序写成这样
Sub Ljcy()
' 声明一个整型变量i用作循环计数器
Dim i As Integer
' 开始一个从1到10的循环
For i = 1 To 10
' 错误:始终将值写入A1单元格
' 这行代码的问题在于,无论i的值是多少,
' 它总是在写入Cells(1, 1),也就是A1单元格
Cells(1, 1).Value = i
Next
End Sub
正确的代码应该是这样的
Sub Ljcy()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = i
Next
End Sub
编写程序时,很多原因都会引起逻辑错误,如:循环变量的初值和终值设置错误,变量类型不正确等,而这些代码单独存在并没有任何问题。
同之前的其他两种错误不同,存在逻辑错误的程序,运行后程序会正常执行,Excel并不会给出任何提示,所以,逻辑错误最难被发现,但在所有错误类型中占的比例却最大。
因此,在调试代码时,多数时间都是在修改程序中存在的逻辑错误。
2.VBA程序的3种状态
设计模式
设计模式是用户设计和编写VBA程序时所处的模式,当程序处于设计模式时,用户可以对程序进行任意修改。
运行模式
程序正在运行时的模式称为运行模式。
在运行模式下,用户可以通过输入输出对话框与程序“对话”,也可以查看程序的代码吗,但不能修改程序。
中断模式
中断模式时程序被临时中断执行(暂停执行)时所处的模式。
在中断模式下,用户可以检查程序存在的错误和修改程序的代码,可以逐句执行程序,一边发现错误,一边更正错误。
3.Excel已经准备好的调试工具
让程序进入中断模式
因为中断模式下可以一边运行程序,一边发现错误并修正错误,所以调试代码多数时间都选择在中断模式下进行。
出现编译错误时
如果程序存在编译错误,运行时系统会自动显示错误提示对话框。
对话框上有两个按钮,点击【帮助】按钮可以查看该错误的帮助信息,单击【确定】按钮即可进入中断模式。
进入中断模式后,程序停止在黄色底纹所在行,这时可以对程序进行任意修改
出现运行时错误
如果程序存在运行时错误,运行后会停在在发生代码 的所在行,自动显示错误提示对话框,这时候单机对话框上的【调试】按钮,让程序进入中断模式
中断一个正在执行的程序
如果程序中没有出现编译错误和运行时错误,程序会一直执行,直到结束位置,如果出现死循环,会一直执行不会种植。
如果当程序证字啊运行,当按下ESC键或CTRL+Break组合键后,系统会中断执行它,并弹出提示对话框,单击对话框中的“调试”按钮即可让程序进入中断模式
为程序设置断点
什么是断点
断电是在代码中设置的一个标记,它告诉VBA在执行到这一行时暂停程序的运行,让用户能在程序执行的特定位置停下来(不包含断点所在的行),检查当前的程序状态,比如变量的值、对象的属性等。
给程序设置断点
将光标移动到要设置断点的行,按F9键即可为该行设置断点,程序会在运行在这一行之前停下来
在菜单栏中的【调试-切换断点】也可以为某一行设置断点
还可以通过单击代码窗口的左侧位置添加断点
再次按F9可以取消断点,如果想要清楚程序中的所有断点,可以通过【调试——清除所有断点】,或者按快捷键【CTRL+SHIFT+F9】
使用Stop语句
给程序设置的断点会在关闭文件时自动被取消,如果希望在重新打开工作簿后继续保留之前设置的断点,可以使用Stop语句。
在程序里加入一个Stop语句,就像给程序设置了一个断点,当程序运行到Stop语句时,会停止在Stop语句所在行,进入中断模式,再次运行,会从Stop之后的代码开始。
使用立即窗口
如果你怀疑程序中的错误是因为变量设置错误引起的,可以在程序中使用Debug.Print语句将程序中变量或表达式的值输出到“立即窗口”中,程序运行结束后,在“立即窗口”中查看变量值的变化情况。
Sub SimpleErrorDemo()
Dim x As Integer
Stop
x = 10
Debug.Print x '在立即窗口中Print变量的值
x = x / 0 ' 这行会触发运行时错误
Debug.Print "This line won't be executed"
End Sub
如果程序处在中断模式下,也可以将光标移到变量名称上,直接查看变量的值
使用本地窗口
在中断模式下,还可以利用“本地窗口”查看变量的数据类型和当前值
可以通过【视图-本地窗口】来调出
使用监视窗口
在中断模式下还可以使用“监视窗口”观察程序中变量或表达式的值。
使用“监视窗口”来监视变量或表达式前,必须先定义要监视的变量或表达式,监视表达式可以在设计模式或中断模式下定义。
在监视窗口中右键空白处即可添加监视,完成后为程序设置断点,运行程序,就可以在【监视窗口】中看到相应的信息了。
只有当程序处于中断模式时才能使用“监视窗口”,所以只有将程序切换到中断模式,“监视窗口”才能正常工作。
如果想编辑或删除监视,就在右键菜单里选择相应的命令。
4.处理错误的艺术
有些错误时可以预先知道的,所以可以在程序中加入一些错误处理的代码,保证程序正常运行。
VBA通过On Error语句捕获运行时错误,该语句告诉VBA,如果运行程序时出现错误应该怎么做
On Error语句有3种形式。
Go Error GoTo标签
Go Error GoTo该语句告诉VBA,当发生错误时,继续执行标签所在行及之后的代码
Sub SimpleErrorDemo()
Dim x As Integer
x = 10
x = x * 2
Stop
On Error GoTo a
x = x / 0 ' 这行会触发运行时错误
a: MsgBox "出现错误后跳转到这里"
End Sub
On Error Resume Next
该语句告诉VBA,如果程序发生错误,继续执行错误行后面的代码。
如果在程序中加入On Error Resume Next语句,运行程序时,即使程序中存在运行时错误,也不会中断程序,显示错误信息,并且会继续执行错误语句之后的代码。
Sub SimpleErrorDemo()
Dim x As Integer
On Error Resume Next
x = 10
x = x * 2
x = x / 0 ' 这行会触发运行时错误
MsgBox "出现错误后继续执行这行代码,并且之前的错误不会报错"
End Sub
On Error Resume Next的作用范围:
- 当前过程(Sub或Function)结束
- 遇到另一个On Error语句
- 遇到 On Error Goto 0语句
On Error GoTo 0
使用On Error GoTo 0语句后,将关闭对程序中运行时错误的捕捉
作用:
1.禁用错误处理(关闭任何当前活动的错误处理历程,恢复VBA的默认错误处理行为)
2.重置错误处理(清除之前设置的任何自定义错误处理程序(如On Error Resume NExt或On Error GoTo Label)
3.默认行为(使VBA在遇到错误时显示标准的错误消息并中断程序执行)
如果在On Error GoTo 0语句开始后,代码再出现运行时错误,尽管承诺徐一开始已经陷入On Error GoTo标签或On Error Resume Next标签,运行时错误都不会被捕捉到
想象一下 VBA 程序是一辆正在行驶的汽车:
正常情况(默认错误处理):
汽车在正常行驶。
如果遇到障碍物(错误),汽车会立即停下(程序中断)。
司机(您)会收到一个警告(错误消息),告诉您发生了什么问题。
使用 On Error Resume Next:
相当于给汽车安装了一个特殊装置。
即使遇到小障碍物(错误),汽车也会继续前进,不会停下。
司机可能不会立即注意到已经撞到了什么。
On Error GoTo 0 的作用:
它就像是拆掉那个特殊装置。
汽车恢复到正常状态,遇到障碍物就会停下。
简单说,它”关闭”了 On Error Resume Next 的效果。
这部分其实没懂
5.让代码跑的更快一些
合理地使用变量
生命变量为合适的数据类型
不同的数据类型占用不同大小的内存空间,所占内存空间的大小直接影响计算机的处理数据的速度,因此为了提高效率,在在声明变量时,在满足需求的前提下,应该尽量选择占用字节少的数据类型。
尽量不使用Variant数据
Variant是一种特殊的数据类型,所有没有声明数据类型的变量都默认为Variant型,但Variant型所占据的存储控件远远大于其他数据类型。
不要让变量一直待在内存里
如果一个变量只在一个过程中使用,请不要声明它为公共变量,尽量减少变量的作用域。
如果不再需要使用某个变量(尤其是对象变量),记得释放它。
释放变量的代码为:
Set 变量名 = Nothing
避免反复引用相同的对象
无论是引用对象,还是调用对象的方法或属性,都会用到小数点.运算符,每次运行程序,计算机都会对这些点.运算符进行解析,当小点.运算符过多时,会花去不少时间。
引用对象不可避免,但当反复引用同一个对象时,可以用一些方法来简化他,从而减少小点.运算符
使用With语句简化引用对象
' 未优化的代码
Sub WithoutWith()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = "Hello"
ws.Range("B1").Value = "World"
ws.Range("C1").Formula = "=A1 & "" "" & B1"
ws.Range("A1:C1").Font.Bold = True
ws.Range("A1:C1").Interior.Color = RGB(200, 200, 200)
End Sub
' 使用 With 语句优化后的代码
Sub WithWith()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
With ws
.Range("A1").Value = "Hello"
.Range("B1").Value = "World"
.Range("C1").Formula = "=A1 & "" "" & B1"
With .Range("A1:C1")
.Font.Bold = True
.Interior.Color = RGB(200, 200, 200)
End With
End With
End Sub
使用对象变量
对于频繁使用的对象,可以将其赋值给一个对象变量,减少重复引用
Sub UsingObjectVariables()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("A1:C1")
With rng
.Value = Array("Hello", "World", "=A1 & "" "" & B1")
.Font.Bold = True
.Interior.Color = RGB(200, 200, 200)
End With
End Sub
使用Application.Caller属性
在工作表函数中,可以使用Application.Caller来引用调用单元格,避免重复使用Range对象:
Function CustomFunction()
With Application.Caller
.Font.Bold = True
.Interior.Color = RGB(200, 200, 200)
CustomFunction = "Formatted: " & .Value
End With
End Function
尽量使用函数完成计算
Excel已经准备了很多线程的函数(工作表函数和VBA内置函数),使用线程的函数比自己重新写能实现更高的效率。
去掉多余的激活和选择
如果你的程序时通过录制宏得到的,那里面一定有很多的激活和选择操作,及Activate方法和Select方法。
去掉多余的操作,可以让程序更简洁,降低阅读和调试的难度,还能提高程序运行的速度。
合理使用数组
下面程序吧1到65536的自然数写入A1:A:65536中
Sub InputArr()
' 定义一个变量用于记录程序开始执行时的时间(从午夜以来的秒数)
Dim start As Double
start = Timer ' 取得从午夜开始到程序运行时经过的秒数
' 定义一个长整型变量 i 和一个数组 arr,数组大小为 1 到 65536
Dim i As Long, arr(1 To 65536) As Long
' 使用循环将 1 到 65536 的数值依次存入数组 arr 中
For i = 1 To 65536
arr(i) = i
Next i ' 循环结束
' 将数组 arr 中的值批量写入到 Excel 工作表的单元格区域 A1:A65536
' 使用 Transpose 函数将一维数组转换为列向量
Range("A1:A65536").Value = Application.WorksheetFunction.Transpose(arr)
' 显示一个消息框,告知程序运行的时间(以秒为单位)
MsgBox "程序运行的时间约是 " & Format(Timer - start, "0.00") & " 秒。"
End Sub
如果想提高运行速度,可以先把数据写入数组,再通过数组批量写入单元格,如:
Sub InputArr()
Dim start As Double
start = Timer ' 取得从午夜开始到程序运行时经过的秒数
Dim i As Long, arr(1 To 65536) As Long
For i = 1 To 65536
arr(i) = i
Next
Range("A1:A65536").Value = Application.WorksheetFunction.Transpose(arr)
MsgBox "程序运行的时间约是 " & Format(Timer - start, "0.00") & " 秒。"
End Sub
将一维数组写入A列单元格前,必须先使用工作表的TRANSPOSE函数将数组进行转置,如想省去转置的计算步骤,可以直接将数组定义为一个多行一列的二位数组。
关闭屏幕更新
设置Application对象的ScreenUpdate属性为False,在程序运行过程中关闭屏幕更新,可以在一定成都上缩短程序运行的时间。
如果程序很短,需要做的操作很少则没这个必要。
1 thought on “6.VBA代码调试与优化”