第19章 VBA程式設計 19-1 VBA的基礎 19-2 建立VBA程式 19-3 VBA的程序與函數 19-4 VBA的變數與資料型態 19-7 Access的物件 19-8 公用模組的說明

19-1 VBA的基礎 19-1-1 微軟的VBA 19-1-2 VBA的編輯程式

19-1-1 微軟的VBA-說明 「VBA」（Visual Basic for Applications）是Office家族支援的程式語言，主要的目的是讓使用者能夠輕鬆擴充Office軟體的功能。 VBA是使用Visual Basic語法的程式語言，這是一種附屬在Office應用程式的語言，例如：Word、Excel和Acces等。 以本書的Access來說，VBA可以讓使用者撰寫程式碼存取Access物件，執行計算、欄位驗證和設定操作等重複性工作。提供資料庫系統更佳的操作環境，以符合使用者的需求。

19-1-1 微軟的VBA-種類 Access的VBA程式稱為【模組】物件，簡單的說，模組是由VBA宣告、程序或函數所組成的集合，在資料庫的VBA專案可以顯示模組清單，分為Microsoft Access物件的類別模組和一般模組2種基本類型，如下所示： 類別模組 一般模組

19-1-1 微軟的VBA-類別模組 類別模組 在Access資料庫建立的表單或報表物件擁有對應的表單和報表模組。 表單和報表模組是存放表單或報表的事件程序。當使用者在表單和報表執行動作產生事件時，就是使用事件程序進行處理。

19-1-1 微軟的VBA-一般模組 一般模組與其它物件無關，它是在資料庫的【模組】清單新增的模組，通常是一些資料庫經常處理的函數或操作程序，其中宣告成Public的程序與函數可以在整個資料庫的任何地方被其它模組呼叫，如果是函數還可以在運算式中使用。 因為一般模組與物件無關，所以建立的程序與函數並不能在控制項屬性的事件作為事件程序。

19-1-2 VBA的編輯程式 VBA程式碼的模組設計檢視會啟動VBA編輯程式進行編輯，在開啟的Access資料庫選【模組】，按上方的【新增】鈕就可以啟動VBA編輯程式，如下圖所示：

19-2 建立VBA程式 19-2-1 在Access新增與執行模組 19-2-2 使用巨集執行VBA函數

19-2-1 在Access新增與執行模組 在Access建立模組物件就是在撰寫VBA程式碼，在本節範例的VBA函數很簡單，只是顯示【第一個VBA程式】訊息文字的視窗後，然後傳回True值。

19-2-2 使用巨集執行VBA函數 在Access新增模組的函數（程序Sub不行）後，在【運算式建立幫手】可以選擇函數來建立運算式，換句話說，Access物件只要是可以使用運算式的地方，就可以呼叫和執行建立的模組函數。

19-3 VBA的程序與函數-說明 VBA模組的程式碼單位是Sub程序或Function函數，每一個程式碼單位需要指定一個名稱，以便在運算式或事件處理時可以使用名稱來呼叫程序或函數。

19-3 VBA的程序與函數-程序與函數1 Sub副程序 Sub程序是一個程式區塊的VBA程式碼，使用Sub和End Sub包圍，程序並沒有傳回值，在括號中可以加上傳入參數，如下所示： Public Sub ShowMsg() MsgBox "第一個VBA程式" End Sub

19-3 VBA的程序與函數-程序與函數2 Function函數 Function函數只是改為Function和End Function包圍，括號中可以加上傳入參數，函數需要傳回值，函數的傳回值是將函數名稱指定成傳回值，如下所示： Private Function Add2N(MaxValue As Integer) As Integer Dim i, TotalValue As Integer For i = 1 To MaxValue Step 1 TotalValue = TotalValue + i Next Add2N = TotalValue End Function

19-3 VBA的程序與函數-程序與函數3 程式和函數的存取修飾子 在程序和函數之前可以加上存取修飾子Private和Public，其存取範圍的說明，如下所示： Private：程序與函數只可以在同一個模組中使用，不能在其它模組進行呼叫。 Public：程序與函數可以在資料庫的任何地方進行呼叫。

19-3 VBA的程序與函數-呼叫程序與函數 在其它模組的VBA程式碼可以呼叫函數或副程序，只有呼叫程序時才需要使用Call，如下所示： Call ShowMsg() 函數因為擁有傳回值，所以通常是使用指定敘述方式來進行呼叫，並且是放在指定敘述的右邊，如下所示： TotalValue = Add2N(10)

19-3 VBA的程序與函數-跳出程序與函數 我們可以中斷程序或函數的執行，程序使用的指令，如下所示： 如為函數，其使用的指令如下所示： Exit Sub 如為函數，其使用的指令如下所示： Exit Function

19-3 VBA的程序與函數-內建函數1 DLookUp()函數

19-3 VBA的程序與函數-內建函數2 DCount()函數 DCount()函數可以在指定的記錄範圍中計算其記錄數，函數的參數和DLookUP()相同，如下所示： DCount(運算式, 資料來源, 條件) 上述DCount()函數的使用範例，如下所示： DCount("客戶編號","客戶訂單查詢")

19-4 VBA的變數與資料型態 19-4-1 變數型態與宣告 19-4-2 指定敘述

19-4-1 變數型態與宣告-變數的宣告1 變數在程式中可以儲存執行的暫存資料，它的命名原則，如下所示： 不能超過255字元，而且不區分大小寫。 名稱中間不能有標點符號的句點、分號、逗號或空白，而且第1個字元不可以是數字。 不能使用Access和VBA的關鍵字和內建函數的名稱。 在VBA程式使用【Dim】指令宣告變數，使用【As】指定資料型態，如下所示： Dim j, TotalValue As Integer Dim str As String

19-4-1 變數型態與宣告-變數的宣告2 如果同時宣告的變數不只一個，請使用”,”逗號分隔，變數str為字串，如果沒有使用As，如下所示： Dim i, Count 上述程式碼宣告2個資料型態為Variant的變數，可以儲存任何資料型態的資料。 事實上，VBA程式碼的變數不需要宣告也可以直接在指定敘述使用，只要有需要就可以在程式碼中使用變數，如此常常會造成程式維護的困擾，為了要求程式碼中的每一個變數都需要事先宣告，請使用下列指令，如下所示： Option Explicit

19-4-1 變數型態與宣告-變數的資料型態

19-4-1 變數型態與宣告-常數的宣告 常數是使用一個名稱取代固定值的數字或字串，與其說是一個變數，不如說是名稱轉換，將一些值使用有意義的名稱取代，常數在宣告時同時需要指定其值，如下所示： Const PI As Single = 3.1415926 上述程式碼宣告圓周率的常數PI。

19-4-2 指定敘述 在宣告變數後就可以指定變數值，稱為指定敘述。指定敘述是”=”等號，其目的是指定變數的值，如下所示： i = 101 str = "無宗憲" 上述程式碼指定變數值，變數分別是整數和字串資料型態的變數。

19-5 VBA的運算子 19-5-1 運算子的優先順序 19-5-2 算術與字串運算子 19-5-3 比較運算子 19-5-4 邏輯運算子

19-5 VBA的運算子 VBA指定敘述的等號右邊若為運算式或條件運算式都是由運算子和運算元所組成，VBA擁有算術、比較、連結和邏輯運算子，如下所示： A + B – 1 A >= B A > B And A > 1 上述運算式中A、B變數和數值1都是運算元，+、-為運算子。

19-5-1 運算子的優先順序 正常的情況，如果沒有優先順序的差異，運算式依照出現的順序，由左到右依序執行。 括號內比括號外的先執行，通常括號的目的是為了推翻現有的優先順序，在括號內是依照正常的優先順序執行。 當運算式超過一個運算子時，算術運算子最先，接著是比較運算子，最後才是邏輯運算子。 對於運算子內的各種運算，比較運算子的優先順序相同，算術和邏輯運算子，請參考後面表格，位在前面列的優先順序比較高，就是先執行。 算術運算子中加和減法優先順序相同，乘和除法擁有相同的優先順序，不過乘除高於加減。

19-5-2 算術與字串運算子 字串連結運算子”&”並不是算術運算子，它的優先順序在算術運算子之後，在比較運算子之前，運算子依照優先順序排序，如下表所示：

19-5-3 比較運算子 比較運算子沒有優先順序的分別，通常都是使用在迴圈和條件敘述的判斷條件，其中Is運算子並非比較物件，而是檢查2個物件是否參考到相同物件，如下表所示：

19-5-4 邏輯運算子 如果迴圈和條件敘述的判斷條件不只一個，需要使用邏輯運算子來連結多個條件，運算子依照優先順序排序，如下表所示：

19-6 VBA的流程控制指令 19-6-1 VBA的條件控制指令 19-6-2 VBA的迴圈控制指令

19-6 VBA的流程控制指令 程式碼的流程控制只是配合條件判斷來執行不同區塊的程式碼，或是像迴圈一般重複執行區塊的程式碼，流程控制指令主要分為2類，如下所示： 條件控制：條件控制是一個選擇題，可能為單一選擇或多選一，依照條件運算子的結果，決定執行哪一個區塊的程式碼。 迴圈控制：迴圈控制是重複執行區塊的程式碼，擁有結束條件可以結束迴圈的執行。

19-6-1 VBA的條件控制指令-If/Then條件敘述 If/Then條件敘述是一種是否執行的條件，決定是否執行區塊內的程式碼，如果If條件為True，就執行Then...End If間的程式碼，如下所示： If TestValue > 0 Then UserName = "無宗憲" End If

19-6-1 VBA的條件控制指令-If/Then/Else條件敘述 If/Then條件只是執行或不執行選擇的單一選擇，不只如此，如果有排它的2個執行區塊需要二選一，只需加上Else即可，如果If條件為True，就執行Then...Else間的程式碼，False執行Else...End If間的程式碼，如下所示： If TestValue > 0 Then UserName = "無宗憲" Else UserName = "胡瓜" End If

19-6-1 VBA的條件控制指令-多選一的條件敘述(If/Then/ElseIf條件敘述) If/Then/ElseIf條件敘述是If/Then條件敘述的延伸，使用ElseIf指令建立多選一條件，如下所示： If thisDay = 1 Then str="星期日" ElseIf thisDay = 2 Then str="星期一" ElseIf thisDay = 3 Then str="星期二" ElseIf thisDay = 4 Then str="星期三" ElseIf thisDay = 5 Then str="星期四" ElseIf thisDay = 6 Then str="星期五" ElseIf thisDay = 7 Then str="星期六" Else Msgbox "無法分辨星期" End If

19-6-1 VBA的條件控制指令-多選一的條件敘述(Select/Case條件敘述) Select Case thisDay Case 1: str="星期日" Case 2: str="星期一" Case 3: str="星期二" Case 4: str="星期三" Case 5: str="星期四" Case 6: str="星期五" Case 7: str="星期六" Case Else Msgbox "無法分辨星期" End Select

19-6-2 VBA的迴圈控制指令-For/Step/Next迴圈 For/Step/Next迴圈敘述可以執行固定次數的迴圈，以Step量增加或減少，如果Step為1可以省略Step指令，例如：使用For/Step/Next迴圈每次增加1，執行1到10次相加的迴圈，如下所示： Dim i, Total For i = 1 To 10 Step 1 Total = Total + i Next 上述For/Step/Next迴圈是從1加到10計算總和，如果使用負數的Step，如下所示： For i = 10 To 1 Step -1

19-6-2 VBA的迴圈控制指令-For Each/In/Next迴圈 For Each迴圈和For Next迴圈敘述十分相似，只不過迴圈主要是使用在物件和集合物件用來顯示所有元素，特別適合哪些不知道共有多少元素的集合物件，如下所示： Public Sub ClearTextField(frm As Form) Dim ctl As Control For Each ctl In frm.Controls If ctl.ControlType = acTextBox Then ctl.Value = "" End If Next ctl End Sub

19-6-2 VBA的迴圈控制指令- Do/While...Until/Loop迴圈1 Do/While...Until/Loop迴圈擁有多種組合，當迴圈執行到條件為False時，可以在迴圈開始或結束使用While或Until測試迴圈條件，如果在迴圈尾測試條件，迴圈至少執行一次，請注意！這種迴圈需要自己處理迴圈的結束條件和計數器。

19-6-2 VBA的迴圈控制指令- Do/While...Until/Loop迴圈2 Do/Loop迴圈使用While條件，條件是在迴圈開頭時檢查，如下所示： i = 1 Total = 0 Do While i <=10 Total = Total + i i = i + 1 Loop

19-6-2 VBA的迴圈控制指令- Do/While...Until/Loop迴圈3 Do/Loop迴圈使用Until條件，條件是在迴圈尾進行檢查，如下所示： i = 1 Total = 0 Do Total = Total + i i = i + 1 Loop Until i > 10

19-6-2 VBA的迴圈控制指令- While/Wend迴圈 While/Wend迴圈控制是在迴圈開始時測試條件，以決定是否繼續執行迴圈的程式碼，在功能上和Do/Loop迴圈相同，如下所示： i = 1 Total = 0 While i <= 10 Total = Total + i i = i + 1 Wend

19-6-2 VBA的迴圈控制指令- Exit Do/For指令1 Exit For：跳離For/Next迴圈 迴圈在尚來到達結束條件時，可以使用Exit For指令強迫跳出For/Next迴圈，結束迴圈的執行，如下所示： For i = 1 To 100 Step 1 … Exit For Next

19-6-2 VBA的迴圈控制指令- Exit Do/For指令2 Exit Do：跳離Do/Loop迴圈 如果沒有使用While或Until指令在迴圈頭尾測試條件，單純的Do/Loop迴圈是一個無窮迴圈，此時可以使用Exit Do指令結束迴圈的執行，如下所示： Do …. Exit Do Loop

19-7 使用Access的物件-說明 Access物件模型是從最上層的Application物件開始，Application物件就是Access建立的應用程式，在其下擁有幾種主要物件，如下表所示：

19-7 使用Access的物件-Form和Report物件 VBA程式碼可以使用Form和Report物件存取表單和報表物件的控制項內容或屬性值，如下所示： curSource = Forms(“表單名稱”).Controls(“控制項名稱”).控制項屬性 curSource = Forms(“表單名稱”)!控制項名稱.控制項屬性 curSource = Forms!表單名稱!控制項名稱.控制項屬性 上述3列程式碼都可以取得表單物件指定控制項的屬性值，如果表單擁有子表單，此時的程式碼如下所示： curQuantity = Forms(“表單名稱”).Controls(“子表單名稱”).Form!控制項名稱 curQuantity = Forms!表單名稱!子表單名稱.Form!控制項名稱

19-7 使用Access的物件-DoCmd物件 DoCmd物件可以在VBA程式碼執行巨集指令，例如：開啟報表物件，其程式碼如下所示： Dim strFormName As String strFormName = “表單名稱” DoCmd.OpenForm strFormName, acNormal 上述程式碼使用正常方式開啟表單物件，acNormal是巨集指令引數的常數，如果需要關閉此表單且不儲存，其程式碼如下所示： DoCmd.Close acForm, strFormName, acSaveNo

19-8 公用模組的說明-IsLoaded()函數1

19-8 公用模組的說明-IsLoaded()函數2 Function IsLoaded(ByVal strFormName As String) As Boolean ' Returns True if the specified form is open in Form view or Datasheet view. Const conObjStateClosed = 0 Const conDesignView = 0 If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then If Forms(strFormName).CurrentView <> conDesignView Then IsLoaded = True End If End Function

19-8 公用模組的說明-CheckRecords()函數1 CheckRecords()函數使用DCount()函數檢查指定查詢物件是否找到任何記錄，其語法如下所示： CheckRecords(“欄位名稱”, “查詢物件名稱”)

19-8 公用模組的說明-CheckRecords()函數2 Function CheckRecords(ByVal ControlName As String, strQueryName As String) As Boolean Dim DCounters As Integer DCounters = DCount(ControlName, strQueryName) If DCounters = 0 Then CheckRecords = True Else CheckRecords = False End If End Function

19-8 公用模組的說明-IsReportLoaded()函數1

19-8 公用模組的說明-IsReportLoaded()函數2 Function IsReportLoaded(ByVal ReportNames As String) As Boolean Const ObjStateClosed = 0 If SysCmd(acSysCmdGetObjectState, acReport, ReportNames) <> ObjStateClose Then IsReportLoaded = True End If End Function