10 種加速宏的方法

避免過度引用

呼叫物件的方法或屬性時，需要經過OLE元件的IDispatch介面。對這些OLE元件的呼叫需要時間，因此減少對OLE元件的參考次數可以提高巨集程式碼的速度。

對於物件屬性或方法的調用， 一般採用Object.Method的表示方法 ，即“.”。符號用於呼叫屬性和方法。

因此，可以根據符號「.」的個數來判斷方法或屬性的呼叫次數。越少的“.” 符號，程式碼運行速度越快。

例如下面的語句包含3個符號“.”。

ThisWorkbook.Sheet1.Range("A1").Value = 100

下面的語句只有一個符號「.」。

Activewindow.Top = 100

這裡有一些減少符號“.”數量的技巧。跑得更快。

首先，當需要重複引用同一個物件時，可以將該物件設定為變數以減少呼叫次數。例如，以下程式碼每行需要兩次呼叫。

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

因為 Sheets(“Sheet1”) 物件需要重複引用，所以可以先將其設為一個變數 sht  ，這樣每個程式碼只需要呼叫一次。

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

其次，如果不想宣告臨時變數sht，也可以使用  前面提到的With語句。如下例所示：

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

第三， 當循環較多時，盡量將屬性和方法保留在循環之外。 在循環中重複使用同一物件的屬性值時，可以先將屬性值賦給循環外的指定變量，然後再在循環中使用該變量，這樣可以達到更快的速度。如下例所示：

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

此範例中的每個迴圈都會取得單元格 Cells(1,2) 的 Value 屬性。如果在迴圈開始之前將 Cells(1.2) 的 Value 屬性指派給變量，則運行速度會更快。如下例所示：

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

上面的程式碼  每次迴圈時都會呼叫ThisWorkbook.Sheets("Sheet1") 。您可以使用 With語句將對ThisWorkbook.Sheets("Sheet1") 的 呼叫移至  循環之外，從而更快地完成此操作。如下例所示：

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

避免使用變體類型

初學者通常更喜歡使用 Variant 類型變量，它的優點是不太複雜，因為對於 Integer 或 Long 資料類型來說，任何類型的資料都可以使用，不會出現記憶體溢出的問題。但是，Variant 類型資料比其他指定類型需要更多的額外記憶體空間（Integer 資料為2 個位元組，Long 資料為4 個位元組，Variant 資料為16 個位元組），VBA 處理Variant 類型資料比其他指定類型需要更多時間資料的。如下例所示。

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

上面的程式碼中，第8到13行對Variant變數做了100萬次加減乘除運算，第17到22行對Integer變數做了100萬次加減乘除運算。在我的電腦上，Variant變數的操作大約花費了 0.09375 秒，而Integer變數的操作大約花費了 0.03125 秒。結果可能因計算機而異，但 Variant 變數明顯慢於 Integer 變數。

因此， 建議在可以明確使用指定資料類型時避免使用 Variant 變數。