編寫(xiě)高效Excel VBA代碼的最佳實(shí)踐

...v.很多ExcelVBA文章和圖書(shū)都介紹過(guò)如何優(yōu)化VBA代碼，使代碼運(yùn)行得更快。下面搜集了一些使ExcelVBA代碼運(yùn)行更快的技術(shù)和技巧，根本上都是實(shí)踐經(jīng)歷的總結(jié)。如果您還有其它優(yōu)化ExcelVBA代碼的方法，可以在本文后留言或給出，與大家分享。

對(duì)于應(yīng)用程序?qū)傩?，在代碼運(yùn)行時(shí)關(guān)閉除必需屬性以外的其它所有屬性在代碼運(yùn)行時(shí)關(guān)閉不需要的Excel功能。其原因是，如果通過(guò)VBA更新不同的單元格區(qū)域，或者從不同的單元格區(qū)域復(fù)制/粘貼來(lái)創(chuàng)立匯總表，那么不希望Excel浪費(fèi)時(shí)間和資源來(lái)重新計(jì)算公式、顯示粘貼進(jìn)度或者重繪網(wǎng)格，尤其在每次單獨(dú)的操作后〔更有甚者，如果代碼使用了循環(huán)，那么每次單獨(dú)操作后Excel都會(huì)在后臺(tái)運(yùn)行這些功能〕。只需要在代碼執(zhí)行完畢時(shí)進(jìn)展一次重新計(jì)算和重繪就足以使工作簿更新。

下面的代碼將幫助您提高代碼的執(zhí)行速度。

〔1〕放置在主代碼前的一段代碼，獲取Excel當(dāng)前的屬性狀態(tài)，然后將其關(guān)閉'獲得當(dāng)前的Excel設(shè)置狀態(tài),將其放置在代碼的開(kāi)頭screenUpdateState=Application.ScreenUpdatingstatusBarState=Application.DisplayStatusBarcalcState=Application.CalculationeventsState=Application.EnableEventsdisplayPageBreakState=ActiveSheet.DisplayPageBreaks'注:這是工作表級(jí)的設(shè)置'關(guān)閉一些Excel功能使代碼運(yùn)行更快Application.ScreenUpdating=FalseApplication.DisplayStatusBar=FalseApplication.Calculation=xlCalculationManualApplication.EnableEvents=FalseActiveSheet.DisplayPageBreaks=False'注:這是工作表級(jí)的設(shè)置〔2〕放置在主代碼完畢后的一段代碼，用來(lái)將Excel恢復(fù)到代碼運(yùn)行前的設(shè)置'代碼運(yùn)行后,恢復(fù)Excel原來(lái)的狀態(tài);將下面的代碼放在代碼的末尾Application.ScreenUpdating=screenUpdateStateApplication.DisplayStatusBar=statusBarStateApplication.Calculation=calcStateApplication.EnableEvents=eventsStateActiveSheet.DisplayPageBreaks=displayPageBreaksState'注:這是工作表級(jí)的設(shè)置下面簡(jiǎn)要解釋這些設(shè)置：

Application.ScreenUpdating：將該屬性設(shè)置為False，告訴Excel不要重繪屏幕。其優(yōu)點(diǎn)是不需要Excel花費(fèi)資源來(lái)繪制屏幕，因而其改變會(huì)更快而不致讓用戶(hù)發(fā)覺(jué)其變化。因?yàn)槿绱祟l繁地繪制屏幕需要大量的資源，所以關(guān)閉繪制屏幕直到代碼執(zhí)行完畢。在代碼完畢前，確保重新開(kāi)啟了該屬性。

Application.DisplayStatusBar：將該屬性設(shè)置為False，告訴Excel停頓顯示狀態(tài)欄。例如，如果使用VBA復(fù)制/粘貼單元格，當(dāng)粘貼執(zhí)行時(shí)Excel將在狀態(tài)欄中顯示操作的進(jìn)度。關(guān)閉屏幕更新不會(huì)關(guān)閉狀態(tài)欄顯示，因此，如果需要的話(huà)，可以禁用屏幕更新而仍然可以通過(guò)狀態(tài)欄給用戶(hù)提供反應(yīng)。記住，如果將該屬性設(shè)置為False，在代碼完畢前應(yīng)該將其設(shè)置為T(mén)rue。

Application.Calculation：該屬性允許編程設(shè)置Excel的計(jì)算模式。“手工的〞〔xlCalculationManual〕模式意味著Excel等待用戶(hù)〔或代碼〕來(lái)觸發(fā)計(jì)算；默認(rèn)為“自動(dòng)的〞〔xlCalculationAutomatic〕模式，意味著由Excel來(lái)決定何時(shí)重新計(jì)算工作簿〔例如，當(dāng)在工作表中輸入新公式時(shí)〕。由于重新計(jì)算工作簿將花費(fèi)時(shí)間且浪費(fèi)資源，因此可能不希望每次改變單元格值時(shí)Excel都觸發(fā)重新計(jì)算。當(dāng)代碼執(zhí)行時(shí)關(guān)閉重新計(jì)算，在代碼完畢前再設(shè)置回重新計(jì)算模式。

Application.EnableEvents：將該屬性設(shè)置為False，告訴Excel不要觸發(fā)事件。你可能不希望Excel為每個(gè)正在通過(guò)代碼發(fā)生改變的單元格觸發(fā)事件，關(guān)閉事件將加速VBA代碼的執(zhí)行。

ActiveSheet.DisplayPageBreaks：當(dāng)在較新版本的Excel中運(yùn)行VBA時(shí)，那么可能比在早期版本的Excel中需要更長(zhǎng)的時(shí)間完成。例如，需要幾秒鐘在早期版本的Excel中完成的宏可能需要幾分鐘才能在更高版本的Excel中完成?；蛘撸诙芜\(yùn)行一個(gè)宏可能比第一次運(yùn)行需要的時(shí)間更長(zhǎng)。這是由于VBA宏修改了多行或列的屬性，或者必須強(qiáng)制執(zhí)行計(jì)算Excel分頁(yè)符。如果宏設(shè)置了任何PageSetup屬性或者手動(dòng)設(shè)置了PageSetup屬性，接著運(yùn)行較大區(qū)域的行或列屬性設(shè)置時(shí)會(huì)出現(xiàn)這樣的問(wèn)題。您可以將該屬性設(shè)置為False來(lái)提高代碼的運(yùn)行速度。當(dāng)然，在代碼運(yùn)行完畢前，應(yīng)將該屬性恢復(fù)為原設(shè)置。在單個(gè)操作中讀/寫(xiě)大塊的單元格區(qū)域

本技巧用于優(yōu)化在Excel和代碼之間轉(zhuǎn)換數(shù)據(jù)的次數(shù)。使用數(shù)組變量存儲(chǔ)所需要的值并執(zhí)行取值或賦值操作，而不是一次遍歷單個(gè)單元格并獲取或設(shè)置單個(gè)值。

例如，下面的代碼在單元格區(qū)域A1：C10000中放置隨機(jī)數(shù)。

代碼段一：運(yùn)行速度較慢的代碼SubtestSlow()DimDataRangeAsRangeDimIrowAsLongDimIcolAsIntegerDimMyVarAsDoubleSetDataRange=Range("A1:C10000")

ForIrow=1To10000ForIcol=1To3MyVar=DataRange(Irow,Icol)'從Excel單元格中讀取值30K次IfMyVar>0ThenMyVar=MyVar*MyVar'改變值DataRange(Irow,Icol)=MyVar'將值寫(xiě)入Excel單元格中30000次EndIfNextIcolNextIrowEndSub代碼段二：運(yùn)行速度更快的代碼SubtestFast()DimDataRangeAsVariantDimIrowAsLongDimIcolAsIntegerDimMyVarAsDoubleDataRange=Range("A1:C10000").Value'一次從Excel單元格中讀取所有的值,將其放入數(shù)組ForIrow=1To10000ForIcol=1To3MyVar=DataRange(Irow,Icol)IfMyVar>0ThenMyVar=MyVar*MyVar'改變數(shù)組中的值DataRange(Irow,Icol)=MyVarEndIfNextIcolNextIrowRange("A1:C10000").Value=DataRange'一次將所有結(jié)果寫(xiě)回單元格EndSub防止選取/激活對(duì)象使用選取的方法更新單元格區(qū)域是最慢的。在試驗(yàn)了使用Range對(duì)象、使用Variant類(lèi)型和使用Select方法對(duì)一個(gè)大的單元格區(qū)域讀寫(xiě)數(shù)據(jù)的操作后，Select方法是最慢的。

再來(lái)看一個(gè)例子：在工作表中有40個(gè)形狀，在每個(gè)形狀中寫(xiě)入“Hello〞。使用Select方法的代碼為：SubtestSlow()DimiAsIntegerFori=0ToActiveSheet.Shapes(i).SelectSelection.Text="Hello"NextiEndSub運(yùn)行速度更快的方法是完全防止使用選取并直接引用形狀：SubtestFast()DimiAsIntegerFori=0ToActiveSheet.Shapes(i).TextEffect.Text="Hello"NextiEndSub在使用宏錄制器時(shí)，所生成的程序代碼在應(yīng)用任何方法或?qū)傩灾岸紩?huì)激活或者選擇對(duì)象。但是，并不是在所有的情況下都需要這樣做。所以，在您編寫(xiě)VBA程序代碼時(shí)，不需要在對(duì)對(duì)象執(zhí)行任何任務(wù)之前都激活或者選擇每個(gè)對(duì)象。

例如，在Excel中，我們?nèi)绻沟谝恍凶兂纱煮w就必須先選項(xiàng)中它。但在VBA中(除在圖表操作時(shí)需要選中圖表對(duì)象外)，很少需要這樣做，即VBA可以在不選中第一行的情況下，將它變成粗體。

宏錄制器的代碼：Rows("1:1").SelectSelection.Font.Bold=True改編后的代碼為：Row(“1:1〞).Font.Bold=True這樣做還可以使程序代碼更簡(jiǎn)潔，并且程序可以運(yùn)行得更快。工作簿設(shè)計(jì)好的工作簿設(shè)計(jì)和數(shù)據(jù)組織有助于編寫(xiě)運(yùn)行良好的代碼。良好設(shè)計(jì)的工作簿，其執(zhí)行效率和維護(hù)量將大大優(yōu)化。可以說(shuō)，工作簿設(shè)計(jì)是從大的宏觀方面進(jìn)展優(yōu)化，而對(duì)代碼的優(yōu)化只是一些微觀的細(xì)節(jié)上的優(yōu)化。其他盡量簡(jiǎn)化代碼

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

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

宏錄制器生成無(wú)效代碼的一個(gè)原因是它不知道在對(duì)話(huà)框中您選擇了哪些選項(xiàng)，因此，當(dāng)您關(guān)閉對(duì)話(huà)框時(shí)它將直接記錄所有可用的選項(xiàng)。例如，選擇單元格區(qū)域G2:G20，然后在單元格格式對(duì)話(huà)框中改變字體樣式為粗體，使用宏錄制器生成的代碼如下：

SubNowThis1()DimStartAsDouble,FinishAsDoubleStart=Timer''為了進(jìn)展測(cè)試,將循環(huán)100次DimNAsLongForN=1To100'***************************Range("G2:G20").SelectWithSelection.Font.Name="Arial".FontStyle="Bold".Size=10.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlNone.ColorIndex=xlAutomaticEndWith'***************************Next'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub您能只用下面的一行代碼為指定的單元格設(shè)置字體樣式，不需要選擇單元格區(qū)域。Range("G2:G20").Font.FontStyle="Bold"如果您考慮到您想要宏所做的事情(本例中為使字體加粗)，那么您可以查閱應(yīng)用到Font對(duì)象的屬性和方法列表，您將知道只需使用Bold屬性編寫(xiě)這個(gè)宏代碼以實(shí)現(xiàn)所需的功能。代碼如下：SubNowThis2()'快約10倍DimStartAsDouble,FinishAsDoubleStart=Timer''為進(jìn)展測(cè)試,將循環(huán)100次DimNAsLongForN=1To100'***************************Range("G2:G20").Font.Bold=True'***************************Next'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間為"&Finish-StartEndSub您也能在用戶(hù)界面中通過(guò)執(zhí)行不同的方法來(lái)錄制產(chǎn)生結(jié)果一樣的操作對(duì)宏錄制器進(jìn)展試驗(yàn)。例如，如果您通過(guò)標(biāo)準(zhǔn)工具欄上的粗體按鈕格式化某區(qū)域?yàn)榇煮w，那么宏錄制器將使用Bold屬性。

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

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

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

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

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

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

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

(6)減少〞Variant〞類(lèi)型變量的使用

雖然您可能發(fā)現(xiàn)在您的代碼中使用Variant(變體)變量是方便的，但是如果您將變量清楚地聲明為特定的數(shù)據(jù)類(lèi)型，然后用VBA處理存儲(chǔ)在該變量中的值，要比處理存儲(chǔ)在Variant變量里的值快。

如果執(zhí)行不涉及分?jǐn)?shù)值的數(shù)學(xué)運(yùn)算，那么在您的代碼中使用Long型變量比使用Variant變量更快。Long型變量也是在For…Next循環(huán)中索引值變量類(lèi)型的最好選擇。

然而，您要注意到，您使用特定類(lèi)型變量所獲取的速度是以失去靈活性為代價(jià)的。例如，當(dāng)使用特定數(shù)據(jù)類(lèi)型時(shí)，您可能遭到變量溢出或類(lèi)型不匹配的情形，而不會(huì)像Variant變量會(huì)自動(dòng)進(jìn)展類(lèi)型轉(zhuǎn)換處理。

(7)聲明時(shí)指定特定的對(duì)象類(lèi)型

當(dāng)您的宏被編譯或者是運(yùn)行(后臺(tái)編譯)時(shí)，會(huì)解析對(duì)象及它們的方法和屬性的引用。經(jīng)過(guò)宏編譯解析的引用比在程序運(yùn)行時(shí)必須被解析的引用要更快，因此，您最好跳過(guò)后臺(tái)編譯。

如果您聲明變量和參數(shù)為特定的對(duì)象類(lèi)型(比方Range或Worksheet)，VBA在編譯您的程序時(shí)將解析引用為這些對(duì)象的屬性和方法。(如果要查找指定對(duì)象類(lèi)型列表，請(qǐng)參見(jiàn)〞對(duì)象瀏覽器〞)減少變量的作用范圍并及時(shí)釋放變量

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

DimTempObjAsAnyObject,AnObjAsAnyObjectSetTempObj=NewAnyObjectSetAnObj=TempObjSetTempObj=Nothing‘釋放對(duì)象變量使用常量

變量會(huì)發(fā)生變化，因此VBA在程序運(yùn)行時(shí)必須獲取當(dāng)前變量的值。

在應(yīng)用程序中使用常量會(huì)使程序運(yùn)行更快。在編譯您的代碼時(shí)，常量?jī)H計(jì)算一次并被存儲(chǔ)。

常量也能使您的宏程序更易閱讀和維護(hù)。如果在您的程序中有一些不變的字符串或數(shù)值的話(huà)，您可以聲明它們作為常量。盡可能使用早期綁定

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

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

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

與使用計(jì)數(shù)進(jìn)展循環(huán)相比，在遍歷集合或數(shù)組時(shí)使用ForEach…Next循環(huán)將更快。在多數(shù)情況下，使用ForEach…Next循環(huán)也更方便，并且使您的宏更簡(jiǎn)潔、更容易閱讀和調(diào)試。

下面的例如運(yùn)行很慢，因?yàn)樵诿看窝h(huán)重復(fù)時(shí)它設(shè)置并調(diào)用了行變量.Row(i)。

SubDoSomethingSlow()DimStartAsDouble,FinishAsDoubleStart=Timer'DimCellAsRange,iAsLongWithSheet1.Range("A1:A10000")Fori=1To10000SetCell=.Rows(i)IfCell>0ThenCell.Font.ColorIndex=5EndIfNextEndWith'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub下面的例如代碼更簡(jiǎn)潔，其運(yùn)行速度大約是上面代碼的2～3倍。因?yàn)镕orEach…Next循環(huán)自動(dòng)記錄行數(shù)并定位，而不需要調(diào)用變量i。SubDoSomethingFaster()'快兩至三倍DimStartAsDouble,FinishAsDoubleStart=Timer'DimCellAsRangeWithSheet1ForEachCellIn.Range("A1:A10000")IfCell>0ThenCell.Font.ColorIndex=5EndIfNextEndWith'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub在執(zhí)行循環(huán)時(shí)考慮如何能夠盡可能地節(jié)省資源

(1)分析循環(huán)以查看是否正在不必要地執(zhí)行一些消耗內(nèi)存的重復(fù)操作。例如，是否可以在循環(huán)外(而不是在循環(huán)中)設(shè)置某些變量？每次都通過(guò)循環(huán)執(zhí)行的轉(zhuǎn)換過(guò)程是否可以在循環(huán)之外執(zhí)行？

(2)考慮是否必須在滿(mǎn)足特定的條件時(shí)才執(zhí)行循環(huán)。如果是，也許可以更早地退出循環(huán)。例如，假設(shè)正在對(duì)一個(gè)不應(yīng)該包含數(shù)字字符的字符串進(jìn)展數(shù)據(jù)驗(yàn)證。如果循環(huán)要檢查字符串中的每個(gè)字符以確定其中是否包含數(shù)字字符，那么您可以在找到第一個(gè)數(shù)字字符時(shí)立即退出循環(huán)。

(3)如果必須在循環(huán)中引用數(shù)組的元素，可以創(chuàng)立一個(gè)臨時(shí)變量存儲(chǔ)該元素的值，而不是引用數(shù)組中的值。從數(shù)組中檢索值比從一樣類(lèi)型的變量讀取值要慢。

(4)將屬性和方法放在循環(huán)外部

在代碼運(yùn)行時(shí)，獲取變量的值快于獲取屬性的值。因此，如果您的代碼在循環(huán)內(nèi)部獲取屬性的值，您可以在循環(huán)外部將該屬性的值先指定給一個(gè)變量，然后在循環(huán)內(nèi)部使用此變量代替屬性的值，這樣的代碼將運(yùn)行得更快。

下面所示的代碼運(yùn)行較慢，因?yàn)樵诿看沃貜?fù)循環(huán)時(shí)都必須獲取Sheet的Range屬性的值。

SubTryThisSlow()DimStartAsDouble,FinishAsDoubleStart=Timer'DimMyLoopAsLongForMyLoop=2To4001Cells(MyLoop,2)=Sheet1.Range("B1")Next'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub下面的例如與上面所產(chǎn)生的結(jié)果一樣，但比上面的要更快，因?yàn)樵谘h(huán)開(kāi)場(chǎng)以前我們已經(jīng)將Sheet的Range屬性的值指定給了單獨(dú)的變量MyVar。這樣，代碼將在每次重復(fù)循環(huán)時(shí)利用該變量的值，而不必每次都要調(diào)有屬性。SubTryThisFaster()'快約35%以上DimStartAsDouble,FinishAsDoubleStart=Timer'DimMyVarAsString,MyLoopAsLongMyVar=Sheet1.Range("B1")ForMyLoop=2To4001Cells(MyLoop,2)=MyVarNext'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub如果您在一個(gè)循環(huán)內(nèi)部使用多個(gè)對(duì)象，您也可以使用With…EndWith將您能夠移動(dòng)的對(duì)象移到循環(huán)外部。下面的例如在每次循環(huán)重復(fù)時(shí)都調(diào)用Sheets對(duì)象和Cells屬性。SubNowTryThisSlow()DimStartAsDouble,FinishAsDoubleStart=Timer'DimcAsLongForc=1To8000Sheet1.Cells(c,5)=cNext'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub對(duì)上面的代碼改寫(xiě)如下，使用With語(yǔ)句將調(diào)用Sheets對(duì)象移到循環(huán)外部，只剩余調(diào)用Cells。SubNowTryThisFaster()'約快3倍DimStartAsDouble,FinishAsDoubleStart=Timer'DimcAsLongWithSheet1Forc=1To8000.Cells(c,5)=cNextEndWith'Finish=TimerMsgBox"本次運(yùn)行時(shí)間為"&Finish-StartEndSub注：您也能通過(guò)使用對(duì)象變量在循環(huán)外部調(diào)用該對(duì)象。使用With…EndWith語(yǔ)句

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

WithWorkbooks("Book1.xls").Worksheets("Sheet1").Range("A1").Formula="=SQRT(20)"With.Font.Name="Arial".Bold=True.Size=10EndWithEndWith同理，可使用With…EndWith語(yǔ)句在同一個(gè)單元格區(qū)域中執(zhí)行多個(gè)操作。盡量減少OLE引用

調(diào)用每個(gè)VBA方法或?qū)傩远夹枰粋€(gè)或多個(gè)OLE引用，這樣在代碼中會(huì)有多個(gè)點(diǎn)運(yùn)算符，而每次代碼調(diào)用都需要對(duì)這些點(diǎn)運(yùn)算符進(jìn)展解析，這將花費(fèi)更多的時(shí)間。因此，在調(diào)用方法或?qū)傩詴r(shí)減少引用長(zhǎng)度將是使您的程序運(yùn)行更快的一種好方法。

可以通過(guò)盡量減少在VBA程序代碼中使用OLE(對(duì)象與嵌入自動(dòng)識(shí)別)引用來(lái)優(yōu)化程序代碼。VBA語(yǔ)句中所調(diào)用的方法和屬性越多，執(zhí)行語(yǔ)句所用的時(shí)間就越多。例如下面的兩個(gè)語(yǔ)句：

語(yǔ)句1：

Workbooks(1).Sheets(1).Range("A1").value="10"語(yǔ)句2：ActiveWindow.Left=200執(zhí)行時(shí)，語(yǔ)句2比語(yǔ)句1快。

同樣，上面所講的對(duì)重復(fù)使用的對(duì)象引用指定一個(gè)變量，通過(guò)調(diào)用變量從而保證防止屢次進(jìn)展對(duì)象引用。盡可能少使用“.〞，使用對(duì)象變量

在前面已經(jīng)介紹過(guò)的對(duì)長(zhǎng)對(duì)象引用使用對(duì)象變量以及使用With…EndWith等都是簡(jiǎn)化〞.〞的方法。因?yàn)樵诖a中的每個(gè)句點(diǎn)都表示至少一個(gè)(而且可能是多個(gè))過(guò)程調(diào)用，而這些過(guò)程調(diào)用必須在后臺(tái)執(zhí)行。真正好的做法是在局部進(jìn)展緩存對(duì)象引用，例如，應(yīng)該把對(duì)象模型中較高層次的對(duì)象引用保存到局部對(duì)象變量中，然后用這些對(duì)象引用創(chuàng)立其他較低層次的對(duì)象引用。例如，引用某單元格數(shù)據(jù)時(shí)，可用如下代碼：

DimiAsLongFori=1to10Workbooks("Book1.xls").Worksheets("Sheet1").Cells(1,i).Value=iNexti但下面的代碼運(yùn)行效率更高，因?yàn)榇a中引用Workbook對(duì)象和Worksheet對(duì)象的調(diào)用命令只執(zhí)行一次，而上面的代碼中卻要執(zhí)行10次。DimwsAsWorksheetDimiAsLongSetws=Workbooks("Book1.xls").Worksheets("Sheet1")Fori=1to10ws.Cells(1,i).Value=iNexti當(dāng)您一遍又一遍的使用一樣對(duì)象引用時(shí)，您可以將該對(duì)象引用設(shè)置成一個(gè)變量，然后使用該變量代替對(duì)象引用。這樣，您在代碼中只需對(duì)該對(duì)象變量進(jìn)展引用即可。

例如，下面的例如在每行中調(diào)用Workbook對(duì)象的Sheets屬性、Range屬性和Value屬性三次，當(dāng)您循環(huán)1000次時(shí)，總共要調(diào)用屬性6000次。SubDoThis1()DimStartAsDouble,FinishAsDoubleStart=Timer'DimNAsLongForN=1To1000Workbooks("Book1").Sheets(1).Range("c5").Value=10Workbooks("Book1").Sheets(1).Range("d10").Value=12Next'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub您能在循環(huán)開(kāi)場(chǎng)前通過(guò)設(shè)置Workbooks(“Book1〞).Sheets(1)作為一個(gè)對(duì)象變量來(lái)優(yōu)化上面的例子，下面的例如在每行僅調(diào)用一個(gè)Range屬性，當(dāng)循環(huán)1000次時(shí)，總共只調(diào)用該屬性2000次。

注意，“Value〞是一個(gè)缺省屬性，通常不需要明確指定它，它將被自動(dòng)調(diào)用。因此，該屬性在下面的代碼中被忽略。然而，就養(yǎng)成良好的編程習(xí)慣而言，還是建議您最好寫(xiě)明該屬性。SubDoThis2()'快約35%以上DimStartAsDouble,FinishAsDoubleStart=Timer'DimThisBookSheetAsObject,NAsLongSetThisBookSheet=Workbooks("Book1").Sheets(1)ForN=1To1000ThisBookSheet.Range("c5")=10ThisBookSheet.Range("d10")=12Next'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub您可以比擬這兩個(gè)例如的運(yùn)行速度，它們都得到同樣的結(jié)果，但在我的機(jī)子上運(yùn)行時(shí)，第二個(gè)例如比第一個(gè)快60%。當(dāng)然，您還能使用With…EndWith語(yǔ)句獲得一樣的結(jié)果。

您也能不設(shè)置明確的對(duì)象變量，而是使用With語(yǔ)句減少對(duì)象的重復(fù)引用。上面的例如也能使用下面的代碼，該代碼僅調(diào)用Workbooks屬性和Sheets屬性一次，當(dāng)循環(huán)1000次時(shí)，總共調(diào)用1000次屬性。SubDoThis3()'快約35%以上DimStartAsDouble,FinishAsDoubleStart=Timer'DimNAsLongWithWorkbooks("Book1").Sheets(1)ForN=1To1000.Range("c5")=10.Range("d10")=12NextEndWith'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub上述三個(gè)例如均得到一樣的結(jié)果，但在我的機(jī)子上運(yùn)行時(shí)，本例如比第一個(gè)例如快50%以上。在一個(gè)語(yǔ)句中進(jìn)展復(fù)制或者粘貼

在用宏錄制代碼時(shí)，首先是選擇一個(gè)區(qū)域，然后再執(zhí)行ActiveSheet.Paste。在使用Copy方法時(shí)，可以在一個(gè)語(yǔ)句中指定復(fù)制的內(nèi)容及要復(fù)制到的目的地。

例如，將B5:C6區(qū)域的內(nèi)容復(fù)制到以單元格B8開(kāi)場(chǎng)的區(qū)域中，使用宏錄制器的代碼為：

Range("B5:C6").SelectSelection.CopyRange("B8").SelectActiveSheet.Paste經(jīng)修改后的最正確代碼是：Range("B5:C6").CopyDestination:=Range("B8")合理地使用消息框和窗體

在一個(gè)很長(zhǎng)的程序中，嘗試著將消息框或者窗體安排顯示在程序的最開(kāi)場(chǎng)或最后面，防止干擾用戶(hù)。此外，盡管窗體提供了許多功能，但它們能夠?qū)е挛募笮⊙杆僭黾?。還有就是盡量防止給工作表單元格用戶(hù)窗體控件，因?yàn)檫@樣將會(huì)導(dǎo)致更新操作，影響程序運(yùn)行速度。盡可能加速對(duì)數(shù)字的運(yùn)算

(1)當(dāng)對(duì)整數(shù)進(jìn)展除法時(shí)，您可以使用整型除法運(yùn)算符(\)而不是浮點(diǎn)除法運(yùn)算符(/)，因?yàn)闊o(wú)論參與除法運(yùn)算的數(shù)值類(lèi)型如何，浮點(diǎn)除法運(yùn)算符總會(huì)返回Double類(lèi)型的值。

(2)在任何具有整數(shù)值的算術(shù)表達(dá)式中使用Single或Double值時(shí)，整數(shù)均將被轉(zhuǎn)換成Single或Double值，最后的結(jié)果將是Single或Double值。如果要對(duì)作為算術(shù)運(yùn)算結(jié)果的數(shù)字執(zhí)行屢次操作，可能需要明確地將該數(shù)字轉(zhuǎn)換為較小的數(shù)據(jù)類(lèi)型。提高字符串操作的性能

(1)盡可能少使用連接操作?？梢栽诘忍?hào)左邊使用Mid函數(shù)替換字符串中的字符，而不是將它們連接在一起。使用Mid函數(shù)的缺點(diǎn)是替換字符串必須與要替換的子字符串的長(zhǎng)度一樣。例如，

DimstrTextAsStringstrText="thisisatest"Mid(strText,11,4)="tent"(2)VBA提供許多可用來(lái)替換函數(shù)調(diào)用的內(nèi)部字符串常量。例如，可以使用vbCrLf常量來(lái)表示字符串中的回車(chē)/換行組合，而不是使用Chr(13)&Chr(10)。

(3)字符串比擬操作的執(zhí)行速度很慢。有時(shí)，可以通過(guò)將字符串中的字符轉(zhuǎn)換為ANSI值來(lái)防止這些操作。例如，以下代碼會(huì)檢查字符串中的第一個(gè)字符是否為空格：IfAsc(strText)=32Then上面的代碼會(huì)比以下代碼更快：IfLeft(strText,1)=""Then使用Asc()檢驗(yàn)ANSI的值

在VBA中，可以使用Chr$()函數(shù)把數(shù)轉(zhuǎn)換成字符，并確定ANSI的值，但是更好的是使用Asc()函數(shù)把字符串轉(zhuǎn)換成數(shù)值，然后確定它的ANSI值。如果需要進(jìn)展有限次數(shù)的這種檢驗(yàn)，對(duì)程序代碼的效率可能不會(huì)產(chǎn)生很大影響，但是，如果需要在多個(gè)循環(huán)內(nèi)進(jìn)展這種檢驗(yàn)時(shí)，這將節(jié)省處理時(shí)間并且有助于程序代碼更快地執(zhí)行。使用Len()檢驗(yàn)空串

盡管有多種方法可檢驗(yàn)空串，但首選的是使用Len()函數(shù)。為了測(cè)試零長(zhǎng)度的串，可以選擇把串與〞〞相比擬，或者比擬串的長(zhǎng)度是否為0，但這些方法比用Len()函數(shù)要用更多的執(zhí)行時(shí)間。當(dāng)對(duì)字符串應(yīng)用Len()函數(shù)并且函數(shù)返回0值時(shí)，說(shuō)明該字符串是空的或者是零長(zhǎng)度的字符串。

并且，因?yàn)樵贗f語(yǔ)句內(nèi)非零值被認(rèn)為是True，所以直接使用Len()函數(shù)而不必與〞〞或0比擬，減少了處理時(shí)間，因此執(zhí)行更快。有效地使用數(shù)組

用VBA數(shù)組而不是單元格區(qū)域來(lái)處理數(shù)據(jù)，即可以先將數(shù)據(jù)寫(xiě)入到某個(gè)數(shù)組，然后用一個(gè)語(yǔ)句就可以將數(shù)組中的數(shù)據(jù)傳遞到單元格區(qū)域中?！睬拔囊咽觥?/p>

在創(chuàng)立元素確實(shí)定數(shù)組時(shí)，使用Array函數(shù)對(duì)于節(jié)約空間和時(shí)間以及寫(xiě)出更具效率的代碼是非常理想的。例如，

DimNamesAsVariantNames=Array("Fan","Yang","Wu","Shen")此外，應(yīng)該盡量使用固定大小的數(shù)組。如果確實(shí)選擇使用了動(dòng)態(tài)數(shù)組，應(yīng)該防止數(shù)組每增加一個(gè)元素就改變一次數(shù)組的大小，最好是每次增加一定數(shù)量的元素。使用Excel的內(nèi)置函數(shù)

對(duì)于要實(shí)現(xiàn)的某一功能，如果有Excel的內(nèi)置函數(shù)能夠?qū)崿F(xiàn)，那么就用Excel的內(nèi)置函數(shù)，不需要另外自定義函數(shù)，因?yàn)樽远x的函數(shù)總比Excel內(nèi)置的函數(shù)慢。

考慮在VBA代碼中使用工作表函數(shù)

操作單元格區(qū)域的Excel工作表函數(shù)通常比完成同樣任務(wù)的VBA程序更快(但不能確?？偸沁@樣，您可以對(duì)它們進(jìn)展速度測(cè)試)

例如，在代碼中使用SUM工作表函數(shù)比用VBA代碼在單元格區(qū)域中循環(huán)并相加值要快得多，以此為例，下面的代碼運(yùn)行速度相對(duì)較慢。

SubAddItSlow()DimStartAsDouble,FinishAsDoubleStart=Timer''為了進(jìn)展測(cè)試,我們循環(huán)5次DimNAsLongForN=1To5'***************************DimCellAsRangeForEachCellInWorksheets(2).Range("A1:G200")[a1]=[a1]+Cell.ValueNextCell'***************************NextN'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub下面的代碼實(shí)現(xiàn)一樣的功能，但運(yùn)行得更快(幾乎瞬間完成)。SubAddItFaster()'快近600倍DimStartAsDouble,FinishAsDoubleStart=Timer''為了進(jìn)展測(cè)試,我們循環(huán)5次DimNAsLongForN=1To5'***************************[a1]=Application.WorksheetFunction._Sum(Worksheets(2).Range("A1:G200"))'***************************Next'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub產(chǎn)生統(tǒng)計(jì)結(jié)果的函數(shù)(例如PRODUCT、COUNT、COUNTA和COUNTIF)是代替運(yùn)行速度更慢的VBA代碼的很好的選擇，并且，一些工作表函數(shù)(例如MATCH和LOOKUP)能夠?qū)卧駞^(qū)域作為參數(shù)。

不要認(rèn)為工作表函數(shù)總是更快的

如下例所示，在VBA中沒(méi)有Max或Min函數(shù)，但Excel中有該函數(shù)。于是，您能編寫(xiě)出如下代碼：SubMaxIt1()DimStartAsDouble,FinishAsDoubleStart=Timer''為了測(cè)試,我們循環(huán)10000次DimNAsLongForN=1To10000'***************************[J1]=Application.Max([J2],[J3])'***************************NextN'Finish=TimerMsgBox"本次運(yùn)行時(shí)間是"&Finish-StartEndSub或者，您能在VBA中使用下面的方式實(shí)現(xiàn)一樣的功能：SubMaxIt2()DimStartAsDouble,FinishAsDoubleStart=Timer''為了測(cè)試,我們循環(huán)10000次DimNAsLongForN=1To10000'***************************If[J2]>=[J3]Then[J1]=[J2]Else[J1]=[J3]'***************************NextN'Finish=TimerMsgBox"本次運(yùn)行的時(shí)間是"&Finish-StartEndSub比擬上面的兩個(gè)程序，可能認(rèn)為使用工作表函數(shù)會(huì)更快，但事實(shí)上用VBA代碼可以獲得幾乎一樣的速度。因此，在一些大的循環(huán)中，您可以對(duì)實(shí)現(xiàn)同樣功能的工作表函數(shù)的VBA代碼進(jìn)展測(cè)試。一些內(nèi)置的VBA函數(shù)事實(shí)上運(yùn)行速度也是慢的，因此，在編寫(xiě)代碼時(shí)，在不同方式之間進(jìn)展速度測(cè)試總是值得的。

但是，在代碼中經(jīng)常使用的簡(jiǎn)單的函數(shù)，就直接編寫(xiě)代碼，而不是使用WorksheetFunction對(duì)象。使用Range.SpecialCells()來(lái)縮小需要處理的單元格數(shù)。只要有可能就使用集合索引值

您能在集合中使用名稱(chēng)或者數(shù)字來(lái)指定某個(gè)單一的對(duì)象，但使用對(duì)象的索引值通常是更快的。如果您使用對(duì)象的名字，VBA必須解析名字成為索引值；但如果您使用索引值，就能防止這個(gè)額外的步驟。

但另一方面，我們要注意到在集合中通過(guò)名稱(chēng)指定對(duì)象有很多優(yōu)點(diǎn)。使用對(duì)象名稱(chēng)能使您的代碼更容易閱讀和調(diào)試。此外，通過(guò)名稱(chēng)指定一個(gè)對(duì)象比通過(guò)索引值更平安，因?yàn)楫?dāng)您的代碼運(yùn)行時(shí)該對(duì)象的索引值可能變化。

例如，某菜單的索引值表示它在菜單欄中的位置，但是如果在菜單欄中添加了菜單或者刪除了菜單，該菜單的索引值會(huì)變化。這樣，您就不應(yīng)該考慮代碼的速度，而應(yīng)保證代碼運(yùn)行可靠。您使用索引值加快代碼速度之前，應(yīng)該確保該索引值在代碼運(yùn)行過(guò)程中或使用應(yīng)用程序時(shí)不會(huì)改變。使用完全受限制的對(duì)象引用

使用完全受限制的對(duì)象引用消除了引用模糊并確保變量有明確的類(lèi)型。

一個(gè)完全受限制的對(duì)象引用包括了對(duì)象庫(kù)名稱(chēng)，如下代碼所示：

DimwbAsExcel.Workbook如果您使用通用的對(duì)象數(shù)據(jù)類(lèi)型聲明變量和參數(shù)，在運(yùn)行過(guò)程中VBA可能必須對(duì)它們的引用進(jìn)展解析為(某對(duì)象的)屬性和方法，這將導(dǎo)致速度變慢。

一個(gè)通用對(duì)象數(shù)據(jù)類(lèi)型例如如下：DimwbAsWorkbook使用已有的VBA方法

也有一些特定目的的VBA方法，它們提供在單元格區(qū)域執(zhí)行特定操作的一種簡(jiǎn)單的方式。例如工作表函數(shù)，這些特定的方法比使用通常的VBA編碼完成一樣的任務(wù)要更快。最常用的是〞Replace〞方法和〞Find〞方法。

Replace方法:

下面的例如用了一種相當(dāng)慢的方式代碼改變單元格區(qū)域H1:H20000中每個(gè)單元格的值