工作自動化要角: 變數與迴圈

　　了解了如何操縱EXCEL活頁簿內工作表及儲存格的物件, 就可以用巨集把要填的資料寫好, 重複執行, 節省時間. 這是工作自動化的第一階段, 重覆執行. 例如在活頁簿workbook的程式區段寫

Sub WirteNo()
    Cells(1, 1) = "編號"
    Cells(2, 1) = 1
    Cells(3, 1) = 2
    Cells(4, 1) = 3
    Cells(5, 1) = 4
    Cells(6, 1) = 5
End Sub

然後分別在sheet1 ~ sheet10執行巨集 WriteNo, 則每個工作表的A欄都會變成如下圖

你在重覆執行的過程, 可能會有很多想法跑出來, 例如

這個工作用自動填滿, 複製/貼上就可以了, 可能還快些!

如果要填到100, 甚至到1000, 那一開始的程式撰寫不是把手給打字打斷了嗎?

如果有一百張工作表, 重複100次巨集執行也很煩, 這不能自動執行嗎?

這些問題, 就讓我們的自動化的技巧進入第二階段了, 變數與迴圈.

首先說明一下變數. 變數就是跟VBA申請一個儲存位置, 這個儲存位置可以存入不同的數值. 當然, 這個儲存位置必須有一個名字, 以便跟別的儲存位置作區別. 這些儲存位置及其名稱就是變數. 申請的方式如下

Dim 變數名稱 As 資料型態

變數名稱你可以英數中組合來命名, 但第一個字不能為數字, 一般會以資料型態當啟始加上有意義的英文字, 例如整數資料的指標 iIndex, i 是 integer , Index是指標. 所以宣告寫成

Dim iIndex As Integer

但常用 Dim i As Integer 來簡化, 因為可能同時有好幾個指標變數, 就簡化成 i, j, k, l, m, n了. 而資料型態除了整數, 常用的還有文字, String, 等等, 詳細內容請自行參閱VBA說明, 例如查詢 Dim, 然後連結到各相關的說明來增強基本知識, 能力.

現在你可以在程式區段寫入

Dim i As Integer

i=1

Cells(i,1)=i

這相當於

Cells(1,1)=1, 

就是告訴電腦把i用1取代. 如果 i=2, Cells(i,1)=i就變成了 Cells(2,1)=2. 你可能會想, 需要的程式是Cells(2,1)=1, Cells(3,1)=2, 而且用三行取代一行, 好像變複雜了. 

先來解決第一個問題, 把Cells(i,1)=i改成 Cells(i+1,1)=i 就可以了. 也就是說變數在程式碼中是可以做各種數學運算的. 程式編譯(解讀)器會先把運算做完再執行填入動作.

然後我們要進入迴圈的說明, 來解決第二個程式簡化的問題.

首先說明 For - Next 迴圈, 寫法是

For 變數 = 起始值 to 結束值 step 變化值

　　程式碼

Next

意思是說, 請VBA把變數從起始值開始, 每次調整變化值一直到結束值, 每調整一次, 就把程式碼執行一次. 導入上面的例子, 填寫1 ~5, 我們的For - Next寫成

For i = 1 to 5 step 1

Next

程式碼的部份就是Cells(i+1,1)=i, 把兩者組合起來

For i = 1 to 5 step 1

　　Cells(i+1,1)=i

Next

意思是執行5次 Cells(i+1,1)=i, 其中i 會是 1,2,3,4,5. 這隱含了 i 會自動加1的功能. 如果是 step 2, 就會自動加2了, 而i只會是1,3,5. 當是step 1時, 是可以省略不寫的.

這樣, 程式碼原本5行就變成了3行了. 把全部重新整理一下, 就成了

Sub WirteNo()

　Dim i As Integer

    Cells(1, 1) = "編號"
    For i = 1 to 5 step 1

　　Cells(i+1,1)=i

　Next

End Sub

如果要做到1000, 就把5改成1000就好了, 這樣效果就來了吧!

其他的迴圈還有For Each - Next,  Do - Loop. 請自行詳細參考VBA說明.

在做迴圈時要注意不要變成了無窮迴圈, 也就是離不開迴圈而造成當機現象, 這時就必須強制中斷, 可能造成資料損失, 程式碼無儲存結果. 一個解救方式是在迴圈中加入 DoEvents 陳述式, 讓程式暫時停止, 去看看有沒有插入的命令, 如停止命令.

一般For - Next已經設了上下限值, 比較不會出現無窮迴圈的現象, 但還是有可能, 例如在自動加1的迴圈中先行減1, 如下

For i = 1 to 5

　i=i-1

Next

這樣 i在每次迴圈時會永遠是1, 就沒完沒了了. 而 Do - Loop 必須配合 While,  Until 關鍵字 或 Exit Do 陳述式來控制跳出迴圈. 而Exit Do還要另外配合 If - Then - Else 或 Select Case 等陳述式做判斷.

有很多時候都是要判斷各種情況才能決定下一個步驟要做什麼, 例如上述 For 迴圈, 如果i > 5的時候就跳出迴圈. 但這個判斷是屬於 For - Next 的一部份. 平常程序中的判斷就用 If - Then - Else, 當一個運算式有多種值的判斷時, 就用 Select Case.

例如一個活動的報名表, 如下

　　Ａ　　Ｂ　　Ｃ　　Ｄ
１編號    姓名　姓別    出席

２　１　陳大　先生　　Ｖ
３　２　梁二　女士　　Ｖ
４　３　張三　先生　　Ｘ
５　４　李四　女士　　Ｖ
６　５　王五　先生　　Ｘ

要向出席的人員打招呼, 在螢幕中秀出早安可以這樣寫

Sub GoodMorning()
    Dim i As Integer

    For i = 2 To 6
        If Cells(i, 4) = "Ｖ" Then
            MsgBox "早安! " & Cells(i, 2) & Cells(i, 3)
        End If
    Next
End Sub

其中用For 從第二行到第六行進行掃瞄, 用If判斷出席欄是否打"V", 有出席就用 Msgbox 秀出打招呼, 共有三個人, 例如

早安! 陳大先生

程式中 "&" 的功能提把文字串連起來, 是文字運算子.

我們還有一個問題, 就是每一頁A欄都要自動填入編號1到5, 而不用重複換頁並執行WriteNo巨集, 這要怎做? 很簡單, 就用巢狀迴圈, 如下

Sub WirteNo()

　Dim i As Integer

　Dim j As Integer

    For j = 1 to 10

        Sheets(j).Cells(1, 1) = "編號"
        For i = 1 to 5

　　    Sheets(j).Cells(i+1,1)=i

　    Next
    Next

End Sub

在執行前, 要先確保有10個工作表哦. 我們用了j變數來控制工作表的轉變, 同時明確指出是那一個工作表的儲存格要填入資料. 這樣就可以看出效率來了.

巢狀寫法可以任意組合, 只要確保一組陳述式是包在另一組陳述式裡面而不是交叉即可. 例如以下的交叉寫法

    Dim i As Integer
 
    For i = 1 To 10
        Do
            Msgbox i
    Next
        Loop Until i > 5

就會造成邏輯不明的情況, 是錯誤的.

另外在變數的部份, 有時候會因為打字錯誤不自知, 造成程式結果錯誤而又不容易查出來的狀況, 這時候可以在程式區段的最上方輸入

Option Explicit

請VBA自動幫我們檢查, 沒有用 Dim宣告的變數都不可以出現在程式中.

只是你遇到的問題一定會比上面的例子還要複雜, 你得用這些工具來分解對應你的問題並用程序把它們組合起來, 這就需要你自行多多練習, 並加強邏輯觀念了.

學會了使用變數, 迴圈, 還有判斷式, 對工作自動化就能事半功倍了, 文中提到但沒有示範的部份, 請一定要自行參考說明, 並加以練習以提昇自己的功力.