Excel 大補帖

【Excel 宗師】用 VBA 寫支機器人🤖

實現資料處理、解放雙手的最後一哩路！

10 min readJun 9, 2022

你聽過「用 Excel 寫程式」嗎？Microsoft Excel 內建開發人員工具，提供使用者利用錄製巨集或是撰寫語法的形式，讓資料處理能夠一指完成，100% 解決資料處理流程複雜的痛點。這麼神奇的功能，讓我們一起學起來！

這篇單元專欄希望帶你認識 Excel VBA 的基本樣貌及應用場景外，也期待能提供你可遷移使用的語法，未來有任何程式撰寫需求都能直接從這裡獲取資源。

本文目錄
1. VBA 基本介紹2. 應用場景－以 RPA 為例3. 核心語法剖析4. 結語：學習 VBA 大有好處5. 延伸閱讀

▍VBA 基本介紹

VBA 全稱為 Visual Basic for Applications，意思就是用 Visual Basic 程式語法撰寫應用程式。微軟在 Excel 上利用增益集、開發人員工具等，提供使用者各式各樣的資料處理功能，滿足大眾多元的使用需求。

同樣地，在某些工作人士的眼中，重複性質高的任務能夠被自動化處理是甚好，不只能夠將時間花在更有意義的決策上，還可以減少企業的人力成本。於是，VBA 的出現徹底滿足了我們希望自動化流程的需求。

因此，我們通常會使用 VBA 做到兩件事：

錄製巨集（Macro）
撰寫語法（Programming）

Macro 會把使用者利用鍵盤輸入或滑鼠點擊的軌跡錄製起來，並且自動轉換成 VBA 可以理解的程式語法。之後只需要啟動錄製好的巨集，就可以讓 Excel 自動執行已預先錄製好的流程

Programming 則是手動將操作步驟以程式語法寫成腳本，只要語法符合邏輯，Excel 同樣能夠自動完成資料處理。上述差異在哪呢？比較之前，我們先來看看除了 VBA 之外，自動化處理的另一項好選擇：RPA。

▍應用場景－以 RPA 為例

說到自動化處理，近年企業吹起一股數位轉型風潮，導入 Robotic Process Automation, RPA（機器人流程自動化）協助改善或解決複雜繁瑣且執行頻率高的流程。

Excel VBA 也作為一種自動化流程的工具，到底與 RPA 有什麼不一樣呢？讓我們用三個例子了解更多吧 👀

1. 操作環境大有來頭！

Excel 支援主流的 Microsoft Windows 和 macOS 系統，只要在 Excel 的開發人員工具打開「巨集」後，就可以盡情錄製或撰寫巨集。

但是，RPA 工具背後通常有支援廠商，像是市場主流的 UiPath、RPA 始祖 Blue Prism，或是 RPA 獨角獸 Automation Anywhere。這些廠商提供圖形化介面，讓使用者可以 No-code（不寫程式）的方式完成機器人流程的設計。

2. 程式語言基礎可能很重要？

我們可以用寫程式碼的方式完成 Excel 巨集，但其實不論是 VBA 或是 RPA，基本上都是不需要程式設計背景就可以輕鬆打造自己的機器人。因為 VBA 提供錄製（Recording）的方式，讓使用者能夠不寫程式就自動完成步驟。

上述的 RPA 廠商也積極打造使用者友善的介面、降低 IT 門檻，讓非技術人員也可以輕鬆上手，幫助整間企業能順利推動數位轉型。

3. 低成本高效率的秘訣是⋯⋯

撇除學習工具的時間成本或是設計機器人的人力成本外，通常大家最在意的就是工具本身的價格。VBA 最大的好處就是依附在 Excel 上，基本上就是免費使用，不需要額外負擔任何軟體費用。

綜合來說，Excel VBA 可錄可寫，我們不一定需要很強的資訊背景才能完成，反而能夠藉由免費、最小學習成本的方式快速上手和熟悉自動化流程，幫助我們解決重複性任務。

為了帶大家直接體會 VBA 的強大，下方我們正式進入巨集語法剖析，建議你可以跟著做，把這項強大的技能帶走！

點我訂閱赴日工作、海外留學、Excel 攻略專欄

medium.com

▍核心語法剖析

在開始拆解語法之前，請先確保你的 Excel 能正常開啟並使用 VBA 功能。

上方工具列點一下「檔案」
在「選項」中找到「自訂功能區」
勾選「開發人員」後按一下確定保存設定

之後便可以直接從上方工具列找到「開發人員」，並在功能區找到 Visual Basic、巨集、錄製巨集等功能。

點一下「Visual Basic」後能進入 VBA 工作區域，這邊我們需要在左側專案的模組底下插入一組模組。

讓我們試試看用 VBA 寫一組 “Hello, World” 的程式吧！

Sub Hello_VBA()
  MsgBox ("Hello, World!")
End Sub

Sub 和 End Sub 負責把程式碼包起來，裡頭的 MsgBox 則是訊息泡泡功能，負責將 ("") 內的文字內容以訊息視窗的方式輸出。

按下 Ctrl + S 儲存後即可用 F5 執行這段程式碼。

恭喜！我們完成了第一支在 Excel 執行的 VBA 巨集，接著我們往下看 VBA 常見的五種應用 😎

1. 自動匯入檔案

Sub Import_Data()
  Dim FolderPath, FilePath, FileName
  Dim MacroWorkbook As Workbook
  Dim ImportWorkbook As Workbook
  Dim SheetName As String
  Set MacroWorkbook = ThisWorkbook
  FolderPath = ThisWorkbook.Path
  FileName = Dir(FolderPath & "\Damien*.xls*")
  FilePath = FolderPath & "\" & FileName
  'Workbooks.Open FileName:=FilePath
  Set ImportWorkbook = Workbooks.Open(FilePath)
  SheetName = Sheets(1).Name
  ImportWorkbook.Worksheets(SheetName).Copy After:=MacroWorkbook.Worksheets("工作表1")
  ImportWorkbook.Close
End Sub

上方程式碼看起來有點複雜，但基本上可以拆解成下方三項流程：

宣告變數
使用 Dim 宣告資料夾路徑、檔案路徑、檔案名稱、本活頁簿、需匯入活頁簿、工作表名稱
將參數指定給對應的變數
把相對應的值指定給上述變數，整體邏輯可以描述成：「現在我開啟的活頁簿是本活頁簿，位址是 Path，相同路徑還有一組以 Damien 為開頭命名的 XLSX 檔案，把資訊組合起來就得出需要匯入活頁簿的存放位址」
自動開啟檔案後複製裡頭的內容並關閉該檔案
使用 Open 開啟需匯入活頁簿，並且我需要匯入這個活頁簿的第一個工作表，使用 Copy After:= 匯入至本活頁簿的工作表 1 後面，最後將需匯入活頁簿關閉

2. 自動建立工作表

Sub AddNewSheet()
  Sheets.Add(After:=ThisWorkbook.Worksheets("工作表1")).Name = "Tab2"
End Sub

Sheets 是指針對工作表操作，在這我們用 Add 設定參數，裡頭包著 After:=ThisWorkbook.Worksheets("工作表1") 意思即為：「我要在這個活頁簿的工作表 1 後面建立新工作表」。

操作的指令則使用 Name 並用等號指定 “Tab2” 為追加工作表的名稱。

3. 找尋最後一列（Last Row）

Sub Find_Last_Row()
  LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
End Sub

這邊使用 Cells 並分別餵入 Cells.Rows.Count 及 “A”，意思是前往 A 行的最後一列，接著我們利用 End(xlUp) ，形同使用 Ctrl 或 COMMAND + ↑，定位 A 行最後一列有值的資料格，最終使用 Row 取得列數。

4. 自動複製貼上

Sub Copy_Paste()
  Range("A1:C5").Copy
  Range("F1").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

關於複製、僅貼上值，可以先使用 Range 並利用 Copy 複製某個範圍，接著再使用 PasteSpecial ，並且分別設定 Paste 參數為 xlPasteValues （僅貼上值），後面的運算、略過空格、轉置則不需要特別設定即可。

5. 自動輸入公式

Sub Input_Formula()
  Range("A1").Formula = "=SUM(1, 2, 3)"
  LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  Selection.AutoFill Destination:=Range("A1:A" & LastRow)
End Sub

首先，我們希望在 A1 資料格輸入某個公式，因此 Range("A1").Formula 實現了自動輸入公式的功能，後面將 =SUM(1, 2, 3) 指定給 A1，記得要加上上下引號。如果希望自動填滿公式到最後一列。

另外，可以結合第 3 點學到的找尋最後一列，並且使用 Selection.AutoFill Destination 後接 := 輸入範圍參數「A1 至 A 行的最後一列」。