本篇介紹如何使用 Excel 的錄製巨集功能,將使用者的操作錄起來,自動產生 VBA 的指令稿。
在 Excel 使用 VBA 開發自動化的程式時,難免會若遇到某些動作不知道該怎麼用 VBA 來撰寫,傳統的作法當然就是去翻官方的 API 文件,看看自己想要使用的功能是不是有出現在 API 中,但是這種做法是最標準的,但是卻很花力氣、更會浪費許多時間。
Excel 的「錄製巨集」其實是一個功能強大,而且也很常被使用的工具(如果會用的話),所以 Excel 在視窗的左下角特別放置了一個錄製巨集按鈕,只不過可能很多人都沒注意到它。
而在開發人員工具列中,也有一個同樣的錄製巨集按鈕,在使用時要按哪一個都可以。
當我們想要錄製一連串繁雜的操作,或是想要產生一些不知道怎麼寫 VBA 程式的動作時,都可以使用錄製巨集功能,以下我們示範錄製巨集的操作步驟。
這裡我們示範錄製一小段變更日期格式的巨集,將變更日期格式的動作記錄起來,方便套用至其他的儲存格上。
Step 1
請先選擇好要處理的資料,準備好之後,再按下「錄製巨集按鈕」。
當然我們也可以先按下「錄製巨集按鈕」,再去選擇儲存格,這樣的話選擇儲存格的動作也會被錄裡起來,先後順序就看使用者需不需要紀錄選擇儲存格的動作,或是說就把全部的動作都一次紀錄下來,最後再把不要的刪掉也可以。
Step 2
在使用者按下錄製巨集按鈕之後,就會出現這樣的錄製巨集視窗,輸入巨集名稱後,按下「確定」即可開始錄製巨集。
Step 3
接下來使用者在 Excel 中所有的操作都會被記錄下來,包含改變資料內容或格式、選擇儲存格、加入圖表等,這時候我們就可以把想要讓 Excel 錄製下來的動作,整個操作一次,讓 Excel 記錄下來。
在這個範例中,我們要修改日期的格式,我在右鍵選單中打開儲存格格式的視窗,調整了一下日期的格式,還有自型與顏色。
在錄製巨集的期間,雖然使用者可以進行各種複雜的操作,但是如果後來需要自己修改 VBA 程式碼的話,建議不要一次錄太多的動作,以免產生的 VBA 程式碼太複雜,自己都看不懂。
Step 4
將所有要記錄的動作都操作一遍之後,就按下「停止錄製巨集」的按鈕。
「停止錄製巨集」按鈕的位置跟原本「錄製巨集按鈕」的位置相同,只是在錄製期間這個按鈕就會變成「停止錄製的按鈕」。
Step 5
在「開發人員」工具列中,點選「巨集」按鈕。
Step 6
選擇剛剛錄製的巨集後,點選「編輯」。
Step 7
打開 VBA 程式碼編輯器之後,就可以看到剛剛錄製下來的 VBA 程式碼了。
以下是錄製下來的程式碼,我稍微加上簡單的註解:
' 調整 Excel 視窗位置(沒用) Application.Left = 985 Application.Top = 30.25 ' 設定日期格式 Selection.NumberFormatLocal = "[DBNum1][$-404]m""月""d""日"";@" ' 設定字型 With Selection.Font .Name = "標楷體" .FontStyle = "標準" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .Color = 255 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With ' 設定背景顏色 With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With ' 調整 Excel 視窗位置(沒用) Application.Left = 816.25 Application.Top = 186.25
這段程式碼當中最重要的就是中間的設定日期格式、字型與背景顏色三大部分,而頭尾卻出現幾行調整 Excel 視窗位置的程式碼,那就是因為我在錄製巨集時,不小心拉動了 Excel 視窗的位置,所以也一起被錄了下來,通常在錄製巨集時,多多少少都會參雜一些這類沒用的程式碼,所以錄完之後,我們都會大約看一下整個程式,把沒用的部分刪掉,或是只找出我們有興趣的部分,複製出來貼到自己寫的程式中使用。
當錄製好巨集之後,我們就可以參考其中的 VBA 程式碼,把它改成自己可以用的版本。
以上面這個修改日期格式的例子來說,它是作用在目前所選擇的儲存格中(Selection
),而我們只要把這個位置替換成我們要區域(請參考活頁簿、工作表與儲存格的教學),就可以變成可以修改任意儲存格日期格式的 VBA 程式碼了。
以下是換成 A5
儲存格的例子,執行就會修改 A5
儲存格的日期格式:
' 設定 A5 儲存格的日期的格式 Range("A5").NumberFormatLocal = "[DBNum1][$-404]m""月""d""日"";@" ' 設定 A5 儲存格的字型 With Range("A5").Font .Name = "標楷體" .FontStyle = "標準" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .Color = 255 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With ' 設定 A5 儲存格背景顏色 With Range("A5").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.799981688894314 .PatternTintAndShade = 0 End With
至於細部的字型與顏色等屬性,我想大家應該看了就懂了,需要改什麼就直接改,善用錄製巨集的功能,可以讓程式開發者省下很多力氣。
更多關於 VBA 的教學文章,請參考 VBA 程式設計。
參考資料:經理人、Excel Easy、Contextures