在巨集執行期間關閉屬性

在任何程式語言中，最好的做法是**避免過早優化。**但是，如果測試顯示你的程式碼執行速度太慢，則可以通過在執行時關閉某些應用程式的屬性來獲得一些速度。將此程式碼新增到標準模組：

Public Sub SpeedUp( _
    SpeedUpOn As Boolean, _
    Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
    With Application
        If SpeedUpOn Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayStatusBar = False 'in case you are not showing any messages
            ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
        Else
            .ScreenUpdating = True
            .Calculation = xlCalc
            .EnableEvents = True
            .DisplayStatusBar = True
            ActiveSheet.DisplayPageBreaks = True
        End If
    End With
End Sub

有關 Office 部落格的更多資訊 - Excel VBA 效能編碼最佳實踐

只需在巨集的開頭和結尾呼叫它：

Public Sub SomeMacro
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    SpeedUp True

    'code here ...

    'by giving the second argument the initial "calculation" state is restored
    'otherwise it is set to 'xlCalculationAutomatic'
    SpeedUp False, xlCalc
End Sub

雖然這些可以在很大程度上被視為常規 Public Sub 程式的增強，但對於改變一個或多個工作表上的值的 Worksheet_Change 和 Workbook_SheetChange 私有事件巨集，禁止使用 Application.EnableEvents = False 禁用事件處理。無法禁用事件觸發器將導致事件巨集在值更改時以遞迴方式執行，並且可能導致凍結工作簿。記得在離開事件巨集之前重新開啟事件，可能是通過安全退出錯誤處理程式。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        
        'code that may change a value on the worksheet goes here
        
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

需要注意的是： 禁用這些設定會縮短執行時間，但它們可能會使除錯應用程式變得更加困難。如果你的程式碼無法正常執行，請註釋掉 SpeedUp True 呼叫，直到找出問題為止。

如果你正在寫入工作表中的單元格，然後從工作表函式中讀回計算結果，則這一點尤為重要，因為 xlCalculationManual 會阻止計算工作簿。要在不禁用 SpeedUp 的情況下解決此問題，你可能需要包含 Application.Calculate 以在特定點執行計算。

注意： 由於這些是 Application 本身的屬性，因此你需要確保在巨集退出之前再次啟用它們。這使得使用錯誤處理程式並避免多個退出點（即 End 或 Unload Me）尤為重要。

有錯誤處理：

Public Sub SomeMacro()
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    On Error GoTo Handler
    SpeedUp True
    
    'code here ...
    i = 1 / 0
CleanExit:
    SpeedUp False, xlCalc
    Exit Sub
Handler:
    'handle error
    Resume CleanExit
End Sub