嫌Excel VBA執行速度慢,這些建議你一定要看

NewJune 發表於 2022-01-14
Excel

  Excel是辦公利器,這無需多言。尤其在辦公室,Excel用的熟練與否,會的Excel知識點多不多,很大程度上決定了你工作是否高效,能否按時打卡下班。可我們也時常聽到這樣的吐槽:Excel好是好,可就是表格大了之後,公式多了之後,它運算起來忒慢了。

  我們寫VBA巨集,除了要實現特定的自動化功能,還肩負一個極其重要的使命:讓Excel快起來~那麼如何優化我們的VBA程式碼,給Excel巨集提速呢?

方法一:啟用【手動計算】

我們都知道【公式】選單欄有【計算選項】,可以選擇【自動計算】、【手動計算】,一般Excel預設是自動計算。如果我們的工具是VBA+Excel公式結合,那麼VBA每執行一行,更改了某些單元格值,則整個Excel都會自動計算一遍公式。我們完全可以在VBA程式碼開始時先切換到【手動計算】,等VBA主體執行完後,再切換到【自動計算】,減少過程中不必要的計算開銷。

嫌Excel VBA執行速度慢,這些建議你一定要看

 

 

 具體到程式碼層面,寫起來也是非常簡便:

    Application.Calculation = xlManual '手動
    
    '在【手動】狀態下,需要點選【開始計算】來觸發excel計算公式
    Application.Calculate '開始計算

    Application.Calculation = xlAutomatic '自動計算

 

方法二:關掉Excel視窗的重新整理功能

當我們的VBA程式碼飛速執行時,伴隨著單元格值在不停變化,Excel介面也在快速計算和重新整理著,這都會拖慢VBA的執行速度,因此大多數情況下,我們可以選擇關掉頁面的重新整理,執行完不要忘記恢復它的重新整理功能,程式碼如下:

Application.ScreenUpdating = False '關掉螢幕重新整理
Application.ScreenUpdating = True '重新啟用螢幕的重新整理功能

 

方法三:程式碼中少用Excel公式,擅用字典

很多剛開始寫VBA的人,由於對語法還不是很熟悉,這類人更傾向於在指令碼里大量呼叫EXCEL基本的公式,如application.WorksheetFunction.VLookup(),這樣可以顯著提升指令碼的開發效率,本是無可厚非的事兒。隨著我們對VBA原生語法和資料型別越來越熟悉,不妨把Vlookup、Hlookup等函式,替換為用VBA字典實現。它語法更靈活,可以輕鬆實現表格從右往左的反向查詢。因為字典這類資料結構(Key,Value)查詢要比Vlookup等公式的匹配速度快很多。

 

方法三:把Excel檔案當資料庫來訪問

把每個工作表看作是資料庫表,用SQL查詢來提升VBA速度。VBA中使用SQL(結構化查詢語言(Structured Query Language))連線某個Excel資料來源(DataSource),可以隱式連線,而無需像workbooks.open那樣顯示載入開啟的表,對程式碼速度的提升顯而易見。使用SQL來過濾、篩選、條件判斷、分組、求最值等,更是可以讓VBA速度快到起飛,資料透視表都瞬間變得不香了。唯一的缺點,SQL的學習成本比VBA和公式還是要稍高些,間接拉高了學習門檻。但話又說回來,真正會寫SQL之後,你會發現以前一些略顯複雜的需求瞬間變得SO EASY。大膽去學吧,相信你絕不會後悔!

改進了VBA程式碼中這些細節後,你會發現,VBA原來可以這麼快!

嫌Excel VBA執行速度慢,這些建議你一定要看

 

 

 

後續的隨筆中,小爬會帶大家一起認識SQL,解鎖更多資料分析的技能,共同領略VBA+SQL的威力~

快來掃碼關注我的公眾號 獲取更多爬蟲、資料分析的知識!

嫌Excel VBA執行速度慢,這些建議你一定要看