第 21 章 VBA 輕鬆上手 著作權所有 © 旗標出版股份有限公司

本章提要 VBA 簡介 VBA 的程式架構 宣告變數與常數 VBA 的運算式 VBA 的條件判斷式 VBA 的迴圈控制 檢視及修改精靈產生的程式 將巨集轉為 VBA 程式

VBA 簡介 VBA (Visual Basic for Applications) 是在 Office 中所使用的程式語言, 可用來存取 Access、Word、Excel 及 Outlook 中的物件。 用在 Access 時, 可進行運算、檢查、設定或控制等自動化操作, 以加強資料處理能力、設計更友善的操作界面並製作出效率更佳、更有彈性的資料庫應用系統。

VBA 簡介 請開啟書附光碟 "Ch21範例資料.mdb" 中的選擇表單：

VBA 簡介

VBA 簡介 這是利用 VBA 建立的功能, 請切換到表單的設計檢視畫面, 並按下程式碼工具鈕 , 我們來看看這個表單的 VBA 程式碼：

VBA 簡介 雖然我們也可使用巨集來進行一些自動化的處理, 但它有以下的缺點： 巨集中可執行的指令有限 (只有 56 個指令)。 流程控制及錯誤處理機制不夠靈活。 必須獨立存放 (無法儲存於所屬的表單或報表中), 因此當資料庫的功能較複雜時, 會變得不容易維護。

VBA 簡介 如果我們要開發一套完整的資料庫應用系統, 巨集就顯得有點力不從心了, 此時還是得使用功能強大而有彈性的 VBA 才行。

VBA 的程式架構 接著我們來認識 VBA 的程式架構, 以了解 VBA 程式是如何組織起來的。請先參考下面的架構圖：

VBA 的程式架構 這裡的『資料庫』就是指一個 Access 的資料庫檔案, 每個資料庫中都有一個 VBA 專案, 其中可包含許多的『模組』 (Module), 而模組就是程式碼所儲存的地方。 Access 的模組分為 2 大類：物件類別模組 (class module) 和一般模組 (standard module, 通常都簡稱為模組)。

物件類別模組 Access 有 3 種物件類別模組, 分別是： 表單類別模組 (form class module) 報表類別模組 (report class module) 自訂類別模組 (custom class module)

物件類別模組 當我們新增表單或報表時, Access 便會自動建立表單類別模組或報表類別模組, 也就是說每一個表單或報表都有專屬的物件類別模組, 用來儲存與該表單或報表相關的程序、函數或事件程序 (事件程序就是當某事件發生時所要執行的程序)。

物件類別模組 這類的物件類別模組不僅是由 Access 自動建立的, 而且有制式的名稱：『Form_ 表單名稱』或『Report_報表名稱』。例如書籍表單的物件類別模組即為『Form_書籍』, 而出貨單報表的則為『Report_出貨單』。

物件類別模組 請注意, 物件類別模組在資料庫視窗的模組頁次中是找不到的, 因為它們是儲存在表單或報表物件之中, 我們必須用 Visual Basic 編輯器才看得到。接續前例：

物件類別模組

一般模組 就是由我們在資料庫視窗模組頁次中所建立的模組, 模組名稱可以自訂, 而其內則可存放程序或函數, 但不能存放事件程序。 在一般模組中定義的程序或函數可供整個資料庫使用。也就是說在整個資料庫中, 您都可以自由使用一般模組內的程序與函數 (除非有特別的宣告限制)。

一般模組

看不到專案總管、屬性視窗 如果看不到專案總管或屬性視窗, 可執行『檢視』功能表中的『專案總管』或『屬性視窗』命令, 或按工具鈕將之顯示出來：

看不到專案總管、屬性視窗

看不到專案總管、屬性視窗 資料庫名稱就是指資料庫檔案 (*.mdb) 的名稱, 而專案名稱則預設會和資料庫名稱相同。但如果更改資料庫的檔案名稱, 專案名稱並不會跟著改變。

模組中的程式單元 在任何模組中都可包含許多的程式單元, 每個程式單元則須指定一個名稱以方便執行。常用的程式單元有 3 種, 分別是： 程序 (Sub procedure) 函數 (Function procedure) 事件程序 (Event procedure)

模組中的程式單元 程序：由程式設計師為了特殊目的而撰寫的程式, 例如計算薪資、比對資料、檢查資料正確性等。程序是以 Sub 為開頭, End Sub 為結尾, 例如：

模組中的程式單元 其中『預覽報表』就是程序的名稱, 而小括號中則為參數；如果有多個參數則要以逗號分開, 若沒有參數則小括號可省略。

模組中的程式單元 函數：功能和程序相同, 但函數還可以傳回運算的結果, 因此常使用於運算式中。函數是以 Function...End Function 來定義, 例如底下的 IfNull 函數：

函數傳回值的資料型態 我們亦可替函數的傳回值指定適當的資料型態：

模組中的程式單元 事件程序：事件程序只會出現在表單、報表的物件類別模組中, 用來撰寫當表單、報表、或控制項發生事件時所要執行的動作, 例如當表單中名為印出貨單的命令鈕被按下時所要執行的程序：

Private vs Public 在程序、函數、或事件程序之前加上 Private 關鍵字, 表示該程式只能在所屬的模組中使用, 而不能在模組以外的地方呼叫。若不加 Private 或加上 Public 關鍵字, 那麼該程式就是公用的, 可以在任何地方呼叫使用。 由於事件程序通常只在所屬的模組中才能運作, 因此 Access 會自動為它加上 Private 關鍵字。

宣告變數與常數 在 VBA 中, 資料可分為變數及常數兩種, 所謂變數, 就是該資料的值會隨著程式的執行而改變；而常數的值則始終保持一致, 不會因為程式的執行而改變。

變數 變數可用來暫存資料, 在 VBA 中是用 Dim 來宣告變數, 例如： 若用 Dim 來宣告多個變數, 則各變數之間要用逗號分開。變數可以用 As 來宣告為各種不同的資料類型, 包括：

變數

變數 以上的資料類型可視需要來選用, 若不指定類型則預設為 Variant, 此時該變數就可用來儲存任何類型的資料, 而不必經過類型轉換。其他類型的變數則在必要時須經過轉換, 例如：

變數 Variant 的執行效率較差, 但使用方便, 所以如果不知道該使用哪種資料類型, 就使用 Variant 吧！另外, 程序或函數的參數也可以指定資料類型, 例如：

常數 在我們的程式中, 有些資料的值是永遠不會改變的, 例如圓周率π= 3.14159。為了方便使用這些固定的數字或文字, 我們可用 Const 將其宣告為常數：

常數 常數在宣告時必須指定初值, 其值在宣告後就不能再改變了。另外, 系統也預先定義了許多常數, 以供我們在執行函數或物件的方法時設定參數之用。例如：

變數、程序及函數的命名規則 名稱中可以使用中文、字母 (a-z 或 A-Z)、數字 (0-9) 或底線 (_)。 名稱是不分大小寫的, 所以 Variable、VARIABLE、VaRiAblE 都表示同一個字。 名稱中不能有空白或標點符號 (逗點、分號、句點等)。

變數、程序及函數的命名規則 第一個字元不可為數字, 例如 49Team 就是錯誤的變數名稱。 名稱最長可達 255 個英文字元。 不可使用保留字或內建的函數名稱, 例如 Integer、Dim、If、And、Date、MsgBox...等。

VBA 的運算式 何謂運算式 運算元 運算子的種類

何謂運算式 運算式是命令電腦進行資料運算的基礎, 主要應用在 VBA 的判斷流程與迴圈, 或 Access 中篩選準則的設定及 SQL 指令。由運算子與運算元所組成, 而運算的結果稱為值。 其中運算子可當作動詞, 表示要執行何種運算或指令；運算元則是該運算執行時必須提供的資料或處理的對象。例如：

運算元 運算元是在運算式中給運算子當作處理對象的單元, 只要能夠提供值, 就可當成運算元：

運算子的種類 邏輯運算子 連結運算子 比較運算子 算術運算子

邏輯運算子 邏輯運算就是『是/否』資料類型, 運算的結果只有 True (是、Yes、On) 或 False (否、No、Off) 兩種。這是邏輯上的運算, 通常用在巨集、模組中的條件判斷式。共有 6 種： Not 運算子 Xor 運算子 And 運算子 Eqv 運算子 Or 運算子 Imp 運算子

邏輯運算子 Not 運算子：此運算子會將運算元的值反向。如果運算元的值為True 時, 運算結果便為 False; 反過來 False 時會得到 True：

邏輯運算子 And 運算子：這是『且』的意思, 表示 2 個運算元都必須都是肯定(True) 時, 運算的結果才是 True。

邏輯運算子 Or 運算子：這是『或』的意思, 表示只要有一個運算元為肯定 (True) 時, 運算結果就是 True。

邏輯運算子 Xor 運算子：這個運算子稱為『互斥』。只要兩個運算元的值不一樣, 其結果就是 True；若相同則為 False。

邏輯運算子 Eqv 運算子：這個運算子稱為『等價』, 只要兩個運算元的值皆為 True 或皆為 False, 其結果就是 True; 若兩者不同則為 False。

邏輯運算子 Imp 運算子：Imp 和 Eqv 是比較不常用的運算子, 其中 Imp 也較難理解, 您可以用 "運算元_1" 作為主要的依據： 1. "運算元_1" 為 True 時, 運算式的結果與 "運算元_2" 相同。 2. "運算元_1" 為 False 時, 運算式的結果都是 True。 3. "運算元_1" 為 Null 時, 只有 "運算元_2" 為 True 時, 運算式的結果才是 True, 否則都是 Null。

邏輯運算子

連結運算子 連結運算子是用來將兩個字串合併成單一字串。這類的運算子只有 2 個, 分別是 "+" 與 "&"。 其中 "+" 又是算術運算子中的相加, 所以如果運算元同為數字時, 就將數值相加；同為字串時, 則自動將字串合併。例如：

連結運算子 不過為了增加運算式的可讀性, 並避免發生混淆, 建議您如果確切知道運算元的型態是字串時, 盡量使用 "&"; 只有在不確定運算元型態下才使用 "+"。

比較運算子 比較運算子需要 2 個運算元作為比較的依據, 其運算元的種類多是數字、字串, 但比較的結果會以布林值 (Boolean) 來表示。以下分別說明常見的 6 個運算子： = (等於) >= (大於等於) <> (不等於) > (大於) < (小於) <= (小於等於)

比較運算子 ＝ (等於)：兩個運算元是否完全一樣。是則為 True；否則為 False。例如：

比較運算子 < (小於)：左側運算元的數值比右側的小, 其值為真, 反之為否。字串時則比較字元在 ASCII 的值, 第一個字元相同時, 則比較下一個。例如：

比較運算子 >= (大於等於)：左側的值大於或等於右側的值時, 結果為 True, 否則為 False：

比較運算子 <= (小於等於)：左側的值小於或等於右側的值時, 結果為 True, 否則為 False。例如：

比較運算子 Is 運算子：『Is』和 "=" 相同, 都是比較左右兩側是否相同, 是則為True , 否則為 False。不同的地方是『Is』的運算元必須是物件的名稱或變數, 所以它的意思為兩側是否為相同物件。例如：

比較運算子 Like 運算子：『Like』運算子不是和其他的值比較, 而是檢查字串是否符合設定的條件。如果符合, 其結果為 True, 否則為 False。其格式如下：

算術運算子 算術運算子應該是讀者最熟悉的運算符號, 除了前面已經介紹過的"+" 外, 還有" -", "*" , "/", "\", "^", "Mod"等 6 個, 其中前 3 個分別是減、乘、除運算, 就不再介紹。其他 3 個分別說明如下：

算術運算子 \ 與 Mod：這兩個都是做除法運算, 與 / 不同的是這兩個只做到整數除法, 且\ 是取其商數, 而 Mod 則取其餘數。例如： ^：指數運算元, 左側為底數, 右側為指數。例如: 2^5 表示 2 的5 次方為 32。

運算子的優先順序 1. 算術 > 比較 > 邏輯：首先以運算子的種類來分, 其最優先的是算術運算子, 排最後的是邏輯運算子。 2. 優先表：屬於同一類的運算子, 以下表來決定執行的優先順序：

運算子的優先順序 在表中的位置愈高, 表示較優先運算。其中 *、/ 在相同位置, 此時就由左至右運算；+ 和 - 也是同樣的情形。

運算子優先順序的例子 您可以看看這個例子： 若是怕運算子太多而眼花撩亂, 可以使用括號就一清二楚了：

VBA 的條件判斷式 在程式中, 我們可用條件判斷式來改變程式的執行流程, 例如依照某欄位的值來決定是否要採用不同的處理程序。VBA 的條件判斷式有： If Select

If 判斷式 If 判斷式就是 If...Then...Else..., 其基本結構如下：

If 判斷式 如果當條件式為 False 時不需要執行任何程式, 那麼 Else 就可以省略。此時若條件式中只有一行要處理的程式, 那麼也可簡寫為 "If 條件運算式 Then 程式", 例如 "If a > 5 Then b = 10"。 另外, 如果有許多條件要依序判斷, 則可用 If 搭配 ElseIf 來處理, 例如依月薪高低來推算等級：

If 判斷式

Select 判斷式 多條件判斷的流程控制除了使用 If 之外, 也可以使用『Select Case...End Select』指令, 它是根據同一個變數的不同值來做判斷。其語法格式如下：

Select 判斷式 其中『Case Else』的部分必須放在最後, 表示當前面的 Case 都不成立時, 就執行這部分的程式 (『Case Else』若不需要可以省略)。在『Case 變數的值』中, "變數的值" 可有多種表示方法, 例如：

Select 判斷式

Select 判斷式 系統會由上到下依序檢查每個 Case 的條件, 遇到第 1 個符合的Case 時就執行其所屬的程式, 並略過其餘的檢查。所以萬一資料同時符合多個不同的 Case, 那麼只會執行第一個符合的 Case。

VBA 的迴圈控制 當我們需要重複執行同一段程式碼的時候, 就可以利用迴圈控制。在 VBA 中共有二種迴圈： For…Next Do...Loop

For...Next 迴圈 For...Next 迴圈適合在已知執行次數的狀況下使用, 例如從 3 加到 10 的總和、計算九九乘法表等。其語法格式如下：

For...Next 迴圈 其中控制變數就是判斷是否繼續執行迴圈的依據。一開始先把控制變數設為起始值, 並檢查變數是否小於或等於終點值。如果是就執行其內的程式, 執行結束後會自動把控制變數的值加上差距值, 然後再次與終點值比較, 以決定是否再次執行程式或是離開迴圈。

For...Next 迴圈 底下我們以計算由 3 加到 9 為例：

For … Next 迴圈

For … Next 迴圈 在 For 迴圈中若要強制跳出迴圈, 可使用 Exit For 指令。例如：

Do While… Loop 迴圈 當執行迴圈的次數與條件判斷有關的時候, 我們就可使用 Do…Loop 迴圈。這個迴圈指令有 3 種用法： Do…Loop While Do Until…Loop

Do While… Loop Do While...Loop：當條件為 True 時, 就會不斷執行迴圈內的程式。其語法如下： 當第一次進入迴圈或要重複執行迴圈時, 都會檢查條件運算式是否為真, 若為假則離開迴圈, 然後繼續執行迴圈之後的程式。

Do…Loop While Do...Loop While：無論如何都先執行一次迴圈中的程式, 然後再依條件決定是否要重複執行。其語法如下：

Do…Loop While 這樣的寫法由於是將條件式放在迴圈的最後, 在撰寫或閱讀時較為不便, 因此 VBA 提供另外一種寫法： 這二種寫法是相同的, 但以While...Wend較為常用。

Do Until…Loop Do Until...Loop：表示『直到條件為真, 才會離開迴圈』, 也就是說, 在條件運算式為假時會重複執行迴圈, 而在為真時反而會離開迴圈。其格式如下：

Do Until…Loop 當我們在設計以上 3 種 Do...Loop 迴圈時, 如果中途跳出迴圈, 可以使用 Exit Do 指令 (但 While...Wend 則不可使用), 例如：

Exit Sub 及 Exit Function 除了 Exit For 及 Exit Do 可以跳出 For…Next 及 Do…Loop 迴圈外, 還可使用 Exit Sub、Exit Function 來跳出程序或函數：

檢視及修改精靈產生的程式 請開啟客戶表單：

檢視及修改精靈產生的程式

錯誤處理 On Error Goto 標籤名稱：表示當程序或函數在執行發生錯誤時, 例如企圖開啟一個不存在的表單, 那麼要立即跳到 (Goto) 標籤的位置繼續執行。在該標籤處通常會擺放處理錯誤的程式碼, 而當錯誤處理完後, 則可用 "Resume 標籤名稱" 再跳到其他的程式去繼續執行。

錯誤處理 定義標籤：標籤是用來標示程式中的某個位置, 以便我們使用 "On Error Goto" 來指定要跳到的位置。其語法是一個標籤名稱後面跟著一個半型的冒號, 例如 "錯誤處理:"。

錯誤處理 錯誤處理程式：就是當錯誤發生時所要執行的程式, 在本例中則是開啟一個交談窗來顯示錯誤訊息：MsgBox Err.Description。其中 Err 是一個『錯誤處理物件』, 當發生錯誤時, 它的 Description 屬性中會存放相關的錯誤訊息。

錯誤發生時的流程

為什麼要處理錯誤？ 當程式執行時發生非語法上的錯誤時, 會立即顯示訊息然後結束程式。然而我們通常不希望程式被意外地結束, 因為可能還有一些善後的動作尚未執行, 或是我們要自行針對錯誤做一些處理。此時就可用 On Error 來攔截錯誤, 程式就不會被中斷了。

為什麼要處理錯誤？ 如果只想將錯誤忽略掉, 可用 "On Error Resume Next" 指令。另外若想取消目前的 On Error 設定, 則可用 "On Error Goto 0" 指令。

用 Docmd 執行巨集指令 在 VBA 程式中我們可用 Docmd 物件來執行巨集指令, 例如前面的 DoCmd.OpenReport 的功能就和巨集中的 OpenReport 指令一樣, 連參數都相同。

將巨集轉為 VBA 程式 巨集最大的缺點, 就是不能和所屬的表單或報表存放在一起, 而且也無法攔截執行時期的錯誤；而其優點則是易學易用, 不必寫程式。 Access 提供了先設計所需的巨集, 然後將之轉換成 VBA 程式碼, 再將之拷貝到相關的表單或報表模組中。如此不但不必寫程式, 又可享有 VBA 程式易維護、執行效率高的優點呢！

將巨集轉為 VBA 程式 請在前例的資料庫視窗中選取檢查客戶(精簡版) 巨集, 然後執行『工具/巨集/將巨集轉換成 Visual Basic』命令, 然後：

將巨集轉為 VBA 程式

將巨集轉為 VBA 程式 底下我們將巨集與程式內容都列出來供您比較：

將巨集轉為 VBA 程式