Excel VBA效能：3種計算優化避免程式跑很慢
Excel VBA效能提升關鍵在於掌握公式計算模式，本文整理3種設定方便，教你如何優化程式碼，一次搞懂設定原理與設計重點，避免巨集因為自動重算跑很慢。
巨集執行緩慢，最常見的原因，就是公式在每一步都自動重算。Application.Calculation 正是控制計算模式的核心屬性。善用它，巨集速度可以大幅提升，是每位VBA使用者一定要掌握的基本功，也是效能優化真正的第一步。
💡 為什麼 VBA 跑這麼慢？
在預設狀況下，VBA 每修改一個儲存格，Excel 就會重新計算整份檔案的公式。如果你用迴圈寫入 5 萬筆資料，Excel 就被迫重算 5 萬次！只要在程式開頭將計算模式切換為「手動」，結束時再「還原」，速度即可提升數倍至數十倍。
📌 三種計算模式比較
變更儲存格立刻全表重算。適合日常一般手動操作，但不適合大量寫入的巨集。
Excel 完全不主動計算，節省大量資源。適合 VBA 批次寫入與大型報表重整。
除「資料表」外皆自動計算。通常用於含有龐大模擬資料表的財務模型中。
很多新手會直接把還原寫死成 Automatic，若使用者本來就習慣手動模式，這會竄改他的個人設定。此外，若忘記寫錯誤處理 (On Error)，程式中途崩潰會讓 Excel 永遠卡在手動模式。正確心法：先用變數記住原始狀態，再完美還原！
🚀 終極武器：VBA 加速「三件套」
實務上，我們會將「停止重算 + 關閉螢幕刷新 + 暫停事件觸發」三個屬性一起使用，達到極致加速。請直接複製以下範本：
Sub 巨集加速標準範本()
' 1. 記錄使用者原始設定
Dim calcState As XlCalculation
calcState = Application.Calculation
' 2. 設置錯誤處理，確保出錯也能還原
On Error GoTo CleanUp
' 3. 關閉三件套 (加速開始)
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
' ==============================
' 你的主要程式碼寫在這裡！
' ==============================
CleanUp:
' 4. 還原三件套 (順序建議與關閉時相反)
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = calcState
' 5. 錯誤提示 (選用)
If Err.Number <> 0 Then
MsgBox "發生錯誤：" & Err.Description, vbCritical
End If
End Sub一、Application.Calculation的定義與功能
Application.Calculation 是Excel VBA中的一個屬性，用來控制整個Excel應用程式重新計算公式的方式。它屬於 Application 物件層級，也就是說，一旦設定，效果會套用到所有目前開啟的活頁簿，而不是只針對單一工作表或儲存格。
' 讀取目前的計算模式 Dim mode As XlCalculation mode = Application.Calculation ' 設定計算模式 Application.Calculation = xlCalculationManual
第一段程式是讀取目前的設定值並存入變數，第二段則是直接指定模式。這個屬性接受的是 XlCalculation 列舉型別的常數，而不是一般的數字或字串。
二、為什麼VBA需要控制計算模式？
Excel預設是「自動計算」。這代表每當VBA修改一個儲存格的值，Excel就會立刻檢查整本活頁簿，重新計算所有相關公式。手動操作時影響不大，但在VBA巨集中，程式可能在幾秒內連續修改數千個儲存格，Excel就會被迫進行數千次重算。
' 問題示範：自動計算下的迴圈
For i = 1 To 50000
Cells(i, 1).Value = i ' 每寫一筆，Excel就重算一次
Next i
這段程式在自動計算模式下執行5萬次迴圈，就等於觸發了5萬次全表重算。如果活頁簿中還有大量VLOOKUP、SUMIFS、INDEX/MATCH等複雜公式，執行時間會被大幅拉長，畫面甚至可能凍結。
三、三種計算模式說明
Application.Calculation 接受 XlCalculation 列舉中的三個常數，分別對應不同的計算行為。
' 三種模式的設定語法 Application.Calculation = xlCalculationAutomatic ' 自動 Application.Calculation = xlCalculationManual ' 手動 Application.Calculation = xlCalculationSemiautomatic ' 半自動
xlCalculationAutomatic（自動計算）：預設模式，只要儲存格有變更，就會立刻觸發全表重算。適合日常操作，但不適合大量寫入的巨集。
xlCalculationManual（手動計算）：VBA效能優化最常用的模式。Excel不會主動計算，只有使用者按F9或VBA呼叫 Calculate 時才更新，能大幅縮短巨集執行時間。
xlCalculationSemiautomatic（半自動計算）：除了「資料表（Data Table）」之外，其餘公式仍自動計算。通常用於財務模型中包含龐大模擬資料表，但又不希望其他公式停止更新的情境。
四、手動與自動模式的實際效能差異
用具體情境來看：活頁簿有5萬筆資料、1000個公式，還有大量VLOOKUP與SUMIFS交叉計算。
' 自動計算模式（未優化）
For i = 1 To 50000
Cells(i, 1).Value = i
Next i
' 結果：觸發5萬次重算，速度極慢
' 手動計算模式（優化後）
Application.Calculation = xlCalculationManual
For i = 1 To 50000
Cells(i, 1).Value = i
Next i
Application.Calculate ' 最後統一重算一次
Application.Calculation = xlCalculationAutomatic
' 結果：只重算1次，速度差距可達數倍至數十倍
兩段程式邏輯完全相同，差別只在於是否控制計算模式。在公式複雜度高的情境下，前後速度差距會非常明顯，這也是為什麼這個屬性被視為VBA效能優化最基本的一步。
五、Application.Calculation的標準使用範本
最安全的寫法，不是「執行完固定改回Automatic」，而是「先記錄使用者原本的設定，最後再還原」。因為使用者的Excel本來就有可能是手動模式，若程式直接寫死改回Automatic，反而改動了對方原本的環境。
Sub 計算模式標準範本()
' 1. 記錄使用者目前的計算模式
Dim calcState As XlCalculation
calcState = Application.Calculation
' 2. 加入錯誤處理，確保異常時也能還原設定
On Error GoTo CleanUp
' 3. 切換為手動，開始執行主要程式
Application.Calculation = xlCalculationManual
' ── 主要程式碼放這裡 ──
CleanUp:
' 4. 無論正常或出錯，都還原為原本的設定
Application.Calculation = calcState
If Err.Number <> 0 Then
MsgBox "發生錯誤：" & Err.Description, vbCritical
End If
End Sub
calcState 在一開始就記錄了原始狀態，On Error GoTo CleanUp 確保即使程式中途出錯，也一定會回到 CleanUp 標籤還原設定，不會讓Excel永遠停在手動模式。
六、巨集三件套效能優化
Application.Calculation 很少單獨使用，通常會搭配另外兩個屬性，合稱「巨集三件套」。三者一起關閉，才能達到最佳執行效率。
Sub 計算模式標準範本()
' 1. 記錄使用者目前的計算模式
Dim calcState As XlCalculation
calcState = Application.Calculation
' 2. 加入錯誤處理，確保異常時也能還原設定
On Error GoTo CleanUp
' 3. 切換為手動，開始執行主要程式
Application.Calculation = xlCalculationManual
' ── 主要程式碼放這裡 ──
CleanUp:
' 4. 無論正常或出錯，都還原為原本的設定
Application.Calculation = calcState
If Err.Number <> 0 Then
MsgBox "發生錯誤：" & Err.Description, vbCritical
End If
End Sub
三個屬性各有分工：Calculation 停止重算、ScreenUpdating 關閉畫面刷新避免閃爍、EnableEvents 關閉事件觸發，防止巨集執行期間意外呼叫 Change 或 Calculate 事件。還原順序建議與關閉順序相反，在較複雜的程式中能降低事件意外觸發的風險。
七、適合使用手動模式的時機與不適合的情境
了解使用時機，才能避免過度使用或在不適合的場合套用。
' 適合用 Manual 的情境（示意） ' ✔ 批量寫入大量資料 ' ✔ 大型報表重整與格式化 ' ✔ ERP資料整理匯入 ' ✔ VBA批次處理多個工作表 ' ✔ 自動化重建公式結構 ' 不適合用 Manual 的情境（示意） ' ✘ 小型檔案、公式少的活頁簿（效益不明顯） ' ✘ 即時儀表板（需要公式隨時反映最新數值） ' ✘ 即時交易資料監控（數據必須同步更新） ' ✘ 初學者尚未熟悉錯誤處理前（容易忘記還原）
判斷標準其實很直觀：如果程式會大量修改儲存格，或活頁簿中有許多複雜公式，幾乎都值得使用手動模式。相反地，如果程式本來就執行很快，或結果必須即時呈現，加上三件套反而增加複雜度，卻沒有明顯效益。
八、手動模式下強制觸發計算的方法
在手動模式中，有時程式邏輯需要讀取某個公式的最新結果才能繼續，這時就必須中途強制觸發計算。
' 強制計算的四種層級（由廣到細）
Application.Calculate ' 重算所有開啟中的活頁簿
ActiveWorkbook.Calculate ' 只重算目前的活頁簿
ActiveSheet.Calculate ' 只重算目前的工作表
Range("A1:B10").Calculate ' 只針對特定範圍重算
選擇哪一層級，取決於影響範圍。若公式只在單一工作表，用 ActiveSheet.Calculate 即可；若有跨表參照，就需要用 ActiveWorkbook.Calculate 才能確保結果正確。按F9等同於 Application.Calculate，Shift+F9 等同於 ActiveSheet.Calculate。
九、使用Application.Calculation的常見錯誤
實務上最容易踩到的兩個坑，都與「還原設定」有關。
' ❌ 錯誤寫法一：直接寫死還原為 Automatic Application.Calculation = xlCalculationAutomatic ' 問題：若使用者原本是 Manual，你幫他改掉了 ' ❌ 錯誤寫法二：沒有錯誤處理 Application.Calculation = xlCalculationManual ' ... 程式中途出錯 ... Application.Calculation = xlCalculationAutomatic ' 這行永遠不會執行到 ' 問題：Excel停在手動模式，公式顯示舊數值 ' ✔ 正確做法：變數記錄原始狀態 + On Error GoTo Dim calcState As XlCalculation calcState = Application.Calculation On Error GoTo CleanUp Application.Calculation = xlCalculationManual ' ... CleanUp: Application.Calculation = calcState
兩種錯誤的後果其實都一樣：Excel停在手動模式，公式不再自動更新，顯示的是舊數值，卻沒有人察覺。加上 On Error GoTo 的錯誤處理，才能確保無論程式正常結束或中途出錯，設定都一定會被還原。
心得
Application.Calculation 看起來只是一行切換模式的程式，但背後其實牽涉三個關鍵問題：誰的設定、什麼時候還原、出錯時怎麼辦。很多人學會這個屬性後，直接把還原寫死成 xlCalculationAutomatic，在自己的電腦上或許沒問題，一旦交給客戶或同事，就可能在不知情的情況下改掉對方的Excel環境設定。養成用變數記錄原始狀態、搭配錯誤處理還原的習慣，才是真正能放到正式環境的寫法。巨集三件套也是同樣道理，三個屬性要一起關、一起開，順序也有講究。這些細節平常不起眼，但在程式交接或出問題的時候，往往就是差這一點。
贊贊小屋VBA教學中心：
Excel巨集執行、Excel巨集程式、Excel巨集程式碼、Excel VBA教學、VBA教學、VBA基本應用、VBA UserForm、VBA VLOOKUP。
VBA課程推薦：零基礎入門進階的20小時完整內容
