ExcelVBA優化代碼儘可能快並且高效率的運行，值得收藏的小結
在本文中，列舉了大量的代碼和示例。但在本文中所討論的代碼並不是本文的中心內容，它們只是用作測試代碼運行速度，以說明本文的相關內容。因此，您可以將本文中的代碼粘貼或輸入到您的工作簿中進行測試，當然您也可以下載本文的附件——優化代碼示例.xls進行調試。注意，您所運行的計算機的環境和配置不同，速度也會有所差異。同樣，您要得出準確的結果，也需要對代碼進行多次的運行和進行最終平均速度的比較。
有必要對代碼進行優化嗎？
這可能不是絕對必要的，但依賴於您要做的工作……如果您正好編寫了一個快速且簡短的或者是一次性使用且與速度和/或簡潔要求無關的代碼，您就不需要優化代碼。
但另一方面，如果您處理一個帶有很多數據、工作簿、工作表等大的工程，再次檢查您第一次編寫好的代碼，看看是否您的代碼需要優化，而這樣做總是值得的。
最終，您將養成編寫代碼的好習慣，將會使您的代碼更簡潔、運行更快速、並且容易為您自已和他人閱讀和調試。同時，由於您的代碼簡潔，因而輸入更快，工作效率更高。
1.減少OLE引用
調用每個VBA方法或屬性都需要一個或多個OLE引用，這樣在代碼中會有多個點運算符，而每次代碼調用都需要對這些點運算符進行解析，這將花費更多的時間。因此，在調用方法或屬性時減少引用長度將是使您的程序運行更快的一種好方法。
例如，下面的代碼包含有三個點運算符，因此Workbooks(1)需要調用三次屬性。
Workbooks(1).Sheets(1).Range("c5").Value = 10
而下面的代碼包含有一個點運算符，這意味著ActiveWindow僅需調用一次屬性。
ActiveWindow.Left = 200
在接下來所講述的內容中有些示例證實了減少點運算符的數量能創建更快運行速度的代碼。
2.使用對象變數
當您一遍又一遍的使用相同對象引用時，您可以將該對象引用設置成一個變數，然後使用該變數代替對象引用。這樣，您在代碼中只需對該對象變數進行引用即可。
例如，下面的示例在每行中調用Workbook對象的Sheets屬性、Range屬性和Value屬性三次，當您循環1000次時，總共要調用屬性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
您能在循環開始前通過設置Workbooks(「Book1」).Sheets(1)作為一個對象變數來優化上面的例子，下面的示例在每行僅調用一個Range屬性，當循環1000次時，總共只調用該屬性2000次。
注意，「Value」是一個預設屬性，通常不需要明確指定它，它將被自動調用。因此，該屬性在下面的代碼中被忽略。然而，就養成良好的編程習慣而言，還是建議您最好寫明該屬性。
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
您可以比較這兩個示例的運行速度，它們都得到同樣的結果，但在我的機子上運行時，第二個示例比第一個快60%。當然，您還能使用With…End With語句獲得相同的結果。
3.使用With…End With語句
您也能不設置明確的對象變數，而是使用With語句減少對象的重複引用。上面的示例也能使用下面的代碼，該代碼僅調用Workbooks屬性和Sheets屬性一次，當循環1000次時，總共調用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
4.使用For Each…Next循環
與使用計數進行循環相比，在遍歷集合或數組時使用For Each…Next循環將更快。在多數情況下，使用For Each…Next循環也更方便，並且使您的宏更簡潔、更容易閱讀和調試。
下面的示例運行很慢，因為在每次循環重複時它設置並調用了行變數.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
Cell.Font.ColorIndex = 5
End If
Next
End With
"--------------------------------------
Finish = Timer
MsgBox "本次運行的時間是" & Finish - Start
End Sub
下面的示例代碼更簡潔，其運行速度大約是上面代碼的2～3倍。因為For Each…Next循環自動記錄行數並定位，而不需要調用變數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
Cell.Font.ColorIndex = 5
End If
Next
End With
"--------------------------------------
Finish = Timer
MsgBox "本次運行的時間是" & Finish - Start
End Sub
5.將屬性和方法放在循環外部
在代碼運行時，獲取變數的值快於獲取屬性的值。因此，如果您的代碼在循環內部獲取屬性的值，您可以在循環外部將該屬性的值先指定給一個變數，然後在循環內部使用此變數代替屬性的值，這樣的代碼將運行得更快。
下面所示的代碼運行較慢，因為在每次重複循環時都必須獲取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
下面的示例與上面所產生的結果相同，但比上面的要更快，因為在循環開始以前我們已經將Sheet的Range屬性的值指定給了單獨的變數MyVar。這樣，代碼將在每次重複循環時利用該變數的值，而不必每次都要調有屬性。
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
如果您在一個循環內部使用多個對象訪問，您也可以使用With…End With將您能夠移動的對象移到循環外部。下面的示例在每次循環重複時都調用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語句將調用Sheets對象移到循環外部，只剩餘調用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
註：您也能通過使用對象變數在循環外部調用該對象。
6.只要有可能就使用集合索引值
您能在集合中使用名稱或者數字來指定某個單一的對象，但使用對象的索引值通常是更快的。如果您使用對象的名字，VBA必須解析名字成為索引值；但如果您使用索引值，就能避免這個額外的步驟。
但另一方面，我們要注意到在集合中通過名稱指定對象有很多優點。使用對象名稱能使您的代碼更容易閱讀和調試。此外，通過名稱指定一個對象比通過索引值更安全，因為當您的代碼運行時該對象的索引值可能變化。
例如，某菜單的索引值表示它在菜單欄中的位置，但是如果在菜單欄中添加了菜單或者刪除了菜單，該菜單的索引值會變化。這樣，您就不應該考慮代碼的速度，而應保證代碼運行可靠。您使用索引值加快代碼速度之前，應該確保該索引值在代碼運行過程中或使用應用程序時不會改變。
7.減少對對象的激活和選擇
在多數情況下，您不必在操作某對象前激活它。如果您是通過宏錄製器來學習VBA編程，您可能習慣於在操作某對象前激活或者選擇該對象。
宏錄製器生成這樣的代碼是因為在您激活窗口和選取它們的內容時，它必須跟蹤您的按鍵並生成與您所操作的步驟相對應的代碼。但是，您通常可以編寫更簡潔且快速的VBA代碼產生同樣的結果，而無須在處理某對象前激活或選擇該對象。。
例如，若要用隨機數字填充Sheet1中單元格F1：F20(使用自動填充AutoFill方法)，使用宏錄製器生成的代碼如下：
Sub DoThisSlow()
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為進行測試,我們將進行100次循環
Dim N As Long
For N = 1 To 100
"***************************
Sheets("Sheet1").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("F1:F20"), Type:=xlFillDefault
Range("F1:F20").Select
"***************************
Next
"--------------------------------------
Finish = Timer
MsgBox "本次運行的時間是" & Finish - Start
End Sub
在上面的代碼中，所有調用Select方法的代碼都不是必須的。您能使用With語句編寫代碼直接操作工作表和單元格，如下代碼所示：
Sub DoThisFaster()
"快約兩倍
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為了進行測試,我們將進行100次循環
Dim N As Long
For N = 1 To 100
"***************************
With Sheets("Sheet1")
.Range("F1").FormulaR1C1 = "=RAND()"
.Range("F1").AutoFill Destination:=.Range("F1:F20"), _
Type:=xlFillDefault
End With
"***************************
Next
"--------------------------------------
Finish = Timer
MsgBox "本次運行的時間是" & Finish - Start
End Sub
當使用宏錄製器時，記住它會精確地記錄你所做的一切，但它不會自動優化代碼。上面所錄製的宏使用AutoFill方法，這不是在某單元格區域填充隨機數的最有效的方法，您能僅用一行代碼實現相同的結果，如下所示：
Sub DoThisMuchFaster()
"比原來的快約四倍
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為了進行測試,我們將進行100次循環
Dim N As Long
For N = 1 To 100
"***************************
Sheets("Sheet1").Range("F1:F20").Formula = "=RAND()"
"***************************
Next
"--------------------------------------
Finish = Timer
MsgBox "本次運行的時間是" & Finish - Start
End Sub
當您優化所錄製的代碼時，您應考慮用這個宏將要去實現什麼功能。您在用戶界面中執行的一些操作將作為一個方法被宏錄製(例如從一個單元格拖拉公式到單元格區域時，會錄製為自動填充AutoFill)，有時您能對這些代碼進行修改，因為在VBA中執行相同的操作有更好的方式。
8.移除不必要的代碼
宏錄製器生成無效代碼的一個原因是它不知道在對話框中您選擇了哪些選項，因此，當您關閉對話框時它將直接記錄所有可用的選項。例如，選擇單元格區域G2:G20，然後在單元格格式對話框中改變字體樣式為粗體，使用宏錄製器生成的代碼如下：
Sub NowThis1()
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為了進行測試,將循環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
您能只用下面的一行代碼為指定的單元格設置字體樣式，不需要選擇單元格區域。
Range("G2:G20").Font.FontStyle = "Bold"
如果您考慮到您想要宏所做的事情(本例中為使字體加粗)，那麼您可以查閱應用到Font對象的屬性和方法列表，您將知道只需使用Bold屬性編寫這個宏代碼以實現所需的功能。代碼如下：
Sub NowThis2()
"快約10倍
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為進行測試,將循環100次
Dim N As Long
For N = 1 To 100
"***************************
Range("G2:G20").Font.Bold = True
"***************************
Next
"--------------------------------------
Finish = Timer
MsgBox "本次運行的時間為" & Finish - Start
End Sub
您也能在用戶界面中通過執行不同的方法來錄製產生結果相同的操作對宏錄製器進行試驗。例如，如果您通過標準工具欄上的粗體按鈕格式化某區域為粗體，那麼宏錄製器將使用Bold屬性。
9.減少」Variant」類型變數的使用
雖然您可能發現在您的代碼中使用Variant(變體)變數是方便的，但是如果您將變數清楚地聲明為特定的數據類型，然後用VBA處理存儲在該變數中的值，要比處理存儲在Variant變數里的值快。
如果執行不涉及分數值的數學運算，那麼在您的代碼中使用Long型變數比使用Variant變數更快。Long型變數也是在For…Next循環中索引值變數類型的最好選擇。
然而，您要注意到，您使用特定類型變數所獲取的速度是以失去靈活性為代價的。例如，當使用特定數據類型時，您可能遭到變數溢出或類型不匹配的情形，而不會像Variant變數會自動進行類型轉換處理。
10.使用特定的對象類型
當您的宏被編譯或者是運行(後台編譯)時，會解析對象及它們的方法和屬性的引用。經過宏編譯解析的引用比在程序運行時必須被解析的引用要更快，因此，您最好跳過後台編譯。
如果您聲明變數和參數為特定的對象類型(比如Range或Worksheet)，VBA在編譯您的程序時將解析引用為這些對象的屬性和方法。(如果要查找指定對象類型列表，請參見」對象瀏覽器」)
使用完全受限制的對象引用
使用完全受限制的對象引用消除了引用模糊並確保變數有明確的類型。
一個完全受限制的對象引用包括了對象庫名稱，如下代碼所示：
Dim wb As Excel.Workbook
如果您使用通用的對象數據類型聲明變數和參數，在運行過程中VBA可能必須對它們的引用進行解析為(某對象的)屬性和方法，這將導致速度變慢。
一個通用對象數據類型示例如下：
Dim wb As Workbook
11.使用常量
變數會發生變化，因此VBA在程序運行時必須獲取當前變數的值。
在應用程序中使用常量會使程序運行更快。在編譯您的代碼時，常量僅計算一次並被存儲。
常量也能使您的宏程序更易閱讀和維護。如果在您的程序中有一些不變的字元串或數值的話，您可以聲明它們作為常量。
12.關閉屏幕刷新
使工作簿的顯示發生變化的程序——例如，在很大的單元格區域改變每個單元格顏色的程序，或者是創建很多圖形對象——在您關閉屏幕刷新後都將運行得更快。
這意味著您不能觀察到程序的運行過程(當您重新開啟屏幕更新時將立即顯示變化)，但程序將運行更快。當您編寫和調試程序時，您可能想讓屏幕更新開啟，然後在您運行程序前關閉屏幕更新。
為了關閉屏幕更新，設置ScreenUpdating屬性的值為False，如下代碼所示：
Application.ScreenUpdating = False
記得當您的宏運行結束時將ScreenUpdating屬性的值重新設置為True。
技巧：您有時能通過不激活您所改變的對象來達到同樣的效果。例如，如果您不需要首先激活文檔而在工作表中創建圖形對象，您不需要關閉屏幕更新設置，因為這些變化總是不可見的。
13.使用已有的VBA方法
也有一些特定目的的VBA方法，它們提供在單元格區域執行特定操作的一種簡單的方式。例如工作表函數，這些特定的方法比使用通常的VBA編碼完成相同的任務要更快。最常用的是」Replace」方法和」Find」方法。
Replace方法:
下面的示例用了一種相當慢的方式代碼改變單元格區域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方法:
下面的代碼使用一種相對較慢的方法在單元格區域I1:I5000中值為4的單元格內添加一個藍色的橢圓。
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方法執行相同的任務，但運行速度更快。
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
關於帶有特定目的的VBA方法的更多的信息，您可參見VBA幫助系統相關主題。
14.考慮在VBA代碼中使用工作表函數
操作單元格區域的Excel工作表函數通常比完成同樣任務的VBA程序更快(但不能確保總是這樣，您可以對它們進行速度測試)
例如，在代碼中使用SUM工作表函數比用VBA代碼在單元格區域中循環並相加值要快得多，以此為例，下面的代碼運行速度相對較慢。
Sub AddItSlow()
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為了進行測試,我們循環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
下面的代碼實現相同的功能，但運行得更快(幾乎瞬間完成)。
Sub AddItFaster()
"快近600倍
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為了進行測試,我們循環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
產生統計結果的函數(例如PRODUCT、COUNT、COUNTA和COUNTIF)是代替運行速度更慢的VBA代碼的很好的選擇，並且，一些工作表函數(例如MATCH和LOOKUP)能夠將單元格區域作為參數。
15.不要認為工作表函數總是更快的
如下例所示，在VBA中沒有Max或Min函數，但Excel中有該函數。於是，您能編寫出如下代碼：
Sub MaxIt1()
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為了測試,我們循環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中使用下面的方式實現相同的功能：
Sub MaxIt2()
Dim Start As Double, Finish As Double
Start = Timer
"--------------------------------------
"為了測試,我們循環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
比較上面的兩個程序，可能認為使用工作表函數會更快，但事實上用VBA代碼可以獲得幾乎相同的速度。因此，在一些大的循環中，您可以對實現同樣功能的工作表函數的VBA代碼進行測試。一些內置的VBA函數事實上運行速度也是慢的，因此，在編寫代碼時，在不同方式之間進行速度測試總是值得的。
小夥伴，大家一起學習Excel VBA知識，一起進步。同時歡迎大家幫忙轉發並關注，謝謝大家的支持！
TAG:我愛excel |