提高VBA運(yùn)行效率方法

提高VBA運(yùn)行效率方法

速度問題一直是VBA程序值得關(guān)注的一個(gè)方面。當(dāng)您編寫了一個(gè)對(duì)大量數(shù)據(jù)進(jìn)行操作的程序后，在運(yùn)行時(shí)可能會(huì)發(fā)現(xiàn)程序運(yùn)行得很慢，有的甚至達(dá)好幾分鐘，就像Excel應(yīng)用程序已崩潰了一樣。但當(dāng)您發(fā)現(xiàn)程序確實(shí)在運(yùn)行，并得到正確的結(jié)果后，您可能就會(huì)想到如何使程序更快的運(yùn)行了。當(dāng)然，代碼運(yùn)行速度慢可能是VBA程序的一個(gè)缺點(diǎn)，這也可能是程序語言本身的一個(gè)原因，但我們也可以找到一些方法優(yōu)化VBA代碼并使程序盡可能快的運(yùn)行。

下面是我整理的一些優(yōu)化VBA代碼或提高程序運(yùn)行速度的方法，有些方法是我們在編程中好的做法和應(yīng)該養(yǎng)成的好習(xí)慣，希望能帶給您一些有益的參考。高質(zhì)量的程序和運(yùn)行效率是需要不斷實(shí)踐，并在實(shí)踐中不斷總結(jié)和積累經(jīng)驗(yàn)的，也希望您能將在編寫程序過程中發(fā)現(xiàn)的一些優(yōu)化方法介紹給大家共享。

1、盡量簡化代碼

通過簡化代碼，可以提高程序的性能。您可以將通用過程編寫為子過程來調(diào)用。例如，假設(shè)有一個(gè)應(yīng)用程序需要在不同的地方實(shí)現(xiàn)查找一定范圍內(nèi)的某個(gè)特殊條目，在一個(gè)沒有簡化代碼的應(yīng)用程序中，不同的過程可能需要應(yīng)用各自的算法以實(shí)現(xiàn)在某個(gè)范圍內(nèi)查找某一條目，修改每個(gè)過程使其采用一個(gè)更有效的算法并不是一件很容易的事。而一個(gè)簡化的程序則只有一個(gè)查找算法，即將該查找算法編寫成通用的子程序，需要查找某個(gè)范圍的過程都調(diào)用該子程序，通過在查找方法的子程序中優(yōu)化查找算法，使得調(diào)用該方法的所有過程都享受性能提高所帶來的好處。

另外，刪除所有無關(guān)的代碼，這在所錄制宏中表現(xiàn)得尤為明顯。在錄制宏時(shí)，經(jīng)常會(huì)產(chǎn)生一些與所實(shí)現(xiàn)的功能無關(guān)的代碼，您可以將這些代碼刪除，以使得代碼得以簡化。

在下面將要講到的設(shè)置對(duì)象變量代替長對(duì)象引用，使用With…EndWith語句、執(zhí)行ForEach…Next循環(huán)語句，根據(jù)程序環(huán)境盡量減少OLE引用，等等，均是簡化代碼的好方法。

2、強(qiáng)制聲明變量

在VBE編輯器中的菜單“工具——選項(xiàng)”對(duì)話框中“編輯器”選項(xiàng)卡中，您應(yīng)該始終保持“要求變量聲明”復(fù)選框被選中，這樣將在模塊代碼頂部出現(xiàn)OptionExplicit語句，要求您在編寫代碼時(shí)對(duì)所有出現(xiàn)的變量均進(jìn)行聲明，這樣，在使用變量時(shí)減少內(nèi)存需求并加速性能。

(1)要節(jié)省內(nèi)存資源，必須始終用特定的數(shù)據(jù)類型聲明所有變量。如果不使用特定的數(shù)據(jù)類型聲明變量，VBA會(huì)創(chuàng)建Variant類型的變量，這將比任何其他數(shù)據(jù)類型要求更多的內(nèi)存。

(2)清楚每種數(shù)據(jù)類型需要多少內(nèi)存以及它可以存儲(chǔ)的值的范圍。除使用較小的數(shù)據(jù)類型會(huì)導(dǎo)致隱性轉(zhuǎn)換的情況外，應(yīng)始終使用盡可能小的數(shù)據(jù)類型。例如，因?yàn)镮nteger類型的變量將被轉(zhuǎn)換成Long類型的變量，應(yīng)該將那些存儲(chǔ)整型值的變量聲明為Long類型，而不是Integer類型。

(3)除非確實(shí)需要，應(yīng)避免使用浮點(diǎn)數(shù)據(jù)類型。盡管Currency數(shù)據(jù)類型更大，但它比Single數(shù)據(jù)類型快，因?yàn)镃urrency數(shù)據(jù)類型不使用浮點(diǎn)處理器。

(4)如果在一個(gè)過程中多次引用一個(gè)對(duì)象，可以創(chuàng)建對(duì)象變量，并將對(duì)該對(duì)象的引用指派給它。因?yàn)閷?duì)象變量存儲(chǔ)對(duì)象在內(nèi)存中的位置，VBA將不必再次查找其位置。

(5)將對(duì)象變量聲明為特定的類型(不是Object類型)，以便利用早期綁定。

3、減少變量的作用范圍并及時(shí)釋放變量

主要是對(duì)象變量，在其使用完后，及時(shí)釋放。例如，

DimTempObjAsAnyObject,AnObjAsAnyObject

SetTempObj=NewAnyObject

SetAnObj=TempObj

SetTempObj=Nothing‘釋放對(duì)象變量

4、盡可能使用早期綁定

綁定是指將程序調(diào)用與實(shí)際代碼相匹配。為了實(shí)現(xiàn)早期綁定，先應(yīng)創(chuàng)建對(duì)對(duì)象庫的引用。早期綁定可以在代碼中使用定義在對(duì)象庫中的常量，可以自動(dòng)列出對(duì)象的方法和屬性，但早期綁定只有在所控制的對(duì)象擁有獨(dú)立的類型庫或?qū)ο髱煳募胚m用且還需要已安裝了特定的庫。而后期綁定則只是在運(yùn)行時(shí)才知道對(duì)象的類型并對(duì)對(duì)象進(jìn)行引用，因此不具備上述特點(diǎn)。

使用早期綁定創(chuàng)建對(duì)象通常更有效率，使代碼能獲得更好的性能。因?yàn)閷?duì)象的早期綁定引用在編譯時(shí)可以通過VBE的解析，而不是通過運(yùn)行時(shí)模塊解析，因此早期綁定的性能要好得多。雖然在程序設(shè)計(jì)時(shí)不可能總是使用早期綁定，但應(yīng)該盡可能使用它。

5、關(guān)閉屏幕刷新

在Excel中，其ScreenUpdating屬性值的默認(rèn)值為True，這樣當(dāng)寫數(shù)據(jù)到工作表或者執(zhí)行任何導(dǎo)致其顯示屬性變化的動(dòng)作時(shí)，Excel的屏幕界面將會(huì)不斷的刷新，不僅影響顯示，而且影響程序運(yùn)行的速度。您可以在進(jìn)入主程序運(yùn)行前將屏幕刷新屬性關(guān)閉，即用Application.ScreenUpdating=False語句關(guān)閉屏幕刷新，這樣將大大改善程序的運(yùn)行速度。

但在程序運(yùn)行完成前，要確保將其恢復(fù)為原來的設(shè)置，即將ScreenUpdating屬性的值設(shè)置為True。因?yàn)槟鷮?duì)該屬性的修改是永久性的修改，Excel不會(huì)為您自動(dòng)恢復(fù)其默認(rèn)值，您必須用語句Application.ScreenUpdating=True恢復(fù)設(shè)置。

6、設(shè)置計(jì)算模式為手動(dòng)

如果您的工作表中含有多個(gè)公式，在每次單元格中的值發(fā)生變化時(shí)，公式都將會(huì)重新計(jì)算，這會(huì)影響程序運(yùn)行速度。您可以在進(jìn)入主程序運(yùn)行前，將計(jì)算模式設(shè)置為手動(dòng)，即使用如下語句Application.Calculation=xlCalculationManual，以避免不必要的計(jì)算。

當(dāng)程序運(yùn)行結(jié)束前，您要恢復(fù)Excel的默認(rèn)計(jì)算模式設(shè)置，即設(shè)置為自動(dòng)重算，可使用下面的語句Application.Calculation=xlCalculationAutomatic，這同ScreenUpdating屬性一樣，Excel不會(huì)自動(dòng)恢復(fù)其為默認(rèn)值。Calculation屬性是對(duì)所有工作簿進(jìn)行的設(shè)置，您也可以用工作表的EnableCalculation屬性來設(shè)置對(duì)某個(gè)工作表是否進(jìn)行重新計(jì)算。

7、使用ForEach…Next循環(huán)

可以使用ForEach…Next循環(huán)來保證程序代碼更快地執(zhí)行。在使用ForEach…Next循環(huán)時(shí)，對(duì)于存儲(chǔ)在集合或數(shù)組中的每個(gè)對(duì)象執(zhí)行一組語句，程序更簡潔，也更容易閱讀、調(diào)試和維護(hù)。當(dāng)ForEach…Next語句迭代集合時(shí)，自動(dòng)指定一個(gè)對(duì)集合當(dāng)前成員的引用，然后在到達(dá)集合的尾部時(shí)跳出循環(huán)語句。

8、使用With…EndWith語句

可以使用With…EndWith語句來盡量減少對(duì)象引用。使用With語句對(duì)指定的對(duì)象完成一系列的任務(wù)，而不用重復(fù)引用對(duì)象。也可以使用嵌套的With語句進(jìn)一步提高程序代碼的效率。例如，下面的使用With…EndWith語句是在同一個(gè)單元格中執(zhí)行多個(gè)操作。

WithWorkbooks(“Book1.xls”).Worksheets(“Sheet1”).Range(“A1”)

.Formula=”=SQRT(20)”

With.Font

.Name=”Arial”

.Bold=True

.Size=10

EndWith

EndWith

同理，可使用With…EndWith語句在同一個(gè)單元格區(qū)域中執(zhí)行多個(gè)操作。

9、在執(zhí)行循環(huán)時(shí)考慮如何能夠盡可能地節(jié)省資源(1)分析循環(huán)以查看是否正在不必要地執(zhí)行一些消耗內(nèi)存的重復(fù)操作。例如，是否可以在循環(huán)外(而不是在循環(huán)中)設(shè)置某些變量？每次都通過循環(huán)執(zhí)行的轉(zhuǎn)換過程是否可以在循環(huán)之外執(zhí)行？

的。例如，

DimNamesAsVariant

Names=Array(“Fan”,“Yang”,“Wu”,“Shen