|
第四章 Excel VBA优化及结束语
第一节 Excel VBA优化 由于Microsoft Office办公套件的广泛应用，以及该软件版本的不断提升，功能不断完善，在Office办公套件平台上开发出的的VBA应用程序越来越多，而VBA是一种宏语言，在运行速度上有很大的限制。因此VBA编程的方法直接关系到VBA程序运行的效率，本节列举了一些提高VBA程序运行效率的方法。 方法1：尽量使用VBA原有的属性、方法和Worksheet函数
由于Excel对象多达百多个，对象的属性、方法、事件多不胜数，对于初学者来说可能对它们不全部了解，这就产生了编程者经常编写与Excel对象的属性、方法相同功能的VBA代码段，而这些代码段的运行效率显然与Excel对象的属性、方法完成任务的速度相差甚大。例如用Range的属性CurrentRegion来返回Range 对象，该对象代表当前区。（当前区指以任意空白行及空白列的组合为边界的区域）。同样功能的VBA代码需数十行。因此编程前应尽可能多地了解Excel对象的属性、方法。
充分利用Worksheet函数是提高程序运行速度的极度有效的方法。如求平均工资的例子：
For Each c In Worksheet(1).Range(″A1:A1000″)
TotalValue = TotalValue ＋ c.Value
Next
AverageValue = TotalValue / Worksheet(1).Range(″A1:A1000″).Rows.Count 而下面代码程序比上面例子快得多：
AverageValue=Application.WorksheetFunction.Average(Worksheets(1).Range(″A1:A1000″)) 其它函数如Count,Counta,Countif,Match,Lookup等等，都能代替相同功能的VBA程序代码，提高程序的运行速度。 方法2：尽量减少使用对象引用，尤其在循环中
每一个Excel对象的属性、方法的调用都需要通过OLE接口的一个或多个调用，这些OLE调用都是需要时间的，减少使用对象引用能加快VBA代码的运行。
1)．使用With语句。
Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.Name=″Pay″
Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.FontStyle=″Bold″ ...
则以下语句比上面的快
With Workbooks(1).Sheets(1).Range(″A1:A1000″).Font
.Name = ″Pay″
.FontStyle = ″Bold″
...
End With
2)．使用对象变量。
如果你发现一个对象引用被多次使用，则你可以将此对象用Set 设置为对象变量，以减少对对象的访问。如：
Workbooks(1).Sheets(1).Range(″A1″).Value = 100
Workbooks(1).Sheets(1).Range(″A2″).Value = 200
则以下代码比上面的要快：
Set MySheet = Workbooks(1).Sheets(1)
MySheet.Range(″A1″).Value = 100
MySheet.Range(″A2″).Value = 200
3)．在循环中要尽量减少对象的访问。
For k = 1 To 1000
Sheets(″Sheet1″).Select
Cells(k,1).Value = Cells(1,1).Value
Next k
则以下代码比上面的要快：
Set TheValue = Cells(1,1).Value
Sheets(″Sheet1″).Select
For k = 1 To 1000
Cells(k,1).Value = TheValue
Next k 方法3：减少对象的激活和选择
如果你的通过录制宏来学习VBA的，则你的VBA程序里一定充满了对象的激活和选择，例如Workbooks(XXX).Activate、Sheets(XXX).Select、Range(XXX).Select等,但事实上大多数情况下这些操作不是必需的。例如
Sheets(″Sheet3″).Select
Range(″A1″).Value = 100
Range(″A2″).Value = 200
可改为：
With Sheets(″Sheet3″)
.Range(″A1″).Value = 100
.Range(″A2″).Value = 200
End With 方法4：关闭屏幕更新
如果你的VBA程序前面三条做得比较差，则关闭屏幕更新是提高VBA程序运行速度的最有效的方法，缩短运行时间2/3左右。关闭屏幕更新的方法：
Application.ScreenUpdate = False
请不要忘记VBA程序运行结束时再将该值设回来：
Application.ScreenUpdate = True 方法5：变量类型确定,少用变体变量
Option Explicit 语句, 在模块级别中使用，强制显式声明模块中的所有变量。 如果模块中使用了 Option Explicit，则必须使用 Dim、Private、Public、ReDim 或 Static 语句来显式声明所有的变量。如果使用了未声明的变量名在编译时间会出现错误。如果没有使用 Option Explicit 语句，一般所有未声明的变量都是 Variant 类型的。
注意 使用 Option Explicit 可以避免在键入已有变量时出错，在变量的范围不是很清楚的代码中使用该语句可以避免混乱. 方法6：关闭Excel系统提示
'本示例关闭所有打开的工作簿。如果某个打开的工作簿有改变，Microsoft Excel 将显示询问是否保存更改的对话框和相应提示。
Workbooks.Close
实际开发程序时,需要关闭提示信息对话框,给用户简洁高效的体验.
Application.DisplayAlerts = False '信息警告关闭
请不要忘记VBA程序运行结束时再将该值设回来：
Application.DisplayAlerts = True '信息警告开启
关闭信息警告后, 保存文档及关闭需要先保存,在关闭
Workbooks("filename.xls").Save '文件保存
Workbooks("filename.xls").Close SaveChanges:=True
'文件关闭, 不出现是否要保存的窗口,并保存所有对此工作簿的更改。
Workbooks("BOOK1.XLS").Close SaveChanges:=False
'本示例关闭 Book1.xls，并放弃所有对此工作簿的更改。
这样可以提高程序的简洁性,给用户服务.
方法7: 提高关键代码和循环代码的效率
不同方法执行效率的差异，但千万不要因为追求效率而损失了代码的可读性、清晰性。效率的优化必须是针对关键代码的优化，对于一些在程序执行过程中，只执行很少次数的代码，没有必要牺牲可读性而进行优化。对于代码执行效率，千万不要人云亦云，必要时候，自己动手测试一下，结果往往会出 乎意料。
代码执行时间的测算VBA和VB中，没有专门的代码执行事件测算工具和方法，笔者一般是使用Timer函数，其返回值是一个Single类型的数值，代表从午夜开始到现在经过的秒数，此数值包括小数部分，但精确程度在Windows NT，2000和XP下大概接近10毫秒。如果要测试一段代码的执行速度，可以使用如下方法：
#001 Sub MeasureTime()
#002
#003 Dim Time1 As Single, Time2 As Single
#004 Dim TotalTime As Single
#005 Dim Times As Long
#006 Dim i As Long
#007
#008 Times = 10000
#009 Time1 = Timer
#010
#011 For i = 1 To Times Step 1
#012 Mytest1
#013 Next i
#014
#015 Time2 = Timer
#016
#017 TotalTime = (Time2 - Time1) * 1000
#018 MsgBox "执行时间: " & TotalTime & " 毫秒（次数：" _
#019 & Times & ")"
#020
#021 End Sub
#022
#023 Sub Mytest1()
#024
#025 Dim i As Long
#026 Dim s As String
#027 i = Rnd
#028 s = Format(i, "#.00")
#029
#030 End Sub
过程MeasureTime可以测试一个过程的执行速度，因为一般一个过程执行会很快，所以使 用循环，执行n次（第8行设置），在第12行调用测试的过程，通过循环前的时间（第9行）和 循环后的时间（第15行），计算总共执行时间（第17行）。使用这个方法，就可以做一些测试，看哪些方法执行效率更高。另外，由于Windows的多任务特定，测试时最好关闭其他无关程序，以获得较准确的测试结果。
方法8 注意单元格写法
cells(1,1) >>>>> range("a1")>>>>.[a1]
cells(1,1) 符合 EXCEL 结构，最快
range("a1") 有对象，稍稍慢
[A1] 写的快，运行慢 方法9 不要直呼其名
a= Worksheets(1).Name >>>>> a=Worksheets("Sheet1").name 方法10 少用RANGE对象，可用数组取代，速度快5-10倍，Test2就比Test1快。 Sub Test1()
Dim i As Long, j As Long, buf As Long
For i = 1 To 10000
For j = 1 To 100
buf = Cells(i, j)
Next j
Next i
End Sub Sub Test2()
Dim i As Long, j As Long, buf As Long, C As Variant
C = Range("A1:CV10000")
For i = 1 To 10000
For j = 1 To 100
buf = C(i, j)
Next j
Next i
End Sub 方法11 注意函数的类型, 尽量少用Variant变量，多用整型变量，如多用整型变量函数。 Chr$ ChrB$ Command$
CurDir$ Date$ Dir$
Error$ Format$ Hex$
Input$ InputB$ LCase$
LeftB$ LTrim$
Mid$ MidB$ Oct$
Right$ RightB$ RTrim$
Space$ Str$ String$
Time$ Trim$ UCase$
这些字符型函数 就比chr date space 等快，因为不加后缀类型指定的函数，其返回值是Variant类型结果。
| 
楼主: zhoujibin1
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-7-23 20:25
楼主
