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小教室!