Excel VBA入门(8): 快捷键/内置常量/代码调试/错误处理/代码优化
-------------------------常用快捷键：
Ctrl+Y 删除当前行
F5 运行当前一个sub
F8 执行本行（调试的时候可以用）
-----------------------内置常量
一般以vb 开头或者xl开头
vbBlack, vbRed, vbGreen, vbMonday, vbCrlf 回车与换行符结合。 vbCr 回车 , vbLf 换行
-------------------------
VBE有丰富的调试工具, 比如立即窗口, 本地窗口, 监视窗口, 断点调试...
第一个博文中已经讲过调试的基本操作: 设置断点, F5运行, F8逐条运行
断点就是程序中暂停停止运行的位置, 设置断点之后, 当运行到断点行所在的语句程序就进入中断模式,
此时在本地窗口和立即窗口中 可以查看变量以及对象的属性值.
1. debug 介绍: 调试工具的基石
debug.print x 在不中断程序的情况下输出x 的值
debug.assert 与if 类似, 用与判断一个条件是否成立, 但是if 语句不会暂定程序的执行, 如果assert方法的参数不成立,
程序会暂停进入中断模式.
Function assert_test(x As Integer, y As Integer) As Double Debug.Assert y <> 0 ' y=0则进入中断模式 If y <> 0 Then assert_test = x / y End If End Function Sub test() MsgBox assert_test(10, 2) ' 正常运行 MsgBox assert_test(10, 0) ' 在Debug.Assert y <> 0 程序会中断 End Sub
立即窗口的使用 : 立即窗口的输出如果超过200行, 就只显示最后200行的内容
打印的关键字是 print 或者?
print assert_test(10,3) 3.33333333333333 ?sqr(2) ' 按下Enter 1.4142135623731
其他
for each sh in sheets: debug.Print sh.name: next ' Enter Sheet8 Sheet9 Sheet10
其他
for i =1 to 4 : debug.Print i*i: next i 1 4 9 16
本地窗口的使用: 单步调试下, 本地窗口可以查看当前过程的所有变量和对象的状态(视图-->本地窗口), 很简单不叙述了...
监视窗口比本地窗口灵活, 可以自己选择想要查看的变量, 这个在Excel入门系列第一篇博文中已经讲过...
2. 错误处理
一般错误分为三种:
(1) 编辑错误: 不正确的代码导致的, 没有end if 等
(2) 运行时错误: 试图执行一个不可能完成的任务: 重命名已经打开的文件, 被除数是0....
(3) 逻辑错误: 语法正确, 操作正当情况下还是没有出想要的结果
on error 语句捕捉错误并进行处理, 告诉程序发生错误时需要转到哪个地方进行处理
on error goto line | resume next | goto 0
on error goto line 启动错误处理程序, line 参数表示行标签或者行号, 必须和 on error 在同一个过程中.
Sub errtest() On Error GoTo errhandle 'errhandle为标签 ChDrive "A" Exit Sub errhandle: MsgBox "程序出错, 请联系技术人员" & vbCrLf & "错误编号:" & _ Err.Number & "," & Err.Description, vbInformation, "错误提示" Err.Clear End Sub
vbcrlf 表示换行
chDrive "A" 表示磁盘驱动器A没有插入软盘时将弹出 设备不可用的 提示
得到结果:
或者用行号表示, 随便一个数字就行! 不是真的行号
Sub errtest() On Error GoTo 11 'errhandle为标签 ChDrive "A" Exit Sub 11 MsgBox "程序出错, 请联系技术人员" & vbCrLf & "错误编号:" & _ Err.Number & "," & Err.Description, vbInformation, "错误提示" Err.Clear End Sub
如果发生错误不影响后面的程序, 那么直接就写 on error resume next
3. 代码优化
实例1: 一般工作表函数比普通VBA代码更有效率
Sub test() Dim num As Long, i As Integer, tm As Date Dim Cell tm = Timer For i = 1 To 1000 ' 只是为了增加循环次数 For Each Cell In Range("A1:A80") num = num + Cell.Value Next Next i Debug.Print "普通运行时间1:" & Format((Timer - tm), "0.0000") & "秒" tm = Timer For i = 1 To 1000 num = WorksheetFunction.Sum(Range("A1:A80")) Next i Debug.Print "用sum函数的运行时间2:" & Format((Timer - tm), "0.0000") & "秒" End Sub
普通运行时间1:0.3359秒
用sum函数的运行时间2:0.0078秒.
实例2: 减少对象的激活或者选择
初学者很喜欢用录制宏, 但是录制宏得到代码很冗余, 有很多的select , activate 语句, 实际上很多对象的操作是不需要激活该对象的.
例如当前工作表是sheet1, 现在要给sheet2 的区域A1 赋值
' 录制宏得到的 Sheet2.Select Range("A1").Select ActiveCell.Value = "Hello" ' 实际很简单 Sheet2.Range("A1").Value = "Hello"
实例3: 少用variant 类型的变量
variant 比较省事, 但是variant 需要占用很多的内存,(integer 占用2个字节, long数据占用4个字节, 全数字的variant 数据占用16个字节)
你可以设置不同的变量进行运算10000次比较运行时间.
实例4: 减少用. 点的数量
对于对象属性的调用, 一般采用 object.method. 点的数量越少速度越快.
对同一对象的重复引用, 可以设置一个变量
ThisWorkbook.Sheets("try").Cells(1, 1) = 1 ThisWorkbook.Sheets("try").Cells(1, 2) = 3 ThisWorkbook.Sheets("try").Cells(1, 3) = 4 ' set Set w = ThisWorkbook.Sheets("try")
也可以用with 语句
With ThisWorkbook.Sheets("try") .Cells(1, 1) = 100 .Cells(1, 2) = 200 End With
实例4: 用数组代替range
如果只对range对象中的单元格的值进行处理, 而不用到单元格的属性和方法, 可以使用数组处理range对象.
数组的处理速度远远快于range对象的运算速度
Dim arr() arr = Range("A1:E400") ' 生成一个400行6列的二维数组
数组arr必须定义为variant 类型, 数组的下表下界是1, 并且不受option base 的影响.
注意, 即使引用的范围只有一行或者一列, 赋值的数组仍然是二维数组
arr(range("A1:A4"))
赋值之后, arr即为一个arr(1 to 44, 1 to 1)的二维variant 数组, 元素的下标为arr(1,1), arr(2,1), arr(3,1), arr(4,1).
经过数据处理之后, 把数组变量写入工作表区域中: range("A1:E400")=arr
实例5: 让代码"专注"运行--> 关闭屏幕刷新
在过程的一开始写: Application.ScreenUpdating = False
在结果的时候Application.ScreenUpdating = True 还原设置
实例6: 单元格/区域的表示方法
cells(1,1), range("A1"), [A1] 三种方法中cells(1,1)是最快的, [A1]最慢
但是三种方法各有各的优点, cells(1,1)可以精确的获得单元格的行列, range("A1") 可以获得属性与方法
对于工作表, sheets(1) 比sheets("sheet1") 速度快, 其实VBA看到名称时, 先把名称解析为索引.
