VBA新增工作表教學:善用變數設定及MsgBox提示視窗

Excel新增資料的第一動作新增工作表,本文先介紹VBA程式如何計算目前活頁簿工作表數量,進而學習變數設定及MsgBox提示視窗,最後在Excel檢視及執行程式巨集。

上一節已經寫好取得網頁資料的程式,它預設是將資料放在目前工作表上,實務上的狀況往往資料是不斷累加,通常會放在Excel不同工作表上,依照上一節的程式架構,每次執行程式取得資料前都要先新增一個空白工作表,取得資料後再適當更改工作表名稱。

既然已經進入寫程式的世界,像這樣每次都要執行的操作,應該乾脆將它寫到程式裡。這一節先介紹在VBA如何新建工作表,往後章節繼續完善整個取得網路資料的程式。

一、多少張工作表

Dim ShCount As Integer

Dim在VBA裡是定義變數的意思,Integer是整數,所以「Dim ShCount As Integer」表示定義一個「ShCount」的整數類型變數。

國中的時候都有學過方程式,一開始會設定x,y變數,在數學的世界裡面設定變數非常有用,在程式的世界裡面,設定變數同樣有很多好處。和國中數學一樣,程式變數其實就是一個代名詞,指稱不特定對象,可以讓我們在設計程式時更為精簡表達。讀者剛開始也許覺得陌生,隨著本書章節的進行,應該能逐漸體會到為何程式要設定變數的道理。

ShCount = Sheets.Count

和英文文法一樣,VBA主要句型為主詞+動詞,在程式裡寫法是對象.指令,所以這裡的「Sheets.Count」便是計算工作表的意思,計算結果作為變數「ShCount」的值。

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 1

二、不小心報錯了

接下來使用MsgBox指令,它在VBA作用是可以叫出一個顯示訊息的小對話方塊,這裡希望顯示的訊息是有多少個工作表,所以先是一個文字字串「”本活頁簿共有”」,空一格,文字連結符號「&」,再空一格。本來是想選取ShCount複製過去,可是當我們游標離開目前這一行時,VBA以為我們程式輸入完了,會自動幫我們檢查程式都市,於是它會發現到MsgBox的顯示參數並不完整,所以跳出錯誤提醒。

這裡其實我們很清楚,是因為想要到其他地方進行複製的緣故,是故意中斷的,待會將繼續輸入,因此可以不用理會這個編譯錯誤的提醒。

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 3

三、必學MsgBox命令

MsgBox “本活頁簿共有” & ShCount & “個工作表”

完整的MsgBox指令行。注意到「ShCount」是從上一行程式碼選取複製過來的,因為它是一個VBA會進行程式讀取的變數,所以左右不用加英文雙引號把它括起來。相對而言,像「”個工作表”」就必須表明為純粹文字的符號,VBA才知道它不是程式碼。

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 5

四、看看我的程式

VBA程式碼輸入完了回到Excel,上方功能區的「開發人員>程式碼」這裡擊點「巨集」。

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 7

五、執行我的程式

在「巨集」視窗會看到先前步驟寫好的「計算工作表」程式,直接按「執行」。

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 9

六、在Excel看到VBA

果然跳出一個小對話方塊,顯示訊息為「本活頁簿共有2個工作表」,截圖可以看到作者將VBA程式碼複製到Excel工作表上,這是為了說明方便。接下來這本書讀者如果有看到Excel工作表上有VBA程式碼,表示在VBA編輯環境也已經寫好了同樣的程式碼。

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 11

七、後面排隊謝謝

Worksheets.Add after:=Sheets(ShCount)

同樣用到VBA文法裡主詞.動詞的結構。主詞是工作表(Worksheets),動作是新增(Add)。

這裡比較特別的是後面還加了一個副詞來描述這個動作,希望把新增的工作表放在最後面。參考上個步驟的巨集程式,Sheets(ShCount) 等同於Sheets(2),意思是第二個工作表,after:=Sheets(ShCount)表示是在第二個工作表之後。如截圖所示,效果就是最後一個工作表(「新增工作表」)的後面,再新增一個工作表(工作表3)。

從這簡單的程式範例可以知道為什麼要用變數「ShCount」,它並沒有特定的值或指定對象,但它都是表示活頁簿的工作表總數。因此不管在執行程式的時候,不管活頁簿裡面有多少個工作表,都會最後的位置新增一個工作表。

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 13

VBA變數與MsgBox命令

這一節用到了VBA裡面的變數和MsgBox指令,其實VBA還有很多其他的變數類型,MsgBox也有很多其它可使用的參數,本書主要是以VBA大數據分析實務應用為主,不會針對單項有細節完整的介紹。不過只要有用到的程式作者都會說明,讀者從實務範例切入,這也是一種學習熟悉VBA程式的有效方式。

每天學習,每天充電:VBA爬蟲專案文章合集

本文內容取自《人人都學得會的網路大數據分析入門》,書本仍然是最好的學習方法,省下一張電影票的錢,今天就買本書吧!

VBA新增工作表教學:善用變數設定及MsgBox提示視窗 15
VBA新增工作表教學:善用變數設定及MsgBox提示視窗 17
加入Line社群,口袋裡的Excel小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.