ExcelVBA优化及结束语
由于Microsoft Office办公套件的广泛应用,以及该软件版本的不断提升,功能不断完善,在Office办公套件平匀上开发出的VBA应用程序越来越多,而VBA是一种宏语言,在运行速度上有很大的限制.因此VBA编程的方法直接关系到VBA程序运行的效率,本节列举了一些提高VBA程序运行效率的方法.
方法1:尽量使用VBA原有的属性
方法和Worksheet函数由于Excel对象多达百多个,对象的属性,方法,事件多不胜数,对于初学者来说可能对它们不全部了解,这就产生了编程者经常编写与Excel对象的属性,方法相同功能的VBA代码段,而这些代码段的运行效率显然与Excel对象的属性,方法完成任务的速度相差甚大.例如用Range的属性CurrentRegion来返回Range对象,该对象代表当前区.(当前区指以任意空白行及空白列的组合为边界的区域).同样功能的VBA代码需数十行.因此编程前应尽可能多地了解Excel对象的属性,方法.充分利用Worksheet函数是提高程序运行速度的极度有效的方法.如求平均工资的例子:
ForEachcInWorksheet(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〃...则以下语句比上面的快
WithWorkbooks(1).Sheets(1).Range(〃A1:A1000〃).Font
.Name=〃Pay〃
.FontStyle=〃Bold〃
...
EndWith
2).使用对象变量.
如果你发现一个对象引用被多次使用,则你可以将此对象用Set设置为对象变量,以减少对对象
的访问.如:
Workbooks(1).Sheets(1).Range(〃A1〃).Value=100
Workbooks(1).Sheets(1).Range(〃A2〃).Value=200
则以下代码比上面的要快:
SetMySheet=Workbooks(1).Sheets(1)
MySheet.Range(〃A1〃).Value=100
MySheet.Range(〃A2〃).Value=200
3).在循环中要尽量减少对象的访问.Fork=1To1000
Sheets(〃Sheet1〃).Select
Cells(k,1).Value=Cells(1,1).Value
Nextk
则以下代码比上面的要快:
SetTheValue=Cells(1,1).Value
Sheets(〃Sheet1〃).Select
Fork=1To1000
Cells(k,1).Value=TheValue
Nextk
方法3:减少对象的激活和选择
如果你的通过录制宏来学习VBA的,则你的VBA程序里一定充满了对象的激活和选择,例如Workbooks(XXX).Activate,Sheets(XXX).Select,Range(XXX).Select等,但事实上大多数情况下这些操作不是必需的.例如
Sheets(〃Sheet3〃).Select
Range(〃A1〃).Value=100
Range(〃A2〃).Value=200
可改为:
WithSheets(〃Sheet3〃)
.Range(〃A1〃).Value=100
.Range(〃A2〃).Value=200
EndWith
方法4:关闭屏幕更新
如果你的VBA程序前面三条做得比较差,则关闭屏幕更新是提高VBA程序运行速度的最有效的方法,缩短运行时间2/3左右.关闭屏幕更新的方法:
Application.ScreenUpdate=False
请不要忘记VBA程序运行结束时再将该值设回来:
Application.ScreenUpdate=True
方法5:变量类型确定,少用变体变量
OptionExplicit语句,在模块级别中使用,强制显式堀明模块中的所有变量.如果模块中使用了OptionExplicit,则必须使用Dim,Private,Public,ReDim或Static语句来显式堀明所有的变量.如果使用了未堀明的变量名在编译时间会出现错误.如果没有使用OptionExplicit语句,一般所有未堀明的变量都是Variant类型的.
注意使用OptionExplicit可以避免在键入已有变量时出错,在变量的范围不是很清楚的代码中使用该语句可以避免混乱.
方法6:关闭Excel系统提示
'本示例关闭所有打开的工作簿.如果某个打开的工作簿有改变,MicrosoftExcel将显示询问是否保存更改的对话框和相应提示.
Workbooks.Close实际开发程序时,需要关闭提示信息对话框,给用户简洁高效的体验.
Application.DisplayAlerts=False'信息警告关闭请不要忘记VBA程序运行结束时再将该值设回来:Application.DisplayAlerts=True'信息警告开启关闭信息警告后,保存文档及关闭需要先保存,在关闭Workbooks("filename.xls").Save'文件保存.
Workbooks("filename.xls").CloseSaveChanges:=True'文件关闭,不出现是否要保存的窗口,并保存所有对此工作簿的更改.Workbooks("BOOK1.XLS").CloseSaveChanges:=False'本示例关闭Book1.xls,并放弃所有对此工作簿的更改.这样可以提高程序的简洁性,给用户服务.
方法7:提高关键代码和循环代码的效率
不同方法执行效率的差异,但千万不要因为追求效率而损失了代码的可读性,清晰性.效率的优化必须是针对关键代码的优化,对于一些在程序执行过程中,只执行很少次数的代码,没有必要牺牲可读性而进行优化.对于代码执行效率,千万不要人云亦云,必要时候,自己动手测试一下,结果往往会出乎意料.
代码执行时间的测算VBA和VB中,没有专门的代码执行事件测算工具和方法,笔者一般是使用Timer函数,其返回值是一个Single类型的数值,代表从午夜开始到现在经过的秒数,此数值包括小数部分,但精确程度在WindowsNT,2000和XP下大概接近10毫秒.如果要测试一段代码的执行速度,可以使用如下方法:
SubMeasureTime()
DimTime1AsSingle,Time2AsSingle
DimTotalTimeAsSingle
DimTimesAsLong
DimiAsLong
Times=10000
Time1=Timer
Fori=1ToTimesStep1
Mytest1
Nexti
Time2=Timer
TotalTime=(Time2-Time1)*1000
MsgBox"执行时间:"&TotalTime&"毫秒(次数:"_ &Times&")"
EndSub
SubMytest1()DimiAsLong
DimsAsString
i=Rnd
s=Format(i,"#.00")
EndSub
过程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快.
SubTest1()
DimiAsLong,jAsLong,bufAsLong
Fori=1To10000
Forj=1To100
buf=Cells(i,j)
Nextj
Nexti
EndSub
SubTest2()
DimiAsLong,jAsLong,bufAsLong,CAsVariant
C=Range("A1:CV10000")
Fori=1To10000
Forj=1To100
buf=C(i,j)
Nextj
Nexti
EndSub
方法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$
这些字符型函数就比chrdatespace等快,因为不加后缀类型指定的函数,其返回值是Variant类型结果.
&Excel VBA(宏)精简 完结&
联系客服