羅傑小沙

Excel VBA程式設計中提高效率的方法

使用VBA代碼，能做很多函數處理不到的，提高Excel的工作效率。但是VBA代碼運行也需要時間，提高代碼執行效率也非常重要。當然，效率的提高應該建立在正確的解決方案和正確的演算法的基礎上，前者保證了結果的正確性，後者保證了效率。通過改進演算法和思路得到的運行效率的提高是以下優化方法的十倍百倍，因此，優化首先應該考慮的演算法。

要特別指出，效率的優化必須是針對關鍵代碼的優化，對於一些在程式執行過程中，只執行很少次數的代碼，沒有必要犧牲可讀性而進行優化。在此基礎上，可以通過注意以下一些問題，提高程式的運行效率。

1.   儘量使用Excel的內置函數

應該儘量使用Excel的內置函數，使用Excel內置函數不僅可以提高運行效率，而且可以節省代碼數量。對於Excel內置函數可以通過以下方式訪問：

Application.函數名()

Application.WorksheetFunction.函數名(myRange)

例如以下求平均值的例子，使用的VBA代碼如下：

For Each c In Worksheet(1).Range("A1:A1000")

TotalValue = TotalValue ＋ c.Value

Next

AverageValue = TotalValue / Worksheet(1).Range("A1:A1000").Rows.Count

而下面代碼程式比上面例子快得多：

AverageValue = Application.Average(Worksheets(1).Range(“A1:A1000”))

其它函數如Count，Counta，Countif，Match，Lookup等等，都能代替相同功能的VBA程式碼，提高程式的運行速度。

2.   儘量減少使用物件引用

在VBA代碼中，應該儘量減少使用物件引用，尤其在迴圈中。每一個Excel物件的屬性、方法的調用都需要通過COM介面的一個或多個調用，這些COM調用都是比較費時的，因此，減少使用物件引用能加快VBA代碼的運行。可以通過以下途徑改進效率：

（1）使用With語句

例如以下語句，可以通過替換為With語句，提高運行效率：

ActiveSheet.Range("A1:A1000").Font.Name = “Arial”

ActiveSheet.Range("A1:A1000").Font.FontStyle = "Bold"

對應的With語句：

With ActiveSheet.Range("A1:A1000").Font

.Name = "Arial"

.FontStyle = "Bold"

End With

（2）使用物件變數

如果一個物件引用被多次使用，則可以通過定義一個區域變數，將此物件用Set 設置為物件變數，以減少對物件的訪問。如：

ActiveSheet.Range("A1").Value = 100

ActiveSheet.Range("A2").Value = 200

則以下代碼要比上面的要快：

Dim objSheet As Object

Set objSheet = ActiveSheet

objSheet.Range("A1").Value = 100

objSheet.Range("A2").Value = 200

（3）減少迴圈中的物件訪問

例如以下迴圈，可以通過設置區域變數或者使用With語句來提高效率。

For k = 1 To 1000

ActiveSheet.Range("A1000").Cells(1,k).Value = k

Next k

則以下代碼比上面的要快（使用With語句）：

With ActiveSheet.Range("A1000")

For k = 1 to 1000

 .Cells(1,k).Value = k

Next k

End With

VBA程式設計中對Excel物件的引用是不可避免的，而且是經常性的工作，例如對Range物件的引用，但同時這種引用又是非常耗時的，例如與使用陣列相比較，使用Range物件要慢1000倍到10000倍，因此，一定要避免頻繁引用Range物件（例如在一些資訊查詢、矩陣運算等時候），必要的時候，可以通過陣列等方式來替代，在運算開始前將Range的資料讀入陣列，運算完成後再寫入Range。

3.   減少物件的啟動和選擇

通過錄製巨集得到的VBA代碼充滿了物件的啟動和選擇，例如Workbooks(XXX).Activate、Sheets(XXX).Select、Range(XXX).Select等，但事實上大多數情況下這些操作不是必需的，因此，應該儘量避免這樣的代碼。例如：

Sheets("Sheet3").Select

Range("A1").Value = 100

Range("A2").Value = 200

可改為：

With Sheets("Sheet3")

.Range("A1").Value = 100

 .Range("A2").Value = 200

End With

4.   關閉螢幕更新

避免不斷的刷新螢幕，在向工作薄寫資料或者繪圖時，鎖定螢幕刷新；

如果你的VBA程式需要經常更新螢幕工作表的內容，則關閉螢幕更新是提高VBA程式運行速度的最有效的方法，可以縮短執行時間2/3左右。關閉螢幕更新的方法：

Application.ScreenUpdate = False

請不要忘記VBA程式運行結束時再將該值設回來：

Application.ScreenUpdate = True

5.   提高關鍵代碼的效率

最後要特別指出，不要做不必要的優化。雖然本書給出了很多不同方法執行效率的差異，但千萬不要因為追求效率而損失了代碼的可讀性、清晰性。

效率的優化必須是針對關鍵代碼的優化，對於一些在程式執行過程中，只執行很少次數的代碼，沒有必要犧牲可讀性而進行優化。

對於代碼執行效率，千萬不要人云亦云，必要時候，自己動手測試一下，結果往往會出乎意料。

6.   代碼執行時間的測算

VBA和VB中，沒有專門的代碼執行事件測算工具和方法，筆者一般是使用Timer函數，其返回值是一個Single類型的數值，代表從午夜開始到現在經過的秒數，此數值包括小數部分，但精確程度在Windows NT，2000和XP下大概接近10毫秒。如果要測試一段代碼的執行速度，可以使用如下方法：

Sub MeasureTime()

Dim Time1 As Single, Time2 As Single

Dim TotalTime As Single

Dim Times As Long

 Dim i As Long

Times = 10000

Time1 = Timer

For i = 1 To Times Step 1

Mytest1

Next i

Time2 = Timer 

TotalTime = (Time2 - Time1) * 1000

MsgBox "執行時間: " & TotalTime & " 毫秒（次數：" & Times & ")"         

End Sub

Sub Mytest1()

 Dim i As Long

Dim s As String

   i = Rnd

   s = Format(i, "#.00")

End Sub

過程MeasureTime可以測試一個過程的執行速度，因為一般一個過程執行會很快，所以使用迴圈，執行n次，通過迴圈前的時間和迴圈後的時間，計算總共執行時間。

使用這個方法，就可以做一些測試，看哪些方法執行效率更高。另外，由於Windows的多工特定，測試時最好關閉其他無關程式，以獲得較準確的測試結果。

資料來源：http://www.excel-cn.com/tip/328-cn.html