分享

Excel VBA(宏)精简(五)

 昵称5012511 2010-12-10

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,ReDimStatic语句来显式堀明所有的变量.如果使用了未堀明的变量名在编译时间会出现错误.如果没有使用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:提高关键代码和循环代码的效率

不同方法执行效率的差异,但千万不要因为追求效率而损失了代码的可读性,清晰性.效率的优化必须是针对关键代码的优化,对于一些在程序执行过程中,只执行很少次数的代码,没有必要牺牲可读性而进行优化.对于代码执行效率,千万不要人云亦云,必要时候,自己动手测试一下,结果往往会出乎意料.

代码执行时间的测算VBAVB,没有专门的代码执行事件测算工具和方法,笔者一般是使用Timer函数,其返回值是一个Single类型的数值,代表从午夜开始到现在经过的秒数,此数值包括小数部分,但精确程度在WindowsNT,2000XP下大概接近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(宏)精简 完结&

 

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多