学习笔记

6.VBA代码调试与优化

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代码调试与优化”

发表回复