很多Excel VBA文章和圖書都介紹過如何優化VBA代碼，使代碼運行得更快。下面搜集了一些使Excel VBA代碼運行更快的技術和技巧，基本上都是實踐經驗的總結。如果您還有其它優化Excel VBA代碼的方法，可以在本文后留言或給出鏈接，與大家分享。
對于應用程序屬性，在代碼運行時關閉除必需屬性以外的其它所有屬性
在代碼運行時關閉不需要的Excel功能。其原因是，如果通過VBA更新不同的單元格區域，或者從不同的單元格區域復制/粘貼來創建匯總表，則不希望Excel浪費時間和資源來重新計算公式、顯示粘貼進度或者重繪網格，尤其在每次單獨的操作后（更有甚者，如果代碼使用了循環，則每次單獨操作后Excel都會在后臺運行這些功能）。只需要在代碼執行結束時進行一次重新計算和重繪就足以使工作簿更新。
下面的代碼將幫助您提高代碼的執行速度。
（1）放置在主代碼前的一段代碼，獲取Excel當前的屬性狀態，然后將其關閉

'獲得當前的Excel設置狀態,將其放置在代碼的開頭
screenUpdateState = application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks '注:這是工作表級的設置
'關閉一些Excel功能使代碼運行更快
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False '注:這是工作表級的設置

（2）放置在主代碼結束后的一段代碼，用來將Excel恢復到代碼運行前的設置

'代碼運行后,恢復Excel原來的狀態;將下面的代碼放在代碼的末尾
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState '注:這是工作表級的設置

下面簡要解釋這些設置：
Application.ScreenUpdating：將該屬性設置為False，告訴Excel不要重繪屏幕。其優點是不需要Excel花費資源來繪制屏幕，因而其改變會更快而不致讓用戶察覺其變化。因為如此頻繁地繪制屏幕需要大量的資源，所以關閉繪制屏幕直到代碼執行結束。在代碼結束前，確保重新開啟了該屬性。
Application.DisplayStatusBar：將該屬性設置為False，告訴Excel停止顯示狀態欄。例如，如果使用VBA復制/粘貼單元格，當粘貼執行時Excel將在狀態欄中顯示操作的進度。關閉屏幕更新不會關閉狀態欄顯示，因此，如果需要的話，可以禁用屏幕更新而仍然可以通過狀態欄給用戶提供反饋。記住，如果將該屬性設置為False，在代碼結束前應該將其設置為True。
Application.Calculation：該屬性允許編程設置Excel的計算模式?！笆止さ摹保▁lCalculationManual）模式意味著Excel等待用戶（或代碼）來觸發計算；默認為“自動的”（xlCalculationAutomatic）模式，意味著由Excel來決定何時重新計算工作簿（例如，當在工作表中輸入新公式時）。由于重新計算工作簿將花費時間且浪費資源，因此可能不希望每次改變單元格值時Excel都觸發重新計算。當代碼執行時關閉重新計算，在代碼結束前再設置回重新計算模式。
Application.EnableEvents：將該屬性設置為False，告訴Excel不要觸發事件。你可能不希望Excel為每個正在通過代碼發生改變的單元格觸發事件，關閉事件將加速VBA代碼的執行。
ActiveSheet.DisplayPageBreaks：當在較新版本的Excel中運行VBA時，則可能比在早期版本的Excel中需要更長的時間完成。例如，需要幾秒鐘在早期版本的Excel中完成的宏可能需要幾分鐘才能在更高版本的Excel中完成。或者，第二次運行一個宏可能比第一次運行需要的時間更長。這是由于VBA宏修改了多行或列的屬性，或者必須強制執行計算Excel分頁符。如果宏設置了任何PageSetup屬性或者手動設置了PageSetup屬性，接著運行較大區域的行或列屬性設置時會出現這樣的問題。您可以將該屬性設置為False來提高代碼的運行速度。當然，在代碼運行結束前，應將該屬性恢復為原設置。

在單個操作中讀/寫大塊的單元格區域
本技巧用于優化在Excel和代碼之間轉換數據的次數。使用數組變量存儲所需要的值并執行取值或賦值操作，而不是一次遍歷單個單元格并獲取或設置單個值。
例如，下面的代碼在單元格區域A1：C10000中放置隨機數。
代碼段一：運行速度較慢的代碼

Sub testSlow()
Dim DataRange As Range
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
Set DataRange = Range("A1:C10000")
 
For Irow = 1 To 10000
For Icol = 1 To 3
MyVar = DataRange(Irow, Icol) '從Excel單元格中讀取值30K次
If MyVar > 0 Then
MyVar = MyVar * MyVar ' 改變值
DataRange(Irow, Icol) = MyVar '將值寫入Excel單元格中30000次
End If
Next Icol
Next Irow
End Sub

代碼段二：運行速度更快的代碼

Sub testFast()
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
DataRange = Range("A1:C10000").Value '一次從Excel單元格中讀取所有的值,將其放入數組

For Irow = 1 To 10000
For Icol = 1 To 3
MyVar = DataRange(Irow, Icol)
If MyVar > 0 Then
MyVar = MyVar * MyVar ' 改變數組中的值
DataRange(Irow, Icol) = MyVar
End If
Next Icol
Next Irow
Range("A1:C10000").Value = DataRange '一次將所有結果寫回單元格
End Sub

避免選取/激活對象
使用選取的方法更新單元格區域是最慢的。在試驗了使用Range對象、使用Variant類型和使用Select方法對一個大的單元格區域讀寫數據的操作后，Select方法是最慢的。
再來看一個例子：在工作表中有40個形狀，在每個形狀中寫入“Hello”。使用Select方法的代碼為：

Sub testSlow()
Dim i As Integer
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Select
Selection.Text = "Hello"
Next i
End Sub

運行速度更快的方法是完全避免使用選取并直接引用形狀：

Sub testFast()
Dim i As Integer
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
Next i
End Sub

在使用宏錄制器時，所生成的程序代碼在應用任何方法或屬性之前都會激活或者選擇對象。但是，并不是在所有的情況下都需要這樣做。所以，在您編寫VBA程序代碼時，不需要在對對象執行任何任務之前都激活或者選擇每個對象。
例如，在Excel中，我們如果要使第一行變成粗體就必須先選項中它。但在VBA中(除在圖表操作時需要選中圖表對象外)，很少需要這樣做，即VBA可以在不選中第一行的情況下，將它變成粗體。
宏錄制器的代碼：

Rows("1:1").Select
Selection.Font.Bold = True

改編后的代碼為：

Row(“1:1”).Font.Bold=True

這樣做還可以使程序代碼更簡潔，并且程序可以運行得更快。

工作簿設計
好的工作簿設計和數據組織有助于編寫運行良好的代碼。良好設計的工作簿，其執行效率和維護量將大大優化?？梢哉f，工作簿設計是從大的宏觀方面進行優化，而對代碼的優化只是一些微觀的細節上的優化。

其他

盡量簡化代碼
通過簡化代碼，可以提高程序的性能。您可以將通用過程編寫為子過程來調用。例如，假設有一個應用程序需要在不同的地方實現查找一定范圍內的某個特殊條目，在一個沒有簡化代碼的應用程序中，不同的過程可能需要應用各自的算法以實現在某個范圍內查找某一條目，修改每個過程使其采用一個更有效的算法并不是一件很容易的事。而一個簡化的程序則只有一個查找算法，即將該查找算法編寫成通用的子程序，需要查找某個范圍的過程都調用該子程序，通過在查找方法的子程序中優化查找算法，使得調用該方法的所有過程都享受性能提高所帶來的好處。
另外，刪除所有無關的代碼，這在所錄制宏中表現得尤為明顯。在錄制宏時，經常會產生一些與所實現的功能無關的代碼，您可以將這些代碼刪除，以使得代碼得以簡化。
宏錄制器生成無效代碼的一個原因是它不知道在對話框中您選擇了哪些選項，因此，當您關閉對話框時它將直接記錄所有可用的選項。例如，選擇單元格區域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屬性。
下面將要講到的設置對象變量代替長對象引用，使用With…End With語句、執行For Each…Next循環語句，根據程序環境盡量減少OLE引用，等等，均是簡化代碼的好方法。

強制聲明變量
在VBE編輯器中的菜單“工具——選項”對話框中“編輯器”選項卡中，您應該始終保持“要求變量聲明”復選框被選中，這樣將在模塊代碼頂部出現Option Explicit語句，要求您在編寫代碼時對所有出現的變量均進行聲明，這樣，在使用變量時減少內存需求并加速性能。
(1)要節省內存資源，必須始終用特定的數據類型聲明所有變量。如果不使用特定的數據類型聲明變量，VBA會創建Variant類型的變量，這將比任何其他數據類型要求更多的內存。
(2)清楚每種數據類型需要多少內存以及它可以存儲的值的范圍。除使用較小的數據類型會導致隱性轉換的情況外，應始終使用盡可能小的數據類型。例如，因為Integer類型的變量將被轉換成Long類型的變量，應該將那些存儲整型值的變量聲明為Long類型，而不是Integer類型。
(3)除非確實需要，應避免使用浮點數據類型。盡管Currency數據類型更大，但它比 Single 數據類型快，因為Currency數據類型不使用浮點處理器。
(4)如果在一個過程中多次引用一個對象，可以創建對象變量，并將對給對象的引用指派給它。因為對象變量存儲對象在內存中的位置，VBA將不必再次查找其位置。
(5)將對象變量聲明為特定的類型(不是Object類型)，以便利用早期綁定。
(6) 減少”Variant”類型變量的使用
雖然您可能發現在您的代碼中使用Variant(變體)變量是方便的，但是如果您將變量清楚地聲明為特定的數據類型，然后用VBA處理存儲在該變量中的值，要比處理存儲在Variant變量里的值快。
如果執行不涉及分數值的數學運算，那么在您的代碼中使用Long型變量比使用Variant變量更快。Long型變量也是在For…Next循環中索引值變量類型的最好選擇。
然而，您要注意到，您使用特定類型變量所獲取的速度是以失去靈活性為代價的。例如，當使用特定數據類型時，您可能遭到變量溢出或類型不匹配的情形，而不會像Variant變量會自動進行類型轉換處理。
(7) 聲明時指定特定的對象類型
當您的宏被編譯或者是運行(后臺編譯)時，會解析對象及它們的方法和屬性的引用。經過宏編譯解析的引用比在程序運行時必須被解析的引用要更快，因此，您最好跳過后臺編譯。
如果您聲明變量和參數為特定的對象類型(比如Range或Worksheet)，VBA在編譯您的程序時將解析引用為這些對象的屬性和方法。(如果要查找指定對象類型列表，請參見”對象瀏覽器”)
減少變量的作用范圍并及時釋放變量
主要是對象變量，在其使用完后，及時釋放。例如，

Dim TempObj As AnyObject,AnObj As AnyObject
Set TempObj=New AnyObject
Set AnObj=TempObj
Set TempObj=Nothing ‘釋放對象變量

使用常量
變量會發生變化，因此VBA在程序運行時必須獲取當前變量的值。
在應用程序中使用常量會使程序運行更快。在編譯您的代碼時，常量僅計算一次并被存儲。
常量也能使您的宏程序更易閱讀和維護。如果在您的程序中有一些不變的字符串或數值的話，您可以聲明它們作為常量。
盡可能使用早期綁定
綁定是指將程序調用與實際代碼相匹配。為了實現早期綁定，先應創建對對象庫的引用。早期綁定可以在代碼中使用定義在對象庫中的常量，可以自動列出對象的方法和屬性，但早期綁定只有在所控制的對象擁有獨立的類型庫或對象庫文件才適用且還需要已安裝了特定的庫。而后期綁定則只是在運行時才知道對象的類型并對對象進行引用，因此不具備上述特點。
使用早期綁定創建對象通常更有效率，使代碼能獲得更好的性能。因為對象的早期綁定引用在編譯時可以通過VBE的解析，而不是通過運行時模塊解析，因此早期綁定的性能要好得多。雖然在程序設計時不可能總是使用早期綁定，但應該盡可能使用它。
使用For Each…Next循環
可以使用For Each…Next循環來保證程序代碼更快地執行。在使用For Each…Next循環時，對于存儲在集合或數組中的每個對象執行一組語句，程序更簡潔，也更容易閱讀、調試和維護。當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 > 0 Then
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 > 0 Then
Cell.Font.ColorIndex = 5
End If
Next
End With
'--------------------------------------
Finish = Timer
MsgBox "本次運行的時間是" & Finish - Start
End Sub

在執行循環時考慮如何能夠盡可能地節省資源
(1)分析循環以查看是否正在不必要地執行一些消耗內存的重復操作。例如，是否可以在循環外(而不是在循環中)設置某些變量？每次都通過循環執行的轉換過程是否可以在循環之外執行？
(2)考慮是否必須在滿足特定的條件時才執行循環。如果是，也許可以更早地退出循環。例如，假設正在對一個不應該包含數字字符的字符串進行數據驗證。如果循環要檢查字符串中的每個字符以確定其中是否包含數字字符，那么您可以在找到第一個數字字符時立即退出循環。
(3)如果必須在循環中引用數組的元素，可以創建一個臨時變量存儲該元素的值，而不是引用數組中的值。從數組中檢索值比從相同類型的變量讀取值要慢。
(4) 將屬性和方法放在循環外部
在代碼運行時，獲取變量的值快于獲取屬性的值。因此，如果您的代碼在循環內部獲取屬性的值，您可以在循環外部將該屬性的值先指定給一個變量，然后在循環內部使用此變量代替屬性的值，這樣的代碼將運行得更快。
下面所示的代碼運行較慢，因為在每次重復循環時都必須獲取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

注：您也能通過使用對象變量在循環外部調用該對象。

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

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語句在同一個單元格區域中執行多個操作。

盡量減少OLE引用
調用每個VBA方法或屬性都需要一個或多個OLE引用，這樣在代碼中會有多個點運算符，而每次代碼調用都需要對這些點運算符進行解析，這將花費更多的時間。因此，在調用方法或屬性時減少引用長度將是使您的程序運行更快的一種好方法。
可以通過盡量減少在VBA程序代碼中使用OLE(對象鏈接與嵌入自動識別)引用來優化程序代碼。VBA語句中所調用的方法和屬性越多，執行語句所用的時間就越多。例如下面的兩個語句：
語句1：

Workbooks(1).Sheets(1).Range("A1").value="10"

語句2：

ActiveWindow.Left=200

執行時，語句2比語句1快。
同樣，上面所講的對重復使用的對象引用指定一個變量，通過調用變量從而保證避免多次進行對象引用。