'獲得當前的Excel設(shè)置狀態(tài),將其放置在代碼的開頭screenUpdateState = Application.ScreenUpdatingstatusBarState = Application.DisplayStatusBarcalcState = Application.CalculationeventsState = Application.EnableEventsdisplayPageBreakState = ActiveSheet.DisplayPageBreaks '注:這是工作表級的設(shè)置'關(guān)閉一些Excel功能使代碼運行更快Application.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.Calculation = xlCalculationManualApplication.EnableEvents = FalseActiveSheet.DisplayPageBreaks = False '注:這是工作表級的設(shè)置
（2）放置在主代碼結(jié)束后的一段代碼，用來將Excel恢復到代碼運行前的設(shè)置
'代碼運行后,恢復Excel原來的狀態(tài);將下面的代碼放在代碼的末尾Application.ScreenUpdating = screenUpdateStateApplication.DisplayStatusBar = statusBarStateApplication.Calculation = calcStateApplication.EnableEvents = eventsStateActiveSheet.DisplayPageBreaks = displayPageBreaksState '注:這是工作表級的設(shè)置下面簡要解釋這些設(shè)置：
Application.ScreenUpdating：將該屬性設(shè)置為False，告訴Excel不要重繪屏幕。其優(yōu)點是不需要Excel花費資源來繪制屏幕，因而其改變會更快而不致讓用戶察覺其變化。因為如此頻繁地繪制屏幕需要大量的資源，所以關(guān)閉繪制屏幕直到代碼執(zhí)行結(jié)束。在代碼結(jié)束前，確保重新開啟了該屬性。
Application.DisplayStatusBar：將該屬性設(shè)置為False，告訴Excel停止顯示狀態(tài)欄。例如，如果使用VBA復制/粘貼單元格，當粘貼執(zhí)行時Excel將在狀態(tài)欄中顯示操作的進度。關(guān)閉屏幕更新不會關(guān)閉狀態(tài)欄顯示，因此，如果需要的話，可以禁用屏幕更新而仍然可以通過狀態(tài)欄給用戶提供反饋。記住，如果將該屬性設(shè)置為False，在代碼結(jié)束前應(yīng)該將其設(shè)置為True。
Application.Calculation：該屬性允許編程設(shè)置Excel的計算模式。“手工的”（xlCalculationManual）模式意味著Excel等待用戶（或代碼）來觸發(fā)計算；默認為“自動的”（xlCalculationAutomatic）模式，意味著由Excel來決定何時重新計算工作簿（例如，當在工作表中輸入新公式時）。由于重新計算工作簿將花費時間且浪費資源，因此可能不希望每次改變單元格值時Excel都觸發(fā)重新計算。當代碼執(zhí)行時關(guān)閉重新計算，在代碼結(jié)束前再設(shè)置回重新計算模式。
Application.EnableEvents：將該屬性設(shè)置為False，告訴Excel不要觸發(fā)事件。你可能不希望Excel為每個正在通過代碼發(fā)生改變的單元格觸發(fā)事件，關(guān)閉事件將加速VBA代碼的執(zhí)行。
ActiveSheet.DisplayPageBreaks：當在較新版本的Excel中運行VBA時，則可能比在早期版本的Excel中需要更長的時間完成。例如，需要幾秒鐘在早期版本的Excel中完成的宏可能需要幾分鐘才能在更高版本的Excel中完成。或者，第二次運行一個宏可能比第一次運行需要的時間更長。這是由于VBA宏修改了多行或列的屬性，或者必須強制執(zhí)行計算Excel分頁符。如果宏設(shè)置了任何PageSetup屬性或者手動設(shè)置了PageSetup屬性，接著運行較大區(qū)域的行或列屬性設(shè)置時會出現(xiàn)這樣的問題。您可以將該屬性設(shè)置為False來提高代碼的運行速度。當然，在代碼運行結(jié)束前，應(yīng)將該屬性恢復為原設(shè)置。
在單個操作中讀/寫大塊的單元格區(qū)域
本技巧用于優(yōu)化在Excel和代碼之間轉(zhuǎn)換數(shù)據(jù)的次數(shù)。使用數(shù)組變量存儲所需要的值并執(zhí)行取值或賦值操作，而不是一次遍歷單個單元格并獲取或設(shè)置單個值。
例如，下面的代碼在單元格區(qū)域A1：C10000中放置隨機數(shù)。
代碼段一：運行速度較慢的代碼
Sub testSlow()Dim DataRange As RangeDim Irow As LongDim Icol As IntegerDim MyVar As DoubleSet DataRange = Range("A1:C10000") For Irow = 1 To 10000For Icol = 1 To 3MyVar = DataRange(Irow, Icol) '從Excel單元格中讀取值30K次If MyVar > 0 ThenMyVar = MyVar * MyVar ' 改變值DataRange(Irow, Icol) = MyVar '將值寫入Excel單元格中30000次End IfNext IcolNext IrowEnd Sub
代碼段二：運行速度更快的代碼
Sub testFast()Dim DataRange As VariantDim Irow As LongDim Icol As IntegerDim MyVar As DoubleDataRange = Range("A1:C10000").Value '一次從Excel單元格中讀取所有的值,將其放入數(shù)組For Irow = 1 To 10000For Icol = 1 To 3MyVar = DataRange(Irow, Icol)If MyVar > 0 ThenMyVar = MyVar * MyVar ' 改變數(shù)組中的值DataRange(Irow, Icol) = MyVarEnd IfNext IcolNext IrowRange("A1:C10000").Value = DataRange '一次將所有結(jié)果寫回單元格End Sub
避免選取/激活對象
使用選取的方法更新單元格區(qū)域是最慢的。在試驗了使用Range對象、使用Variant類型和使用Select方法對一個大的單元格區(qū)域讀寫數(shù)據(jù)的操作后，Select方法是最慢的。
再來看一個例子：在工作表中有40個形狀，在每個形狀中寫入“Hello”。使用Select方法的代碼為：
Sub testSlow()Dim i As IntegerFor i = 0 To ActiveSheet.Shapes.CountActiveSheet.Shapes(i).SelectSelection.Text = "Hello"Next iEnd Sub
運行速度更快的方法是完全避免使用選取并直接引用形狀：
Sub testFast()Dim i As IntegerFor i = 0 To ActiveSheet.Shapes.CountActiveSheet.Shapes(i).TextEffect.Text = "Hello"Next iEnd Sub
在使用宏錄制器時，所生成的程序代碼在應(yīng)用任何方法或?qū)傩灾岸紩せ罨蛘哌x擇對象。但是，并不是在所有的情況下都需要這樣做。所以，在您編寫VBA程序代碼時，不需要在對對象執(zhí)行任何任務(wù)之前都激活或者選擇每個對象。
例如，在Excel中，我們?nèi)绻沟谝恍凶兂纱煮w就必須先選項中它。但在VBA中(除在圖表操作時需要選中圖表對象外)，很少需要這樣做，即VBA可以在不選中第一行的情況下，將它變成粗體。
宏錄制器的代碼：
Rows("1:1").SelectSelection.Font.Bold = True
改編后的代碼為：
Row(“1:1”).Font.Bold=True這樣做還可以使程序代碼更簡潔，并且程序可以運行得更快。
工作簿設(shè)計
好的工作簿設(shè)計和數(shù)據(jù)組織有助于編寫運行良好的代碼。良好設(shè)計的工作簿，其執(zhí)行效率和維護量將大大優(yōu)化?？梢哉f，工作簿設(shè)計是從大的宏觀方面進行優(yōu)化，而對代碼的優(yōu)化只是一些微觀的細節(jié)上的優(yōu)化。
其他
- 盡量簡化代碼
通過簡化代碼，可以提高程序的性能。您可以將通用過程編寫為子過程來調(diào)用。例如，假設(shè)有一個應(yīng)用程序需要在不同的地方實現(xiàn)查找一定范圍內(nèi)的某個特殊條目，在一個沒有簡化代碼的應(yīng)用程序中，不同的過程可能需要應(yīng)用各自的算法以實現(xiàn)在某個范圍內(nèi)查找某一條目，修改每個過程使其采用一個更有效的算法并不是一件很容易的事。而一個簡化的程序則只有一個查找算法，即將該查找算法編寫成通用的子程序，需要查找某個范圍的過程都調(diào)用該子程序，通過在查找方法的子程序中優(yōu)化查找算法，使得調(diào)用該方法的所有過程都享受性能提高所帶來的好處。
另外，刪除所有無關(guān)的代碼，這在所錄制宏中表現(xiàn)得尤為明顯。在錄制宏時，經(jīng)常會產(chǎn)生一些與所實現(xiàn)的功能無關(guān)的代碼，您可以將這些代碼刪除，以使得代碼得以簡化。
宏錄制器生成無效代碼的一個原因是它不知道在對話框中您選擇了哪些選項，因此，當您關(guān)閉對話框時它將直接記錄所有可用的選項。例如，選擇單元格區(qū)域G2:G20，然后在單元格格式對話框中改變字體樣式為粗體，使用宏錄制器生成的代碼如下：Sub NowThis1() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- '為了進行測試,將循環(huán)100次 Dim N As Long For N = 1 To 100 '*************************** Range("G2:G20").Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlNone .ColorIndex = xlAutomatic End With '*************************** Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
您能只用下面的一行代碼為指定的單元格設(shè)置字體樣式，不需要選擇單元格區(qū)域。
Range("G2:G20").Font.FontStyle = "Bold"
如果您考慮到您想要宏所做的事情(本例中為使字體加粗)，那么您可以查閱應(yīng)用到Font對象的屬性和方法列表，您將知道只需使用Bold屬性編寫這個宏代碼以實現(xiàn)所需的功能。代碼如下：
Sub NowThis2() '快約10倍 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- '為進行測試,將循環(huán)100次 Dim N As Long For N = 1 To 100 '*************************** Range("G2:G20").Font.Bold = True '*************************** Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間為" & Finish - Start End Sub
您也能在用戶界面中通過執(zhí)行不同的方法來錄制產(chǎn)生結(jié)果相同的操作對宏錄制器進行試驗。例如，如果您通過標準工具欄上的粗體按鈕格式化某區(qū)域為粗體，那么宏錄制器將使用Bold屬性。
下面將要講到的設(shè)置對象變量代替長對象引用，使用With…End With語句、執(zhí)行For Each…Next循環(huán)語句，根據(jù)程序環(huán)境盡量減少OLE引用，等等，均是簡化代碼的好方法。 - 強制聲明變量
在VBE編輯器中的菜單“工具——選項”對話框中“編輯器”選項卡中，您應(yīng)該始終保持“要求變量聲明”復選框被選中，這樣將在模塊代碼頂部出現(xiàn)Option Explicit語句，要求您在編寫代碼時對所有出現(xiàn)的變量均進行聲明，這樣，在使用變量時減少內(nèi)存需求并加速性能。
(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)換的情況外，應(yīng)始終使用盡可能小的數(shù)據(jù)類型。例如，因為Integer類型的變量將被轉(zhuǎn)換成Long類型的變量，應(yīng)該將那些存儲整型值的變量聲明為Long類型，而不是Integer類型。
(3)除非確實需要，應(yīng)避免使用浮點數(shù)據(jù)類型。盡管Currency數(shù)據(jù)類型更大，但它比 Single 數(shù)據(jù)類型快，因為Currency數(shù)據(jù)類型不使用浮點處理器。
(4)如果在一個過程中多次引用一個對象，可以創(chuàng)建對象變量，并將對給對象的引用指派給它。因為對象變量存儲對象在內(nèi)存中的位置，VBA將不必再次查找其位置。
(5)將對象變量聲明為特定的類型(不是Object類型)，以便利用早期綁定。
(6) 減少”Variant”類型變量的使用
雖然您可能發(fā)現(xiàn)在您的代碼中使用Variant(變體)變量是方便的，但是如果您將變量清楚地聲明為特定的數(shù)據(jù)類型，然后用VBA處理存儲在該變量中的值，要比處理存儲在Variant變量里的值快。
如果執(zhí)行不涉及分數(shù)值的數(shù)學運算，那么在您的代碼中使用Long型變量比使用Variant變量更快。Long型變量也是在For…Next循環(huán)中索引值變量類型的最好選擇。
然而，您要注意到，您使用特定類型變量所獲取的速度是以失去靈活性為代價的。例如，當使用特定數(shù)據(jù)類型時，您可能遭到變量溢出或類型不匹配的情形，而不會像Variant變量會自動進行類型轉(zhuǎn)換處理。
(7) 聲明時指定特定的對象類型
當您的宏被編譯或者是運行(后臺編譯)時，會解析對象及它們的方法和屬性的引用。經(jīng)過宏編譯解析的引用比在程序運行時必須被解析的引用要更快，因此，您最好跳過后臺編譯。
如果您聲明變量和參數(shù)為特定的對象類型(比如Range或Worksheet)，VBA在編譯您的程序時將解析引用為這些對象的屬性和方法。(如果要查找指定對象類型列表，請參見”對象瀏覽器”) - 減少變量的作用范圍并及時釋放變量
主要是對象變量，在其使用完后，及時釋放。例如，Dim TempObj As AnyObject,AnObj As AnyObject Set TempObj=New AnyObject Set AnObj=TempObj Set TempObj=Nothing ‘釋放對象變量
- 使用常量
變量會發(fā)生變化，因此VBA在程序運行時必須獲取當前變量的值。
在應(yīng)用程序中使用常量會使程序運行更快。在編譯您的代碼時，常量僅計算一次并被存儲。
常量也能使您的宏程序更易閱讀和維護。如果在您的程序中有一些不變的字符串或數(shù)值的話，您可以聲明它們作為常量。 - 盡可能使用早期綁定
綁定是指將程序調(diào)用與實際代碼相匹配。為了實現(xiàn)早期綁定，先應(yīng)創(chuàng)建對對象庫的引用。早期綁定可以在代碼中使用定義在對象庫中的常量，可以自動列出對象的方法和屬性，但早期綁定只有在所控制的對象擁有獨立的類型庫或?qū)ο髱煳募胚m用且還需要已安裝了特定的庫。而后期綁定則只是在運行時才知道對象的類型并對對象進行引用，因此不具備上述特點。
使用早期綁定創(chuàng)建對象通常更有效率，使代碼能獲得更好的性能。因為對象的早期綁定引用在編譯時可以通過VBE的解析，而不是通過運行時模塊解析，因此早期綁定的性能要好得多。雖然在程序設(shè)計時不可能總是使用早期綁定，但應(yīng)該盡可能使用它。 - 使用For Each…Next循環(huán)
可以使用For Each…Next循環(huán)來保證程序代碼更快地執(zhí)行。在使用For Each…Next循環(huán)時，對于存儲在集合或數(shù)組中的每個對象執(zhí)行一組語句，程序更簡潔，也更容易閱讀、調(diào)試和維護。當For Each…Next語句迭代集合時，自動指定一個對集合當前成員的引用，然后在到達集合的尾部時跳出循環(huán)語句。
與使用計數(shù)進行循環(huán)相比，在遍歷集合或數(shù)組時使用For Each…Next循環(huán)將更快。在多數(shù)情況下，使用For Each…Next循環(huán)也更方便，并且使您的宏更簡潔、更容易閱讀和調(diào)試。
下面的示例運行很慢，因為在每次循環(huán)重復時它設(shè)置并調(diào)用了行變量.Row(i)。Sub DoSomethingSlow() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim Cell As Range, i As Long With Sheet1.Range("A1:A10000") For i = 1 To 10000 Set Cell = .Rows(i) If Cell > 0 Then Cell.Font.ColorIndex = 5 End If Next End With '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
下面的示例代碼更簡潔，其運行速度大約是上面代碼的2～3倍。因為For Each…Next循環(huán)自動記錄行數(shù)并定位，而不需要調(diào)用變量i。
Sub DoSomethingFaster() '快兩至三倍 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim Cell As Range With Sheet1 For Each Cell In .Range("A1:A10000") If Cell > 0 Then Cell.Font.ColorIndex = 5 End If Next End With '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
- 在執(zhí)行循環(huán)時考慮如何能夠盡可能地節(jié)省資源
(1)分析循環(huán)以查看是否正在不必要地執(zhí)行一些消耗內(nèi)存的重復操作。例如，是否可以在循環(huán)外(而不是在循環(huán)中)設(shè)置某些變量？每次都通過循環(huán)執(zhí)行的轉(zhuǎn)換過程是否可以在循環(huán)之外執(zhí)行？
(2)考慮是否必須在滿足特定的條件時才執(zhí)行循環(huán)。如果是，也許可以更早地退出循環(huán)。例如，假設(shè)正在對一個不應(yīng)該包含數(shù)字字符的字符串進行數(shù)據(jù)驗證。如果循環(huán)要檢查字符串中的每個字符以確定其中是否包含數(shù)字字符，那么您可以在找到第一個數(shù)字字符時立即退出循環(huán)。
(3)如果必須在循環(huán)中引用數(shù)組的元素，可以創(chuàng)建一個臨時變量存儲該元素的值，而不是引用數(shù)組中的值。從數(shù)組中檢索值比從相同類型的變量讀取值要慢。
(4) 將屬性和方法放在循環(huán)外部
在代碼運行時，獲取變量的值快于獲取屬性的值。因此，如果您的代碼在循環(huán)內(nèi)部獲取屬性的值，您可以在循環(huán)外部將該屬性的值先指定給一個變量，然后在循環(huán)內(nèi)部使用此變量代替屬性的值，這樣的代碼將運行得更快。
下面所示的代碼運行較慢，因為在每次重復循環(huán)時都必須獲取Sheet的Range屬性的值。Sub TryThisSlow() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim MyLoop As Long For MyLoop = 2 To 4001 Cells(MyLoop, 2) = Sheet1.Range("B1") Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
下面的示例與上面所產(chǎn)生的結(jié)果相同，但比上面的要更快，因為在循環(huán)開始以前我們已經(jīng)將Sheet的Range屬性的值指定給了單獨的變量MyVar。這樣，代碼將在每次重復循環(huán)時利用該變量的值，而不必每次都要調(diào)有屬性。
Sub TryThisFaster() '快約35%以上 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim MyVar As String, MyLoop As Long MyVar = Sheet1.Range("B1") For MyLoop = 2 To 4001 Cells(MyLoop, 2) = MyVar Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
如果您在一個循環(huán)內(nèi)部使用多個對象訪問，您也可以使用With…End With將您能夠移動的對象移到循環(huán)外部。下面的示例在每次循環(huán)重復時都調(diào)用Sheets對象和Cells屬性。
Sub NowTryThisSlow() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim c As Long For c = 1 To 8000 Sheet1.Cells(c, 5) = c Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
對上面的代碼改寫如下，使用With語句將調(diào)用Sheets對象移到循環(huán)外部，只剩余調(diào)用Cells。
Sub NowTryThisFaster() '約快3倍 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim c As Long With Sheet1 For c = 1 To 8000 .Cells(c, 5) = c Next End With '-------------------------------------- Finish = Timer MsgBox "本次運行時間為" & Finish - Start End Sub
注：您也能通過使用對象變量在循環(huán)外部調(diào)用該對象。
- 使用With…End With語句
可以使用With…End With語句來盡量減少對象引用。使用With語句對指定的對象完成一系列的任務(wù)，而不用重復引用對象。也可以使用嵌套的With語句進一步提高程序代碼的效率。例如，下面的使用With…End With語句是在同一個單元格中執(zhí)行多個操作。With Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1") .Formula="=SQRT(20)" With .Font .Name="Arial" .Bold=True .Size=10 End With End With
同理，可使用With…End With語句在同一個單元格區(qū)域中執(zhí)行多個操作。
- 盡量減少OLE引用
調(diào)用每個VBA方法或?qū)傩远夹枰粋€或多個OLE引用，這樣在代碼中會有多個點運算符，而每次代碼調(diào)用都需要對這些點運算符進行解析，這將花費更多的時間。因此，在調(diào)用方法或?qū)傩詴r減少引用長度將是使您的程序運行更快的一種好方法。
可以通過盡量減少在VBA程序代碼中使用OLE(對象鏈接與嵌入自動識別)引用來優(yōu)化程序代碼。VBA語句中所調(diào)用的方法和屬性越多，執(zhí)行語句所用的時間就越多。例如下面的兩個語句：
語句1：Workbooks(1).Sheets(1).Range("A1").value="10"
語句2：
ActiveWindow.Left=200
執(zhí)行時，語句2比語句1快。
同樣，上面所講的對重復使用的對象引用指定一個變量，通過調(diào)用變量從而保證避免多次進行對象引用。 - 盡可能少使用“.”，使用對象變量
在前面已經(jīng)介紹過的對長對象引用使用對象變量以及使用With…End With等都是簡化”.”的方法。因為在代碼中的每個句點都表示至少一個(而且可能是多個)過程調(diào)用，而這些過程調(diào)用必須在后臺執(zhí)行。真正好的做法是在局部進行緩存對象引用，例如，應(yīng)該把對象模型中較高層次的對象引用保存到局部對象變量中，然后用這些對象引用創(chuàng)建其他較低層次的對象引用。例如，引用某單元格數(shù)據(jù)時，可用如下代碼：Dim i As Long For i=1 to 10 Workbooks("Book1.xls").Worksheets("Sheet1").Cells(1,i).Value=i Next i
但下面的代碼運行效率更高，因為代碼中引用Workbook對象和Worksheet對象的調(diào)用命令只執(zhí)行一次，而上面的代碼中卻要執(zhí)行10次。
Dim ws As Worksheet Dim i As Long Set ws= Workbooks("Book1.xls").Worksheets("Sheet1") For i=1 to 10 ws.Cells(1,i).Value=i Next i
當您一遍又一遍的使用相同對象引用時，您可以將該對象引用設(shè)置成一個變量，然后使用該變量代替對象引用。這樣，您在代碼中只需對該對象變量進行引用即可。
例如，下面的示例在每行中調(diào)用Workbook對象的Sheets屬性、Range屬性和Value屬性三次，當您循環(huán)1000次時，總共要調(diào)用屬性6000次。Sub DoThis1() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim N As Long For N = 1 To 1000 Workbooks("Book1").Sheets(1).Range("c5").Value = 10 Workbooks("Book1").Sheets(1).Range("d10").Value = 12 Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
您能在循環(huán)開始前通過設(shè)置Workbooks(“Book1”).Sheets(1)作為一個對象變量來優(yōu)化上面的例子，下面的示例在每行僅調(diào)用一個Range屬性，當循環(huán)1000次時，總共只調(diào)用該屬性2000次。
注意，“Value”是一個缺省屬性，通常不需要明確指定它，它將被自動調(diào)用。因此，該屬性在下面的代碼中被忽略。然而，就養(yǎng)成良好的編程習慣而言，還是建議您最好寫明該屬性。Sub DoThis2() '快約35%以上 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim ThisBookSheet As Object, N As Long Set ThisBookSheet = Workbooks("Book1").Sheets(1) For N = 1 To 1000 ThisBookSheet.Range("c5") = 10 ThisBookSheet.Range("d10") = 12 Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
您可以比較這兩個示例的運行速度，它們都得到同樣的結(jié)果，但在我的機子上運行時，第二個示例比第一個快60%。當然，您還能使用With…End With語句獲得相同的結(jié)果。
您也能不設(shè)置明確的對象變量，而是使用With語句減少對象的重復引用。上面的示例也能使用下面的代碼，該代碼僅調(diào)用Workbooks屬性和Sheets屬性一次，當循環(huán)1000次時，總共調(diào)用1000次屬性。Sub DoThis3() '快約35%以上 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim N As Long With Workbooks("Book1").Sheets(1) For N = 1 To 1000 .Range("c5") = 10 .Range("d10") = 12 Next End With '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
上述三個示例均得到相同的結(jié)果，但在我的機子上運行時，本示例比第一個示例快50%以上。
- 在一個語句中進行復制或者粘貼
在用宏錄制代碼時，首先是選擇一個區(qū)域，然后再執(zhí)行ActiveSheet.Paste。在使用Copy方法時，可以在一個語句中指定復制的內(nèi)容及要復制到的目的地。
例如，將B5:C6區(qū)域的內(nèi)容復制到以單元格B8開始的區(qū)域中，使用宏錄制器的代碼為：Range("B5:C6").Select Selection.Copy Range("B8").Select ActiveSheet.Paste
經(jīng)修改后的最佳代碼是：
Range("B5:C6").Copy Destination:=Range("B8")
- 合理地使用消息框和窗體
在一個很長的程序中，嘗試著將消息框或者窗體安排顯示在程序的最開始或最后面，避免干擾用戶。此外，盡管窗體提供了許多功能，但它們能夠?qū)е挛募笮⊙杆僭黾印＿€有就是盡量避免給工作表單元格鏈接用戶窗體控件，因為這樣將會導致鏈接更新操作，影響程序運行速度。 - 盡可能加速對數(shù)字的運算
(1)當對整數(shù)進行除法時，您可以使用整型除法運算符(\)而不是浮點除法運算符(/)，因為無論參與除法運算的數(shù)值類型如何，浮點除法運算符總會返回Double類型的值。
(2)在任何具有整數(shù)值的算術(shù)表達式中使用Single或Double值時，整數(shù)均將被轉(zhuǎn)換成Single或Double值，最后的結(jié)果將是Single或Double值。如果要對作為算術(shù)運算結(jié)果的數(shù)字執(zhí)行多次操作，可能需要明確地將該數(shù)字轉(zhuǎn)換為較小的數(shù)據(jù)類型。 - 提高字符串操作的性能
(1)盡可能少使用連接操作?？梢栽诘忍栕筮吺褂肕id函數(shù)替換字符串中的字符，而不是將它們連接在一起。使用 Mid 函數(shù)的缺點是替換字符串必須與要替換的子字符串的長度相同。例如，Dim strText As String strText = "this is a test" Mid(strText, 11, 4) = "tent"
(2)VBA提供許多可用來替換函數(shù)調(diào)用的內(nèi)部字符串常量。例如，可以使用vbCrLf常量來表示字符串中的回車/換行組合，而不是使用Chr(13) & Chr(10)。
(3)字符串比較操作的執(zhí)行速度很慢。有時，可以通過將字符串中的字符轉(zhuǎn)換為 ANSI 值來避免這些操作。例如，下列代碼會檢查字符串中的第一個字符是否為空格：If Asc(strText) = 32 Then
上面的代碼會比以下代碼更快：
If Left(strText, 1) = " " Then
- 使用Asc()檢驗ANSI的值
在VBA中，可以使用Chr$()函數(shù)把數(shù)轉(zhuǎn)換成字符，并確定ANSI的值，但是更好的是使用Asc()函數(shù)把字符串轉(zhuǎn)換成數(shù)值，然后確定它的ANSI值。如果需要進行有限次數(shù)的這種檢驗，對程序代碼的效率可能不會產(chǎn)生很大影響，但是，如果需要在多個循環(huán)內(nèi)進行這種檢驗時，這將節(jié)省處理時間并且有助于程序代碼更快地執(zhí)行。 - 使用Len()檢驗空串
盡管有多種方法可檢驗空串，但首選的是使用Len()函數(shù)。為了測試零長度的串，可以選擇把串與””相比較，或者比較串的長度是否為0，但這些方法比用Len()函數(shù)要用更多的執(zhí)行時間。當對字符串應(yīng)用Len()函數(shù)并且函數(shù)返回0值時，說明該字符串是空的或者是零長度的字符串。
并且，因為在If語句內(nèi)非零值被認為是True，所以直接使用Len()函數(shù)而不必與””或0比較，減少了處理時間，因此執(zhí)行更快。 - 有效地使用數(shù)組
用VBA數(shù)組而不是單元格區(qū)域來處理數(shù)據(jù)，即可以先將數(shù)據(jù)寫入到某個數(shù)組，然后用一個語句就可以將數(shù)組中的數(shù)據(jù)傳遞到單元格區(qū)域中。（前文已述）
在創(chuàng)建已知元素的確定數(shù)組時，使用Array函數(shù)對于節(jié)約空間和時間以及寫出更具效率的代碼是非常理想的。例如，Dim Names As Variant Names=Array("Fan","Yang","Wu","Shen")
此外，應(yīng)該盡量使用固定大小的數(shù)組。如果確實選擇使用了動態(tài)數(shù)組，應(yīng)該避免數(shù)組每增加一個元素就改變一次數(shù)組的大小，最好是每次增加一定數(shù)量的元素。
- 使用Excel的內(nèi)置函數(shù)
對于要實現(xiàn)的某一功能，如果有Excel的內(nèi)置函數(shù)能夠?qū)崿F(xiàn)，那么就用Excel的內(nèi)置函數(shù)，不需要另外自定義函數(shù)，因為自定義的函數(shù)總比Excel內(nèi)置的函數(shù)慢。
考慮在VBA代碼中使用工作表函數(shù)
操作單元格區(qū)域的Excel工作表函數(shù)通常比完成同樣任務(wù)的VBA程序更快(但不能確保總是這樣，您可以對它們進行速度測試)
例如，在代碼中使用SUM工作表函數(shù)比用VBA代碼在單元格區(qū)域中循環(huán)并相加值要快得多，以此為例，下面的代碼運行速度相對較慢。Sub AddItSlow() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- '為了進行測試,我們循環(huán)5次 Dim N As Long For N = 1 To 5 '*************************** Dim Cell As Range For Each Cell In Worksheets(2).Range("A1:G200") [a1] = [a1] + Cell.Value Next Cell '*************************** Next N '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
下面的代碼實現(xiàn)相同的功能，但運行得更快(幾乎瞬間完成)。
Sub AddItFaster() '快近600倍 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- '為了進行測試,我們循環(huán)5次 Dim N As Long For N = 1 To 5 '*************************** [a1] = Application.WorksheetFunction. _ Sum(Worksheets(2).Range("A1:G200")) '*************************** Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
產(chǎn)生統(tǒng)計結(jié)果的函數(shù)(例如PRODUCT、COUNT、COUNTA和COUNTIF)是代替運行速度更慢的VBA代碼的很好的選擇，并且，一些工作表函數(shù)(例如MATCH和LOOKUP)能夠?qū)卧駞^(qū)域作為參數(shù)。
不要認為工作表函數(shù)總是更快的
如下例所示，在VBA中沒有Max或Min函數(shù)，但Excel中有該函數(shù)。于是，您能編寫出如下代碼：Sub MaxIt1() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- '為了測試,我們循環(huán)10000次 Dim N As Long For N = 1 To 10000 '*************************** [J1] = Application.Max([J2], [J3]) '*************************** Next N '-------------------------------------- Finish = Timer MsgBox "本次運行時間是" & Finish - Start End Sub
或者，您能在VBA中使用下面的方式實現(xiàn)相同的功能：
Sub MaxIt2() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- '為了測試,我們循環(huán)10000次 Dim N As Long For N = 1 To 10000 '*************************** If [J2] >= [J3] Then [J1] = [J2] Else [J1] = [J3] '*************************** Next N '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
比較上面的兩個程序，可能認為使用工作表函數(shù)會更快，但事實上用VBA代碼可以獲得幾乎相同的速度。因此，在一些大的循環(huán)中，您可以對實現(xiàn)同樣功能的工作表函數(shù)的VBA代碼進行測試。一些內(nèi)置的VBA函數(shù)事實上運行速度也是慢的，因此，在編寫代碼時，在不同方式之間進行速度測試總是值得的。
但是，在代碼中經(jīng)常使用的簡單的函數(shù)，就直接編寫代碼，而不是使用WorksheetFunction對象。 - 使用Range.SpecialCells()來縮小需要處理的單元格數(shù)。
- 只要有可能就使用集合索引值
您能在集合中使用名稱或者數(shù)字來指定某個單一的對象，但使用對象的索引值通常是更快的。如果您使用對象的名字，VBA必須解析名字成為索引值；但如果您使用索引值，就能避免這個額外的步驟。
但另一方面，我們要注意到在集合中通過名稱指定對象有很多優(yōu)點。使用對象名稱能使您的代碼更容易閱讀和調(diào)試。此外，通過名稱指定一個對象比通過索引值更安全，因為當您的代碼運行時該對象的索引值可能變化。
例如，某菜單的索引值表示它在菜單欄中的位置，但是如果在菜單欄中添加了菜單或者刪除了菜單，該菜單的索引值會變化。這樣，您就不應(yīng)該考慮代碼的速度，而應(yīng)保證代碼運行可靠。您使用索引值加快代碼速度之前，應(yīng)該確保該索引值在代碼運行過程中或使用應(yīng)用程序時不會改變。 - 使用完全受限制的對象引用
使用完全受限制的對象引用消除了引用模糊并確保變量有明確的類型。
一個完全受限制的對象引用包括了對象庫名稱，如下代碼所示：Dim wb As Excel.Workbook
如果您使用通用的對象數(shù)據(jù)類型聲明變量和參數(shù)，在運行過程中VBA可能必須對它們的引用進行解析為(某對象的)屬性和方法，這將導致速度變慢。
一個通用對象數(shù)據(jù)類型示例如下：Dim wb As Workbook
- 使用已有的VBA方法
也有一些特定目的的VBA方法，它們提供在單元格區(qū)域執(zhí)行特定操作的一種簡單的方式。例如工作表函數(shù)，這些特定的方法比使用通常的VBA編碼完成相同的任務(wù)要更快。最常用的是”Replace”方法和”Find”方法。
Replace方法:
下面的示例用了一種相當慢的方式代碼改變單元格區(qū)域H1:H20000中每個單元格的值。Sub NowDoThis1() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim Cell As Range For Each Cell In Worksheets(1).Range("H1:H20000").Cells If Cell.Value = 4 Then Cell.Value = 4.5 Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
下面的示例使用Replace方法進行同樣的操作，但運行得更快。
Sub NowDoThis2() '快約兩倍 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Worksheets(1).Range("H1:H20000").Replace "4", "4.5" '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
Find方法:
下面的代碼使用一種相對較慢的方法在單元格區(qū)域I1:I5000中值為4的單元格內(nèi)添加一個藍色的橢圓。Sub FindItSlow() Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim Cell As Range For Each Cell In Worksheets(1).Range("I1:I5000").Cells If Cell.Value = 4 Then With Worksheets(1).Ovals.Add(Cell.Left, _ Cell.Top, Cell.Width, _ Cell.Height) .Interior.Pattern = xlNone .Border.ColorIndex = 5 End With End If Next '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
下面的示例使用了Find方法和FindNext方法執(zhí)行相同的任務(wù)，但運行速度更快。
Sub FindItFaster() '快約25倍 Dim Start As Double, Finish As Double Start = Timer '-------------------------------------- Dim Cell As Range, FirstAddress As String With Worksheets(1).Range("I1:I5000") Set Cell = .Find(4) If Not Cell Is Nothing Then FirstAddress = Cell.Address Do With Worksheets(1).Ovals.Add(Cell.Left, _ Cell.Top, Cell.Width, _ Cell.Height) .Interior.Pattern = xlNone .Border.ColorIndex = 5 End With Set Cell = .FindNext(Cell) Loop Until Cell Is Nothing Or Cell.Address = FirstAddress End If End With '-------------------------------------- Finish = Timer MsgBox "本次運行的時間是" & Finish - Start End Sub
關(guān)于帶有特定目的的VBA方法的更多的信息，您可參見VBA幫助系統(tǒng)相關(guān)主題。
結(jié)語
當然，代碼優(yōu)化可能不是絕對必要的，這依賴于您要做的工作……如果您正好編寫了一個快速且簡短的或者是一次性使用且與速度和/或簡潔要求無關(guān)的代碼，您就不需要優(yōu)化代碼。
但另一方面，如果您處理一個帶有很多數(shù)據(jù)、工作簿、工作表等大的工程，再次檢查您第一次編寫好的代碼，看看是否您的代碼需要優(yōu)化，而這樣做總是值得的。
最終，您將養(yǎng)成編寫代碼的好習慣，將會使您的代碼更簡潔、運行更快速、并且容易為您自已和他人閱讀和調(diào)試。同時，由于您的代碼簡潔，因而輸入更快，工作效率更高。
