優化VBA效率 之 優化錄製宏

曾經幫很多企業朋友升級/優化過管理信息系統，其中有一些系統會用到Excel作為重要的輸入輸出工具，因而也經常涉及到他們之前用VBA開發的一些模塊。讓我感觸最深刻的就是，很多這些VBA程序都包含有大段的錄製下來的宏代碼，大多沒有經過任何修改，導致程序運行時效率非常低下，屏幕閃爍不停。其實我們完全可以在不修改任何演算法結構的情況下，只按照幾個簡單的原則對錄製代碼進行一些優化，就能讓其運行速度提高十倍以上（取決於要處理的數據量，數據越多提高越明顯）。

首先，我們平時錄製的宏代碼，是VBA根據我們的人工操作過程，逐步生成的。須知，我們每一個簡單的操作，在VBA看來都可能是一大串細節操作的集合！比如設置單元格的背景顏色，在我們看來，可能只是想指定顏色為紅色，但是在VBA看來，它會認為你不僅想指定顏色為紅色，而且還要指定塗色方式為「全均勻塗色，無斜線/漸變」，陰影模式為「無陰影」，以及與顏色碼對應的Windows調色板等等。所以儘管我們事實上只想用到.interior.color=vbRed，但VBA會給我們錄製出一大堆代碼。這些不必要的屬性設置越多，效率就越低。

接下來，錄製宏中最影響效率的，則是大量的select/selection語句。在錄製宏的時候，我們人工操作必然要先選中一個單元格，再設置其屬性，於是VBA也錄製成了 range(「A1」).select ，然後再 selection.XXXX=XXXX。這裡selection就是「被選中者」，也就是前面的range(「A1」)。所以這句話其實完全等價於 range(「A1」).XXXX=XXXX。可是按照錄製宏的寫法，多出了一個range(「A1」).select操作，也就是模仿滑鼠點擊，讓這個單元格處於被選中狀態。而這個選中操作其實很複雜，包括刷新屏幕讓該單元格高亮顯示等等，是非常耗費時間的！

最後，根據VBA解釋器/編譯器的運行原理，表示屬性/方法的小數點 「.」 層次太深，運行效率也會受到影響，因為這涉及到「面向對象」層次結構在內存中的存儲和定址機制問題。總之大家記住，多用with就可以有效減少這種搜索深度方面的損耗。

好，到這裡我們可以簡單總結一下最基本的宏代碼優化原則：

（1） 刪除不必要的select/selection，將其整合為一個語句；

（2） 刪除不必要的屬性設置（當然，某個屬性是否必要需要認真判斷）；

（3） 多使用with語句，從而減少「.」的使用。

現在我們看一個例子。假如我們有下圖這樣一個任務：表格中從A1到N200共有2800個數字。現在希望把所有這些數字單元格的格式都設置為「粗體/斜體/下劃線/紅色14號字/黃色背景」。為了比較優化前後的運行效率，我們不使用Range(「A1:N200」)這樣的方式統一處理，而是用循環來逐個設置每一個單元格，共計設置2800次。

接下來，我們把這段代碼原封不動複製到自己的程序中，運行時間如下圖所示。在我的電腦上，共計用去14秒時間。