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(宏)精简 完结&
|
|