ExcelVBA優(yōu)化及結(jié)束語
由于Microsoft Office辦公套件的廣泛應(yīng)用,以及該軟件版本的不斷提升,功能不斷完善,在Office辦公套件平勻上開發(fā)出的VBA應(yīng)用程序越來越多,而VBA是一種宏語言,在運行速度上有很大的限制.因此VBA編程的方法直接關(guān)系到VBA程序運行的效率,本節(jié)列舉了一些提高VBA程序運行效率的方法.
方法1:盡量使用VBA原有的屬性
方法和Worksheet函數(shù)由于Excel對象多達(dá)百多個,對象的屬性,方法,事件多不勝數(shù),對于初學(xué)者來說可能對它們不全部了解,這就產(chǎn)生了編程者經(jīng)常編寫與Excel對象的屬性,方法相同功能的VBA代碼段,而這些代碼段的運行效率顯然與Excel對象的屬性,方法完成任務(wù)的速度相差甚大.例如用Range的屬性CurrentRegion來返回Range對象,該對象代表當(dāng)前區(qū).(當(dāng)前區(qū)指以任意空白行及空白列的組合為邊界的區(qū)域).同樣功能的VBA代碼需數(shù)十行.因此編程前應(yīng)盡可能多地了解Excel對象的屬性,方法.充分利用Worksheet函數(shù)是提高程序運行速度的極度有效的方法.如求平均工資的例子:
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〃))
其它函數(shù)如Count,Counta,Countif,Match,Lookup等等,都能代替相同功能的VBA程序代碼,提高程序的運行速度.
方法2:盡量減少使用對象引用,尤其在循環(huán)中
每一個Excel對象的屬性,方法的調(diào)用都需要通過OLE接口的一個或多個調(diào)用,這些OLE調(diào)用都是
需要時間的,減少使用對象引用能加快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).使用對象變量.
如果你發(fā)現(xiàn)一個對象引用被多次使用,則你可以將此對象用Set設(shè)置為對象變量,以減少對對象
的訪問.如:
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).在循環(huán)中要盡量減少對象的訪問.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:減少對象的激活和選擇
如果你的通過錄制宏來學(xué)習(xí)VBA的,則你的VBA程序里一定充滿了對象的激活和選擇,例如Workbooks(XXX).Activate,Sheets(XXX).Select,Range(XXX).Select等,但事實上大多數(shù)情況下這些操作不是必需的.例如
Sheets(〃Sheet3〃).Select
Range(〃A1〃).Value=100
Range(〃A2〃).Value=200
可改為:
WithSheets(〃Sheet3〃)
.Range(〃A1〃).Value=100
.Range(〃A2〃).Value=200
EndWith
方法4:關(guān)閉屏幕更新
如果你的VBA程序前面三條做得比較差,則關(guān)閉屏幕更新是提高VBA程序運行速度的最有效的方法,縮短運行時間2/3左右.關(guān)閉屏幕更新的方法:
Application.ScreenUpdate=False
請不要忘記VBA程序運行結(jié)束時再將該值設(shè)回來:
Application.ScreenUpdate=True
方法5:變量類型確定,少用變體變量
OptionExplicit語句,在模塊級別中使用,強制顯式堀明模塊中的所有變量.如果模塊中使用了OptionExplicit,則必須使用Dim,Private,Public,ReDim或Static語句來顯式堀明所有的變量.如果使用了未堀明的變量名在編譯時間會出現(xiàn)錯誤.如果沒有使用OptionExplicit語句,一般所有未堀明的變量都是Variant類型的.
注意使用OptionExplicit可以避免在鍵入已有變量時出錯,在變量的范圍不是很清楚的代碼中使用該語句可以避免混亂.
方法6:關(guān)閉Excel系統(tǒng)提示
'本示例關(guān)閉所有打開的工作簿.如果某個打開的工作簿有改變,MicrosoftExcel將顯示詢問是否保存更改的對話框和相應(yīng)提示.
Workbooks.Close實際開發(fā)程序時,需要關(guān)閉提示信息對話框,給用戶簡潔高效的體驗.
Application.DisplayAlerts=False'信息警告關(guān)閉請不要忘記VBA程序運行結(jié)束時再將該值設(shè)回來:Application.DisplayAlerts=True'信息警告開啟關(guān)閉信息警告后,保存文檔及關(guān)閉需要先保存,在關(guān)閉Workbooks("filename.xls").Save'文件保存.
Workbooks("filename.xls").CloseSaveChanges:=True'文件關(guān)閉,不出現(xiàn)是否要保存的窗口,并保存所有對此工作簿的更改.Workbooks("BOOK1.XLS").CloseSaveChanges:=False'本示例關(guān)閉Book1.xls,并放棄所有對此工作簿的更改.這樣可以提高程序的簡潔性,給用戶服務(wù).
方法7:提高關(guān)鍵代碼和循環(huán)代碼的效率
不同方法執(zhí)行效率的差異,但千萬不要因為追求效率而損失了代碼的可讀性,清晰性.效率的優(yōu)化必須是針對關(guān)鍵代碼的優(yōu)化,對于一些在程序執(zhí)行過程中,只執(zhí)行很少次數(shù)的代碼,沒有必要犧牲可讀性而進(jìn)行優(yōu)化.對于代碼執(zhí)行效率,千萬不要人云亦云,必要時候,自己動手測試一下,結(jié)果往往會出乎意料.
代碼執(zhí)行時間的測算VBA和VB中,沒有專門的代碼執(zhí)行事件測算工具和方法,筆者一般是使用Timer函數(shù),其返回值是一個Single類型的數(shù)值,代表從午夜開始到現(xiàn)在經(jīng)過的秒數(shù),此數(shù)值包括小數(shù)部分,但精確程度在WindowsNT,2000和XP下大概接近10毫秒.如果要測試一段代碼的執(zhí)行速度,可以使用如下方法:
SubMeasureTime()
DimTime1AsSingle,Time2AsSingle
DimTotalTimeAsSingle
DimTimesAsLong
DimiAsLong
Times=10000
Time1=Timer
Fori=1ToTimesStep1
Mytest1
Nexti
Time2=Timer
TotalTime=(Time2-Time1)*1000
MsgBox"執(zhí)行時間:"&TotalTime&"毫秒(次數(shù):"_ &Times&")"
EndSub
SubMytest1()DimiAsLong
DimsAsString
i=Rnd
s=Format(i,"#.00")
EndSub
過程MeasureTime可以測試一個過程的執(zhí)行速度,因為一般一個過程執(zhí)行會很快,所以使用循環(huán),執(zhí)行n次(第8行設(shè)置),在第12行調(diào)用測試的過程,通過循環(huán)前的時間(第9行)和循環(huán)后的時間(第15行),計算總共執(zhí)行時間(第17行).使用這個方法,就可以做一些測試,看哪些方法執(zhí)行效率更高.另外,由于Windows的多任務(wù)特定,測試時最好關(guān)閉其他無關(guān)程序,以獲得較準(zhǔn)確的測試結(jié)果.
方法8注意單元格寫法
cells(1,1)>>>>>range("a1")>>>>.[a1]cells(1,1)符合EXCEL結(jié)構(gòu),最快range("a1")有對象,稍稍慢[A1]寫的快,運行慢
方法9不要直呼其名
a=Worksheets(1).Name>>>>>a=Worksheets("Sheet1").name
方法10少用RANGE對象
可用數(shù)組取代,速度快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注意函數(shù)的類型
盡量少用Variant變量,多用整型變量,如多用整型變量函數(shù).
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$
這些字符型函數(shù)就比chrdatespace等快,因為不加后綴類型指定的函數(shù),其返回值是Variant類型結(jié)果.
&Excel VBA（宏）精簡 完結(jié)&
聯(lián)系客服
