提高VBA運行效率方法

1、v1.0可編輯可修改提高 VBA運行效率方法速度問題一直是VBA程序值得關注的一個方面。當您編寫了一個對大量數(shù)據(jù)進行操作的程序后，在運行時可能會發(fā)現(xiàn)程序運行得很慢，有的甚至達好幾分鐘，就像Excel 應用程序已崩潰了一樣。但當您發(fā)現(xiàn)程序確實在運行，并得到正確的結(jié)果后，您可能就會想到如何使程序更快的運行了。當然，代碼運行速度慢可能是VBA程序的一個缺點，這也可能是程序語言本身的一個原因，但我們也可以找到一些方法優(yōu)化 VBA代碼并使程序盡可能快的運行。下面是我整理的一些優(yōu)化VBA代碼或提高程序運行速度的方法，有些方法是我們在編程中好的做法和應該養(yǎng)成的好習慣，希望能帶給您一些有益的參考。高質(zhì)量的程序

2、和運行效率是需要不斷實踐，并在實踐中不斷總結(jié)和積累經(jīng)驗的，也希望您能將在編寫程序過程中發(fā)現(xiàn)的一些優(yōu)化方法介紹給大家共享。1、盡量簡化代碼通過簡化代碼，可以提高程序的性能。您可以將通用過程編寫為子過程來調(diào)用。例如，假設有一個應用程序需要在不同的地方實現(xiàn)查找一定范圍內(nèi)的某個特殊條目，在一個沒有簡化代碼的應用程序中，不同的過程可能需要應用各自的算法以實現(xiàn)在某個范圍內(nèi)查找某一條目，修改每個過程使其采用一個更有效的算法并不是一件很容易的事。而一個簡化的程序則只有一個查找算法，即將該查找算法編寫成通用的子程序，需要查找某個范圍的過程都調(diào)用該子程序，通過在查找方法的子程序中優(yōu)化查找算法，使得調(diào)用該方法的所有

3、過程都享受性能提高所帶來的好處。另外，刪除所有無關的代碼，這在所錄制宏中表現(xiàn)得尤為明顯。在錄制宏時，經(jīng)常會產(chǎn)生一些與所實現(xiàn)的功能無關的代碼，您可以將這些代碼刪除，以使得代碼得以簡化。在下面將要講到的設置對象變量代替長對象引用，使用WithEnd With語句、執(zhí)行For EachNext循環(huán)語句，根據(jù)程序環(huán)境盡量減少OLE引用，等等，均是簡化代碼的好方法。2、強制聲明變量在 VBE編輯器中的菜單“工具選項”對話框中“編輯器”選項卡中，您應該始終保持“要求變量聲明”復選框被選中，這樣將在模塊代碼頂部出現(xiàn)Option Explicit語句，要求您在編寫代碼時對所有出現(xiàn)的變量均進行聲明，這樣，在使用

4、變量時減少內(nèi)存需求并加速性能。1v1.0可編輯可修改(1) 要節(jié)省內(nèi)存資源，必須始終用特定的數(shù)據(jù)類型聲明所有變量。如果不使用特定的數(shù)據(jù)類型聲明變量，VBA會創(chuàng)建 Variant類型的變量，這將比任何其他數(shù)據(jù)類型要求更多的內(nèi)存。(2) 清楚每種數(shù)據(jù)類型需要多少內(nèi)存以及它可以存儲的值的范圍。除使用較小的數(shù)據(jù)類型會導致隱性轉(zhuǎn)換的情況外，應始終使用盡可能小的數(shù)據(jù)類型。例如，因為Integer類型的變量將被轉(zhuǎn)換成Long 類型的變量，應該將那些存儲整型值的變量聲明為Long 類型，而不是Integer類型。(3) 除非確實需要，應避免使用浮點數(shù)據(jù)類型。盡管Currency 數(shù)據(jù)類型更大，但它比Singl

5、e數(shù)據(jù)類型快，因為 Currency 數(shù)據(jù)類型不使用浮點處理器。(4) 如果在一個過程中多次引用一個對象，可以創(chuàng)建對象變量，并將對該對象的引用指派給它。因為對象變量存儲對象在內(nèi)存中的位置， VBA將不必再次查找其位置。(5) 將對象變量聲明為特定的類型 ( 不是 Object 類型 ) ，以便利用早期綁定。3、減少變量的作用范圍并及時釋放變量主要是對象變量，在其使用完后，及時釋放。例如，Dim TempObj As AnyObject,AnObj As AnyObjectSet TempObj=New AnyObjectSet AnObj=TempObjSet TempObj=Nothing釋

6、放對象變量4、盡可能使用早期綁定綁定是指將程序調(diào)用與實際代碼相匹配。為了實現(xiàn)早期綁定，先應創(chuàng)建對對象庫的引用。早期綁定可以在代碼中使用定義在對象庫中的常量，可以自動列出對象的方法和屬性，但早期綁定只有在所控制的對象擁有獨立的類型庫或?qū)ο髱煳募胚m用且還需要已安裝了特定的庫。而后期綁定則只是在運行時才知道對象的類型并對對象進行引用，因此不具備上述特點。使用早期綁定創(chuàng)建對象通常更有效率，使代碼能獲得更好的性能。因為對象的早期綁定引用在編譯時可以通過 VBE的解析，而不是通過運行時模塊解析，因此早期綁定的性能要好得多。雖然在程序設計時不可能總是使用早期綁定，但應該盡可能使用它。5、關閉屏幕刷新在 E

7、xcel 中，其 ScreenUpdating 屬性值的默認值為 True ，這樣當寫數(shù)據(jù)到工作表或者執(zhí)行任何導致其顯示屬性變化的動作時， Excel 的屏幕界面將會不斷的刷新，不僅影響顯示，而且影響程序運行的速度。您2v1.0可編輯可修改可以在進入主程序運行前將屏幕刷新屬性關閉，即用= False語句關閉屏幕刷新，這樣將大大改善程序的運行速度。但在程序運行完成前，要確保將其恢復為原來的設置，即將ScreenUpdating屬性的值設置為True 。因為您對該屬性的修改是永久性的修改，Excel 不會為您自動恢復其默認值，您必須用語句= True 恢復設置。6、設置計算模式為手動如果您的工作表

8、中含有多個公式，在每次單元格中的值發(fā)生變化時，公式都將會重新計算，這會影響程序運行速度。您可以在進入主程序運行前，將計算模式設置為手動，即使用如下語句=xlCalculationManual，以避免不必要的計算。當程序運行結(jié)束前，您要恢復Excel 的默認計算模式設置，即設置為自動重算，可使用下面的語句=xlCalculationAutomatic，這同 ScreenUpdating屬性一樣， Excel 不會自動恢復其為默認值。Calculation屬性是對所有工作簿進行的設置，您也可以用工作表的EnableCalculation屬性來設置對某個工作表是否進行重新計算。7、使用 For Ea

9、chNext循環(huán)可以使用 For EachNext循環(huán)來保證程序代碼更快地執(zhí)行。在使用For EachNext循環(huán)時，對于存儲在集合或數(shù)組中的每個對象執(zhí)行一組語句，程序更簡潔，也更容易閱讀、調(diào)試和維護。當For EachNext 語句迭代集合時，自動指定一個對集合當前成員的引用，然后在到達集合的尾部時跳出循環(huán)語句。8、使用 WithEnd With語句可以使用 WithEnd With語句來盡量減少對象引用。使用With 語句對指定的對象完成一系列的任務，而不用重復引用對象。也可以使用嵌套的With 語句進一步提高程序代碼的效率。例如，下面的使用WithEnd With語句是在同一個單元格中執(zhí)

10、行多個操作。With Workbooks( “”).Worksheets( “Sheet1”).Range( “A1”).Formula= ”=SQRT(20)”With .Font.Name=”Arial ”.Bold=True.Size=10End With3v1.0可編輯可修改End With同理，可使用WithEnd With語句在同一個單元格區(qū)域中執(zhí)行多個操作。9、在執(zhí)行循環(huán)時考慮如何能夠盡可能地節(jié)省資源(1) 分析循環(huán)以查看是否正在不必要地執(zhí)行一些消耗內(nèi)存的重復操作。例如，是否可以在循環(huán)外( 而不是在循環(huán)中 ) 設置某些變量每次都通過循環(huán)執(zhí)行的轉(zhuǎn)換過程是否可以在循環(huán)之外執(zhí)行(2)

11、考慮是否必須在滿足特定的條件時才執(zhí)行循環(huán)。如果是，也許可以更早地退出循環(huán)。例如，假設正在對一個不應該包含數(shù)字字符的字符串進行數(shù)據(jù)驗證。如果循環(huán)要檢查字符串中的每個字符以確定其中是否包含數(shù)字字符，那么您可以在找到第一個數(shù)字字符時立即退出循環(huán)。(3) 如果必須在循環(huán)中引用數(shù)組的元素，可以創(chuàng)建一個臨時變量存儲該元素的值，而不是引用數(shù)組中的值。從數(shù)組中檢索值比從相同類型的變量讀取值要慢。10、盡量減少OLE引用可以通過盡量減少在VBA程序代碼中使用OLE(對象鏈接與嵌入自動識別) 引用來優(yōu)化程序代碼。VBA語句中所調(diào)用的方法和屬性越多，執(zhí)行語句所用的時間就越多。例如下面的兩個語句：語句 1：Workb

12、ooks(1).Sheets(1).Range(“A1”).value=/10語句 2：=200執(zhí)行時，語句2 比語句 1 快。同樣，上面所講的對重復使用的對象引用指定一個變量，通過調(diào)用變量從而保證避免多次進行對象引用。11、避免對象激活或者不需要先進行先擇在使用宏錄制器時，所生成的程序代碼在應用任何方法或?qū)傩灾岸紩せ罨蛘哌x擇對象。但是，并不是在所有的情況下都需要這樣做。所以，在您編寫VBA程序代碼時，不需要在對對象執(zhí)行任何任務之前都激活或者選擇每個對象。例如，在 Excel 中，我們?nèi)绻沟谝恍凶兂纱煮w就必須先選項中它。但在VBA中 ( 除在圖表操作時需要選中圖表對象外 ) ，很少需要

13、這樣做，即VBA可以在不選中第一行的情況下，將它變成粗體。宏錄制器的代碼：Rows(1:1).Select= True改編后的代碼為：4v1.0可編輯可修改Row(“1:1 ”).=True這樣做還可以使程序代碼更簡潔，并且程序可以運行得更快。12、在一個語句中進行復制或者粘貼在用宏錄制代碼時，首先是選擇一個區(qū)域，然后再執(zhí)行。在使用Copy 方法時，可以在一個語句中指定復制的內(nèi)容及要復制到的目的地。例如，將 B5:C6 區(qū)域的內(nèi)容復制到以單元格B8 開始的區(qū)域中，使用宏錄制器的代碼為：Range(B5:C6).SelectRange(B8).Select經(jīng)修改后的最佳代碼是：Range(B5:

14、C6).Copy Destination:=Range(B8)13、盡可能少使用“ . ”在前面已經(jīng)介紹過的對長對象引用使用對象變量以及使用WithEnd With等都是簡化” . ”的方法。因為在代碼中的每個句點都表示至少一個( 而且可能是多個) 過程調(diào)用，而這些過程調(diào)用必須在后臺執(zhí)行。真正好的做法是在局部進行緩存對象引用，例如，應該把對象模型中較高層次的對象引用保存到局部對象變量中，然后用這些對象引用創(chuàng)建其他較低層次的對象引用。例如，引用某單元格數(shù)據(jù)時，可用如下代碼：Dim i As LongFor i=1 to 10Workbooks(“”).Worksheets( “Sheet1”).

15、Cells(1,i).Value=iNext i但下面的代碼運行效率更高，因為代碼中引用Workbook 對象和 Worksheet 對象的調(diào)用命令只執(zhí)行一次，而上面的代碼中卻要執(zhí)行10 次。Dim ws As WorksheetDim i As LongSet ws= Work books( “”).Worksheets( “Sheet1”)For i=1 to 105v1.0可編輯可修改(1,i).Value=iNext i14、合理地使用消息框和窗體在一個很長的程序中，償試著將消息框或者窗體安排顯示在程序的最開始或最后面，避免干擾用戶。此外，盡管窗體提供了許多功能，但它們能夠?qū)е挛募笮?/p>

16、迅速增加。還有就是盡量避免給工作表單元格鏈接用戶窗體控件，因為這樣將會導致鏈接更新操作，影響程序運行速度。15、盡可能加速對數(shù)字的運算 (1)當對整數(shù)進行除法時，您可以使用整型除法運算符()而不是浮點除法運算符 (/) ，因為無論參與除法運算的數(shù)值類型如何，浮點除法運算符總會返回Double類型的值。 (2) 在任何具有整數(shù)值的算術表達式中使用Single 或 Double 值時，整數(shù)均將被轉(zhuǎn)換成Single或 Double 值，最后的結(jié)果將是Single或 Double 值。如果要對作為算術運算結(jié)果的數(shù)字執(zhí)行多次操作，可能需要明確地將該數(shù)字轉(zhuǎn)換為較小的數(shù)據(jù)類型。16、提高字符串操作的性能(1

17、) 盡可能少使用連接操作。可以在等號左邊使用Mid 函數(shù)替換字符串中的字符，而不是將它們連接在一起。使用 Mid函數(shù)的缺點是替換字符串必須與要替換的子字符串的長度相同。例如，Dim strText As StringstrText = this is a testMid(strText, 11, 4) = tent(2)VBA 提供許多可用來替換函數(shù)調(diào)用的內(nèi)部字符串常量。例如，可以使用vbCrLf 常量來表示字符串中的回車 / 換行組合，而不是使用Chr(13) & Chr(10)。(3) 字符串比較操作的執(zhí)行速度很慢。有時，可以通過將字符串中的字符轉(zhuǎn)換為ANSI 值來避免這些操作。例如，下列

18、代碼會檢查字符串中的第一個字符是否為空格：If Asc(strText) = 32 Then上面的代碼會比以下代碼更快：If Left(strText, 1) = Then17、使用 Asc() 檢驗 ANSI 的值在 VBA中，可以使用Chr$() 函數(shù)把數(shù)轉(zhuǎn)換成字符，并確定ANSI的值，但是更好的是使用Asc() 函數(shù)把字符串轉(zhuǎn)換成數(shù)值，然后確定它的ANSI 值。如果需要進行有限次數(shù)的這種檢驗，對程序代碼的效率可能不會6v1.0可編輯可修改產(chǎn)生很大影響，但是，如果需要在多個循環(huán)內(nèi)進行這種檢驗時，這將節(jié)省處理時間并且有助于程序代碼更快地執(zhí)行。18、使用 Len() 檢驗空串盡管有多種方法可檢驗空串，但首選的是使用Len() 函數(shù)。為了測試零長度的串，可以選擇把串與”相比較，或者比較串的長度是否為0，但這些方法比用Len() 函數(shù)要用更多的執(zhí)行時間。當對字符串應用Len() 函數(shù)并且函數(shù)返回0 值時，說明該字符串是空的或者是零長度的字符串。并且，因為在If語句內(nèi)非零值被認為是True ，所以直接使用Len() 函數(shù)而不必與”或0 比較，減少了處理時間，因此執(zhí)行更快。19、有效地使用數(shù)組用 VBA數(shù)組而不是單元格區(qū)域來處理數(shù)據(jù)，即可以先將數(shù)據(jù)寫入到某個數(shù)組，然后用一個語句就可以將