Excel VBA（宏）精簡（五）

昵稱5012511 2010-12-10

展開全文

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

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

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

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

ForEachcInWorksheet(1).Range(〃A1:A1000〃)

TotalValue=TotalValue+c.Value

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:盡量減少使用對象引用,尤其在循環(huán)中

每一個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).使用對象變量.

如果你發(fā)現(xiàn)一個對象引用被多次使用,則你可以將此對象用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).在循環(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:減少對象的激活和選擇

如果你的通過錄制宏來學習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語句來顯式堀明所有的變量.如果使用了未堀明的變量名在編譯時間會出現(xiàn)錯誤.如果沒有使用OptionExplicit語句,一般所有未堀明的變量都是Variant類型的.

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

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

'本示例關閉所有打開的工作簿.如果某個打開的工作簿有改變,MicrosoftExcel將顯示詢問是否保存更改的對話框和相應提示.

Workbooks.Close實際開發(fā)程序時,需要關閉提示信息對話框,給用戶簡潔高效的體驗.

Application.DisplayAlerts=False'信息警告關閉請不要忘記VBA程序運行結束時再將該值設回來:Application.DisplayAlerts=True'信息警告開啟關閉信息警告后,保存文檔及關閉需要先保存,在關閉Workbooks("filename.xls").Save'文件保存.

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

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

不同方法執(zhí)行效率的差異,但千萬不要因為追求效率而損失了代碼的可讀性,清晰性.效率的優(yōu)化必須是針對關鍵代碼的優(yōu)化,對于一些在程序執(zhí)行過程中,只執(zhí)行很少次數的代碼,沒有必要犧牲可讀性而進行優(yōu)化.對于代碼執(zhí)行效率,千萬不要人云亦云,必要時候,自己動手測試一下,結果往往會出乎意料.

代碼執(zhí)行時間的測算VBA和VB中,沒有專門的代碼執(zhí)行事件測算工具和方法,筆者一般是使用Timer函數,其返回值是一個Single類型的數值,代表從午夜開始到現(xiàn)在經過的秒數,此數值包括小數部分,但精確程度在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&"毫秒(次數:"_ &Times&")"

EndSub

SubMytest1()DimiAsLong

DimsAsString

i=Rnd

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

EndSub

過程MeasureTime可以測試一個過程的執(zhí)行速度,因為一般一個過程執(zhí)行會很快,所以使用循環(huán),執(zhí)行n次(第8行設置),在第12行調用測試的過程,通過循環(huán)前的時間(第9行)和循環(huán)后的時間(第15行),計算總共執(zhí)行時間(第17行).使用這個方法,就可以做一些測試,看哪些方法執(zhí)行效率更高.另外,由于Windows的多任務特定,測試時最好關閉其他無關程序,以獲得較準確的測試結果.