Excel工作表日期連動:1個VBA名稱變數的經典用法

Excel工作表日期連動是很好用的技巧,可以依照情況自行更改,本文以1個常見的爬蟲範例,教你學會VBA名稱變數的經典用法,讓你的程式專案更加自動化。

贊贊小屋VBA自學入門系列在先前文章介紹了網頁資料和新建工作表的程式,也介紹如何將程式匯出匯入,在這裡要將這些東西整合,設計一個自動取得網頁資料,並且將資放到新增的工作表上,除此之外,因為資料大多數是持續取得的,所以要以日期作為新工作表的名稱。

一、MsgBox與Date

Dim Today As Date

定義一個Today變數,它的類型是Date,表示是日期。

Today = Date

這裡的「Date」是一個函數,和Excel一樣VBA也有快速執行功能的函數,少部份函數是Excel和VBA共通,大部份是VBA特有的函數。這裡的Date便是VBA裡傳回系統日期函數,作用等於Excel的Today函數。注意到這一行的Date和上一行Date雖然是同一個單字,但一個是變數類型,一個是程式函數。就像Today在此是變數,在Excel裡可能一個函數,只是剛好名稱相同,必須看上下文確認它的功用,這在寫程式裡很重要。

MsgBox “今天是” & Today

這裡的VBA MsgBox函數是使用文字+變數的結構,截圖是完整的程式和執行結果,簡單三行程式碼,可以做出Excel做不到的效果。

一、MsgBox與Date

二、程式複製貼上

VBA巨集匯出匯入的方式可以取得寫好的程式,其實有一個更快方法,把兩個檔案都開啟,直接在VBA專案視窗就可以複製貼上操作,把每個模組當作是一個Word文書即可,差別只在於內容是程式碼而已。

例如這裡先選取「1.3 新增工作表」的「Module1」裡「Sub 新增工作表()」全部的程式碼,反白之後滑鼠右鍵,快捷選擇「複製」,操作流程和Excel或是Word是完全一樣。

二、程式複製貼上

三、自動填入日期

「新增工作表」是根據上個步驟所複製的「新增工作表」修改,程序名稱相同,但卻是存在不同檔案的程式。在此針對較為特別的程式說明如下:

Today2 = Format(Today1, “yyyymmdd”)

VBA Format函數的功用類似於Excel的TEXT函數,可以轉換數值類型。在第一個步驟可以知道Date函數會得到「2020/7/30」,但有在Excel新增工作表的讀者,應該瞭解工作表名稱不可以是像「/」這樣的特殊符號,因此在此把它轉換成20200730(”yyyymmdd”)。

Worksheets.Add after:=Sheets(Sheets.Count)

原始程式「新增工作表」中是以ShCount變數指稱工作表數目,這裡為簡化程式起見,直接將其內建到Sheets集合對象的參數,表示指定所有Sheets集合中的某一個工作表,亦即最後一張工作表。

ActiveSheet.Name = Today2

除了主詞+動詞,英語裡還一個基本句型是主詞+Be動詞+形容詞,用來說明主詞的性質。程式語言則是對象.屬性,用來設定對象的屬性值,這一行程式碼意思是將目前工作表(ActiveSheet)的名稱()設定為變數值(Today2)。因為上一行程式碼是新增工作表,Excel會把剛新增的工作表指定為目前工作表,配合這裡的ActiveSheet便是將新增工作表重新命名。

執行「新增工作表」巨集之後,果然在Excel活頁簿多了一個「20200730」工作表。

三、自動填入日期

四、VBA模組名稱

使用匯入巨集的方式,將取得網頁資料的程式模組匯入,由於原模組名稱為「Module1」,和目前活頁簿專案的「Module1」名稱衝突,VBA自動將名稱改為「Module11」。

VBA編輯器的介面中左上區域是專案視窗,左下區域是所選取專案對象的屬性視窗,例如「Module11」的「Name」屬性欄位,選取「Module11」,可以在這裡直接更改。

四、VBA模組名稱

五、合併Sub程序

沿續上個步驟,先複製整個「新增工作表」程式碼,將「Sub 新增工作表」改為「Sub 新增工作表並取得網路資料」,然後將「Module11」名稱更改為「Module2」,把「取得網頁資料」中With…End With的程式碼複製貼到「Sub 新增工作表並取得網路資料」的下面。只要瞭解VBA Sub用法和一行一行依序執行程式的性質,就能清楚地進行像這樣的操作。

五、合併Sub程序

六、執行階段錯誤

執行「新增工作表並取得網路資料」巨集會提示執行階段錯誤,這個錯誤很容易理解,解決方法也很簡單,就是先把現有的「20200730」工作表刪除,接著就可以順利執行程式了。

六、執行階段錯誤

七、Excel工作表日期連動

刪除名稱重複的工作表後再次執行巨集,果然成功新增一個以當天日期為名稱的工作表,而且這個工作表上已經下載好了所需要的網頁資料,可以讓整個VBA爬蟲程式更為完整。

Excel工作表日期連動:VBA爬蟲自動填入日期名稱

程式碼如何精簡及優化

本篇文章最後有三點補充:

1.程式碼精簡:

第三步驟有利用Sheets(Sheets.Count)簡化程式,這個隨著程式碼越來越多是蠻重要的不過對於程式初學者或者以實務用途為主而言,不用急著要精簡程式,只要依照思惟邏輯設計,養成像是設定變數的良好習慣即可。隨著程式經驗的累積,自然會知道怎樣寫程式比較好,這個跟寫文章是一樣道理。

2.Excel操作:

VBA本質上仍然是操作Excel,只是比較特別以程式碼方式執行,所以這裡遇到的名稱不可有特殊符號、名稱不可重複,這個是Excel本身的限制,VBA程式碼並不會突破Excel範圍進行操作。

3.程式優化:

這裡的程式範例如果工作表名稱相同會跳錯,其實隨著程式設計功力的強化,可以進一優化,例如預先偵錯是否相同名稱的工作表,有的話,跳出對話方塊,提醒操作者並且提供是否刪除或覆蓋等選項,這些功能和程式碼精簡一樣,建議可以多多參考VBA教學文章,進一步優化程式執行體驗。

本文章講解影片:

YouTube video

完整VBA課程:職場及投資應用,20小時入門進階:

VBA課程:職場及投資應用,20小時入門進階

最新文章: