ExcelVBA優(yōu)化及結(jié)束語(yǔ)

由于Microsoft Office辦公套件的廣泛應(yīng)用,以及該軟件版本的不斷提升,功能不斷完善,在Office辦公套件平勻上開(kāi)發(fā)出的VBA應(yīng)用程序越來(lái)越多,而VBA是一種宏語(yǔ)言,在運(yùn)行速度上有很大的限制.因此VBA編程的方法直接關(guān)系到VBA程序運(yùn)行的效率,本節(jié)列舉了一些提高VBA程序運(yùn)行效率的方法.

方法1:盡量使用VBA原有的屬性

方法和Worksheet函數(shù)由于Excel對(duì)象多達(dá)百多個(gè),對(duì)象的屬性,方法,事件多不勝數(shù),對(duì)于初學(xué)者來(lái)說(shuō)可能對(duì)它們不全部了解,這就產(chǎn)生了編程者經(jīng)常編寫(xiě)與Excel對(duì)象的屬性,方法相同功能的VBA代碼段,而這些代碼段的運(yùn)行效率顯然與Excel對(duì)象的屬性,方法完成任務(wù)的速度相差甚大.例如用Range的屬性CurrentRegion來(lái)返回Range對(duì)象,該對(duì)象代表當(dāng)前區(qū).(當(dāng)前區(qū)指以任意空白行及空白列的組合為邊界的區(qū)域).同樣功能的VBA代碼需數(shù)十行.因此編程前應(yīng)盡可能多地了解Excel對(duì)象的屬性,方法.充分利用Worksheet函數(shù)是提高程序運(yùn)行速度的極度有效的方法.如求平均工資的例子:

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程序代碼,提高程序的運(yùn)行速度.

方法2:盡量減少使用對(duì)象引用,尤其在循環(huán)中

每一個(gè)Excel對(duì)象的屬性,方法的調(diào)用都需要通過(guò)OLE接口的一個(gè)或多個(gè)調(diào)用,這些OLE調(diào)用都是

需要時(shí)間的,減少使用對(duì)象引用能加快VBA代碼的運(yùn)行.例如

1).使用With語(yǔ)句

Workbooks(1).Sheets(1).Range(〃A1:A1000〃).Font.Name=〃Pay〃

Workbooks(1).Sheets(1).Range(〃A1:A1000〃).Font.FontStyle=〃Bold〃...則以下語(yǔ)句比上面的快

WithWorkbooks(1).Sheets(1).Range(〃A1:A1000〃).Font

.Name=〃Pay〃

.FontStyle=〃Bold〃

...

EndWith

2).使用對(duì)象變量.

如果你發(fā)現(xiàn)一個(gè)對(duì)象引用被多次使用,則你可以將此對(duì)象用Set設(shè)置為對(duì)象變量,以減少對(duì)對(duì)象

的訪(fǎng)問(wèn).如:

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)中要盡量減少對(duì)象的訪(fǎng)問(wè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:減少對(duì)象的激活和選擇

如果你的通過(guò)錄制宏來(lái)學(xué)習(xí)VBA的,則你的VBA程序里一定充滿(mǎn)了對(duì)象的激活和選擇,例如Workbooks(XXX).Activate,Sheets(XXX).Select,Range(XXX).Select等,但事實(shí)上大多數(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程序運(yùn)行速度的最有效的方法,縮短運(yùn)行時(shí)間2/3左右.關(guān)閉屏幕更新的方法:

Application.ScreenUpdate=False

請(qǐng)不要忘記VBA程序運(yùn)行結(jié)束時(shí)再將該值設(shè)回來(lái):

Application.ScreenUpdate=True

方法5:變量類(lèi)型確定,少用變體變量

OptionExplicit語(yǔ)句,在模塊級(jí)別中使用,強(qiáng)制顯式堀明模塊中的所有變量.如果模塊中使用了OptionExplicit,則必須使用Dim,Private,Public,ReDim或Static語(yǔ)句來(lái)顯式堀明所有的變量.如果使用了未堀明的變量名在編譯時(shí)間會(huì)出現(xiàn)錯(cuò)誤.如果沒(méi)有使用OptionExplicit語(yǔ)句,一般所有未堀明的變量都是Variant類(lèi)型的.

注意使用OptionExplicit可以避免在鍵入已有變量時(shí)出錯(cuò),在變量的范圍不是很清楚的代碼中使用該語(yǔ)句可以避免混亂.

方法6:關(guān)閉Excel系統(tǒng)提示

'本示例關(guān)閉所有打開(kāi)的工作簿.如果某個(gè)打開(kāi)的工作簿有改變,MicrosoftExcel將顯示詢(xún)問(wèn)是否保存更改的對(duì)話(huà)框和相應(yīng)提示.

Workbooks.Close實(shí)際開(kāi)發(fā)程序時(shí),需要關(guān)閉提示信息對(duì)話(huà)框,給用戶(hù)簡(jiǎn)潔高效的體驗(yàn).

Application.DisplayAlerts=False'信息警告關(guān)閉請(qǐng)不要忘記VBA程序運(yùn)行結(jié)束時(shí)再將該值設(shè)回來(lái):Application.DisplayAlerts=True'信息警告開(kāi)啟關(guān)閉信息警告后,保存文檔及關(guān)閉需要先保存,在關(guān)閉Workbooks("filename.xls").Save'文件保存.

Workbooks("filename.xls").CloseSaveChanges:=True'文件關(guān)閉,不出現(xiàn)是否要保存的窗口,并保存所有對(duì)此工作簿的更改.Workbooks("BOOK1.XLS").CloseSaveChanges:=False'本示例關(guān)閉Book1.xls,并放棄所有對(duì)此工作簿的更改.這樣可以提高程序的簡(jiǎn)潔性,給用戶(hù)服務(wù).

方法7:提高關(guān)鍵代碼和循環(huán)代碼的效率

不同方法執(zhí)行效率的差異,但千萬(wàn)不要因?yàn)樽非笮识鴵p失了代碼的可讀性,清晰性.效率的優(yōu)化必須是針對(duì)關(guān)鍵代碼的優(yōu)化,對(duì)于一些在程序執(zhí)行過(guò)程中,只執(zhí)行很少次數(shù)的代碼,沒(méi)有必要犧牲可讀性而進(jìn)行優(yōu)化.對(duì)于代碼執(zhí)行效率,千萬(wàn)不要人云亦云,必要時(shí)候,自己動(dòng)手測(cè)試一下,結(jié)果往往會(huì)出乎意料.

代碼執(zhí)行時(shí)間的測(cè)算VBA和VB中,沒(méi)有專(zhuān)門(mén)的代碼執(zhí)行事件測(cè)算工具和方法,筆者一般是使用Timer函數(shù),其返回值是一個(gè)Single類(lèi)型的數(shù)值,代表從午夜開(kāi)始到現(xiàn)在經(jīng)過(guò)的秒數(shù),此數(shù)值包括小數(shù)部分,但精確程度在WindowsNT,2000和XP下大概接近10毫秒.如果要測(cè)試一段代碼的執(zhí)行速度,可以使用如下方法:

SubMeasureTime()

DimTime1AsSingle,Time2AsSingle

DimTotalTimeAsSingle

DimTimesAsLong

DimiAsLong

Times=10000

Time1=Timer

Fori=1ToTimesStep1

Mytest1

Nexti

Time2=Timer

TotalTime=(Time2-Time1)*1000

MsgBox"執(zhí)行時(shí)間:"&TotalTime&"毫秒(次數(shù):"_ &Times&")"

EndSub

SubMytest1()DimiAsLong

DimsAsString

i=Rnd

s=Format(i,"#.00")

EndSub

過(guò)程MeasureTime可以測(cè)試一個(gè)過(guò)程的執(zhí)行速度,因?yàn)橐话阋粋€(gè)過(guò)程執(zhí)行會(huì)很快,所以使用循環(huán),執(zhí)行n次(第8行設(shè)置),在第12行調(diào)用測(cè)試的過(guò)程,通過(guò)循環(huán)前的時(shí)間(第9行)和循環(huán)后的時(shí)間(第15行),計(jì)算總共執(zhí)行時(shí)間(第17行).使用這個(gè)方法,就可以做一些測(cè)試,看哪些方法執(zhí)行效率更高.另外,由于Windows的多任務(wù)特定,測(cè)試時(shí)最好關(guān)閉其他無(wú)關(guān)程序,以獲得較準(zhǔn)確的測(cè)試結(jié)果.

方法8注意單元格寫(xiě)法

cells(1,1)>>>>>range("a1")>>>>.[a1]cells(1,1)符合EXCEL結(jié)構(gòu),最快range("a1")有對(duì)象,稍稍慢[A1]寫(xiě)的快,運(yùn)行慢

方法9不要直呼其名

a=Worksheets(1).Name>>>>>a=Worksheets("Sheet1").name

方法10少用RANGE對(duì)象

可用數(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ù)的類(lèi)型

盡量少用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等快,因?yàn)椴患雍缶Y類(lèi)型指定的函數(shù),其返回值是Variant類(lèi)型結(jié)果.

&Excel VBA（宏）精簡(jiǎn) 完結(jié)&