Excel VBA：第2節沒有眼睛的世界

2023/09/03閱讀時間約 10 分鐘

0.食緊挵破碗，但小朋友不會，小朋友用手抓飯

　　在實際進入運用VBA編碼指令命令Excel進行什麼酷炫的自動化之前，我們應該先從簡單的（容易想像的）東西開始。

　　比如說，調整格式或許是個好案例。假使你的辦公室有在使用ERP之類的辦公室系統串聯彙整各部門資料，極有可能可以從資料庫中倒出你需要的資訊。然而，一般而言ERP系統最怕沒有撈到資料，寫進去的東西抓不出來，事事需要IT權限進去開鎖來抓，簡直煩死IT。於是按照我以偏概全的印象調查，大部分的ERP倒出的初級資料包山包海，需要使用者為了當次目的刪除不需要的行與列或重新排序，然後才終於可以寄給主管或是客戶。如果是一次兩次單獨事件稱不上繁瑣，但如果是例行公事的話，這工作可就耐人尋味了。

宣言指定代稱活頁簿或頁籤
同時控制兩個活頁簿
調整欄寬、刪除欄

　　希望從前曾經是我的問題的問題能帶給你靈感。

0.我也不想，都是生活逼的。

1.直欄橫行，不要再說了，Excel的世界就是這麼不講道理

　　假設一份6x8（含標題）微不足道的資料名叫test.xlsx，剛從你的系統中下載下來，它或許長這樣：

1.這份資料我隨便擬的。地址從長野縣的郵遞區號表抓的。

　　雖然看起來很棒，可是這個完成度要交給不是同事的別人的話，總讓人遲疑。C列欄寬不夠使得標題字被吃掉了；而且要提交的資訊其實也只需要A與C列的Site與Performace一覽。B、D、E、F都可以刪掉免得占用檔案大小。

　　但首先想要達成上面兩個目的（調整欄寬、刪除列）之前，我們必須先了解這次的巨集（VBA）應該寫在哪份檔案上？

　　眾所周知，如果這個初級資料是從ERP之類的系統下載下來的話，它當然是「剛下載」下來的（我在說什麼廢話？）意即，它每次都是全新的。這麼一來理所當然，你不可能每次都花十分鐘寫一支VBA然後用一秒鐘執行程式；不然你直接用三分鐘動動手指調整格式不就好了。

　　也就是說，既然目的是想要免去每次都要重新鑽木取火的麻煩，那就得固定在一個地方建造爐灶以便輕鬆生火。

2.從這個Excel控制那個Excel，傀儡提絲之術

　　綜上前提，必須先做一個讓VBA可以寄存的檔案，以便每次都可以藉由它來控制新下載下來的初級資料，進行調整格式的作業。

　　而這裡再次有了其它麻煩。世界唯一的東西不需要名字也不存在區別；但複數存在的時候，不得不說清楚，以免張冠李戴。而在程式的世界裡沒有眼睛，人眼可以看到這個檔案和那個檔案，一目瞭然；在程式裡則必須以「宣言（Dim）」指定：設址於長野縣的XXXX股份有限公司，下稱甲方。

　　現在，我們終於可以打開一個新的活頁簿，進入<Visual Basic>的編輯區塊進行編碼了。

上半闕編碼：

Sub test()

Dim wb As Workbook
Set wb = ThisWorkbook

Dim ws As Worksheet
Set ws = wb.Worksheets(1)

ws.Cells(1, 2) = "Try try."

End Sub

白話：

Dim wb As Workbook
宣言有一個活頁簿叫做wb。

Set wb = ThisWorkbook
我宣言名叫wb活頁簿以下可代指這份「寫錄這支巨集的活頁簿（ThisWorkbook）」。

　　一般情況下，指定代稱哪個活頁簿時的編碼應該是：Set wb = Workbooks("該Excel檔名")，且該檔案必須處於正在使用的狀態。但由於要指定的是現在正在編寫VBA的「這個」活頁簿，可用特殊略式：ThisWorkbook。

Dim ws As Worksheet
又說有一個頁籤叫做ws。
Set ws = wb.Worksheets(1)
我宣言名叫ws的頁籤，往後代指wb活頁簿的左起第一個頁籤。

　　(1)序號一律左起。如果今天要指定的是左起第三個頁籤，就是Worksheets(3)了。
　　(2)請注意這裡Worksheets是複數型。
　　(3)如果你的檔案類型是容易需要前後移動頁籤，或許指定頁籤名比較不會容易亂套；這樣的話請用雙引號""夾注頁籤名：Worksheets("sample")。
　　下面最後一行，我們想知道給VBA的這些代稱是不是都跟我們的認知相同？試試看讓VBA控制在儲存格上寫點什麼：
ws.Cells(1, 2) = "Try try."
指定ws頁籤的儲存格B1，錄入文字Try try.

　　是的，你沒有看錯。Cells(行,欄)的順序與平時我們指稱的順序相反，並且不用英文指稱欄位，而是用數字。Cells(1,2)控制的是儲存格B1。按下<執行>後結果如下：

2.Try try.

3.遠程遙控甲地租車乙地還

　　上面已經確認了記載VBA的活頁簿檔案在我們的控制之下了，現在要來處理每次下載下來的初級資料檔案。

下半闕編碼：

Sub test()
'前略

Dim db As Workbook
Set db = Workbooks("test.xlsx")

Dim ds As Worksheet
Set ds = db.Worksheets(1)

ds.Cells(1, 7) = "Check check."

End Sub

白話：

'前略
　　在編碼中需要註解時用單引號'，單引號後的任何文字都不會對程式產生影響。註解在後續需要修正時能夠提示這part是寫來幹嘛的，諸如此類，其實幫助很大，值得善用。
Dim db As Workbook
宣言有另一個活頁簿叫db。

　　請記得同一支VBA裡面的所有代稱可以隨便取，但不能重複。
Set db = Workbooks("test.xlsx")
以db代稱已打開的活頁簿test.xlsx。

　　如我們上闕所說，這是一般指令其它活頁簿時的編碼，前提必須是該活頁簿已經打開。
　　我想諸位會有所嗤笑：「每次下載下來的檔案怎麼可能都同一個名字？」——至少就我的個人經驗是如此。通常系統下載下來的檔案檔名都會後綴日期之類，讓你可以每天下載不覆蓋也不會搞不清楚哪個是哪個；固然每次都不會是相同名字。但這麼一來這裡的指稱可能會使VBA迷路，沒有正確的檔名就會找不到正確檔案，但這裡的解決案我們改天再談。
Dim ds As Worksheet
又宣言有另一個頁籤叫ds。
Set ds = db.Worksheets(1)
指定ds代稱db活頁簿的第一個頁籤。
ds.Cells(1, 7) = "Check check."
指定ds頁籤的儲存格G1，錄入文字Check check.

　　依然我們做了測試，看看這個「另一個」檔案是否已正確無誤地在我們的控制之下。<執行>後結果：

3.Check check.

　　到這裡看起來已經都準備好了，我們進入這次想要執行的目標動作。

　　雖然事已至此，還是岔題說明一下Microsoft Excel檔案的層級。一份Excel檔案我們稱之為「活頁簿（workbook）」，活頁簿下可以有許多不同「頁籤（worksheet）」。每個頁籤由「直欄（column）橫行（row）」組成表格，其中可以對每個儲存格（cell）或是複數儲存格的區塊（range）進行編輯。

報告到最後的時候老闆已經離席：

Sub test()
'前略

ds.Columns(3).ColumnWidth = 20
ds.Range("E:F").Delete
ds.Range("B:B,D:D").Delete

End Sub

白話：

ds.Columns(3).ColumnWidth = 20
設定ds頁籤的C欄（序號3）欄寬為20。

　　欄位可以用序號也可以用英文編號，英文編號時需用雙引號夾注：Columns("C")
ds.Range("E:F").Delete
刪除範圍E欄到F欄。

　　欄位除了用Columns指令，也可以利用範圍Range指令，但此時不能用序號。也請記得你現在控制著兩個檔案，必須要隨時指示這個指令是用在哪個活頁簿（頁籤）上。
　　另外，或許諸位已經察覺，關於刪除的指令一定要從後往前處理，否則因為前面刪除了一行，後面原本指示的F欄變成E欄，結果刪錯行的意外時而有之。附帶一提，VBA執行之後是沒有「上一步」可以返回的。在還不熟悉之時，檔案請先複製出來測試執行，以免損壞原檔資料。
ds.Range("B:B,D:D").Delete
刪除範圍B欄與D欄。

　　如果想跳欄刪除，中間用逗點,隔開。
　　一樣我們試著執行看看結果：