10個加速Excel巨集的方法

用VBA將報表自動化也一段時間了，

但有時候資料量過大，VBA的效能真的會讓人覺得......

不過看到這個國外網站後真的獲益良多~


Ref:
"10 ways to speed up your macros"
https://www.dummies.com/software/microsoft-office/excel/10-ways-to-speed-up-your-macros/

基本上1 ~ 5點可以放在一起：

1. HALTING SHEET CALCULATIONS (停止sheet頁面的自動計算)

→  
當調整為手動時(Application.Calculation = xlCalculationManual)，執行巨集時，sheet上所帶的公式都不會跟著更新。因此如果執行巨集時會用到公式，此功能依然要設為自動(Application.Calculation = xlCalculationAutomatic)

2. DISABLING SHEET SCREEN UPDATING (停止螢幕更新)

→   此為「開啟」螢幕更新的狀態

→  此為「關閉」螢幕更新的狀態

當自動生成的Sheet很多時，速度是不是有差呢！！ 小弟覺得最有感的加速方式！！

範例檔案：網頁爬蟲(環境資源資料開放平臺)

3. TURNING OFF STATUS BAR UPDATES (停止狀態列更新)

→ 狀態列在哪邊？就是sheet下面那條


4. TELLING EXCEL TO IGNORE EVENTS (忽略事件變更)

→ 可參照 https://ithelp.ithome.com.tw/articles/10156956 (Excel VBA 的眉眉角角Day6:關於工作表程式觸發與暫停觸發)

5.  HIDING PAGE BREAKS (隱藏分頁線)

→ 列印時，分隔第1頁、第2頁、...的線，這個平常基本上不會用到

根據上述5點，可依序加入在VBA code的最上方 與 最下方
最上方：關掉、變更1~5點所提及的應用程式物件狀態
最下方：開啟、變更1~5點所提及的應用程式物件狀態

Sub SpeedUp_Macro()
    Application.Calculation = xlCalculationManual       '---1
    Application.ScreenUpdating = False                  '---2
    Application.DisplayStatusBar = False                '---3
    Application.EnableEvents = False                    '---4
    ActiveSheet.DisplayPageBreaks = False               '---5

    'Put your VBA code here

    Application.Calculation = xlCalculationAutomatic    '---1
    Application.ScreenUpdating = True                   '---2
    Application.DisplayStatusBar = True                 '---3
    Application.EnableEvents = True                     '---4
    ActiveSheet.DisplayPageBreaks = True                '---5
End Sub

VBA

6.   SUSPENDING PIVOT TABLE UPDATES (停止樞紐分析表更新)

這邊應該滿直觀的，當要執行你的Code之前，先將樞紐分析表切換成「手動更新 」，
等到Code執行完後再切回「自動更新」。

Sub Macro1()
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
    'Put your VBA code here
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
End Sub

VBA

7. STEERING CLEAR OF COPY AND PASTE (避開"複製"與"貼上"的指令) 

一般來講，我們用巨集錄製的複製與貼上指令會長這樣：

'Ver. 1
Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

VBA

但實際上，複製與貼上的指令可用一行完成，並且可省下上述Code複製後暫存於剪貼簿的時間

'Ver. 2
Range("A1").Copy Destination:=Range("B1")

VBA

小弟針對此寫了一小段測試Code：VBA-複製貼上語法比較

現在給定1~10000這一萬個數字，我利用這兩種版本的程式碼，

分別執行100次，最後記錄這100次它們各別平均花了多少時間

Ver. 1 的程式碼：平均約0.05 sec ；Ver. 2的程式碼：平均約0.01 sec

當資料大時，Ver. 2的速度明顯優於 Ver. 1

此外，還有提到另外兩項：

(1) 純粹複製數值

Range("B1").Value = Range("A1").Value

VBA

(2) 複製公式

Range("B1").Formula= Range("A1").Formula

VBA

8. USING THE WITH STATEMENT (使用With敘述)

直接使用With敘述，可讓excel知道你With了哪一個Object，

然後就可以一次修改Object內部成員的值。

9. AVOIDING THE SELECT METHOD (避免使用"Select")

呼應第7點，複製貼上用一行解決即可，避免使用多餘的Select指令

10. LIMITING TRIPS TO THE WORKSHEET (限制程式在Worksheet中的行為)

這點真的太實際啦！
若在程式碼中，若一直針對Cells操作，將使得程式效率大幅降低。
原文的範例，即是將Range("A1")的值先設給一個變數，
然後再用此變數去跑For loop判斷式，藉此避免每次跑For loop時，都要去存取Cells

此外，可以再參照此網站的第6點，馬上開門見山說了：
「每一次Excel 與 VBA 之間的資料搬移都是額外的負荷」
這邊我覺得他講得很好，那張圖更是貼切XD

而且第6點這個Range 轉成 Array的方法可以說是相當實用!!