VBA 快速指南

---

---

VBA - 概述

VBA 代表Visual Basic for Applications，是微軟的一種事件驅動程式語言，現在主要用於微軟辦公應用程式，如 MS Excel、MS Word 和 MS Access。

它幫助技術人員構建自定義應用程式和解決方案，以增強這些應用程式的功能。此功能的優點是您無需在 PC 上安裝 Visual Basic，但是，安裝 Office 將隱式地幫助實現此目的。

您可以在所有 Office 版本中使用 VBA，從 MS-Office 97 到 MS-Office 2013，以及任何最新的可用版本。在 VBA 中，Excel VBA 最受歡迎。使用 VBA 的優勢在於您可以使用線性規劃在 MS Excel 中構建非常強大的工具。

VBA 的應用

您可能想知道為什麼要在 Excel 中使用 VBA，因為 MS-Excel 本身提供了大量的內建函式。MS-Excel 只提供基本的內建函式，這些函式可能不足以執行復雜的計算。在這種情況下，VBA 成為最明顯的解決方案。

例如，使用 Excel 的內建公式很難計算貸款的月還款額。相反，為這種計算編寫 VBA 比較容易。

訪問 VBA 編輯器

在 Excel 視窗中，按“ALT+F11”。將開啟一個 VBA 視窗，如下面的螢幕截圖所示。

VBA - Excel 宏

在本章中，您將學習如何逐步編寫簡單的宏。

步驟 1 - 首先，在 Excel 20XX 中啟用“開發工具”選單。為此，請單擊“檔案”→“選項”。

步驟 2 - 單擊“自定義功能區”選項卡，然後選中“開發工具”。單擊“確定”。

步驟 3 - “開發工具”功能區將出現在選單欄中。

步驟 4 - 單擊“Visual Basic”按鈕以開啟 VBA 編輯器。

步驟 5 - 透過新增按鈕開始編寫指令碼。單擊“插入”→選擇按鈕。

步驟 6 - 執行右鍵單擊並選擇“屬性”。

步驟 7 - 編輯名稱和標題，如下面的螢幕截圖所示。

步驟 8 - 現在雙擊按鈕，將顯示子過程大綱，如下面的螢幕截圖所示。

步驟 9 - 透過簡單地新增訊息開始編碼。

Private Sub say_helloworld_Click()
   MsgBox "Hi"
End Sub

步驟 10 - 單擊按鈕以執行子過程。子過程的輸出顯示在下面的螢幕截圖中。確保您的設計模式已開啟。如果未開啟，只需單擊即可開啟。

注意 - 在後面的章節中，我們將演示如何使用簡單的按鈕，如步驟 1 到 10 所述。因此，徹底理解本章很重要。

VBA - Excel 術語

在本章中，您將熟悉常用的 Excel VBA 術語。這些術語將在後續模組中使用，因此瞭解每一個術語都很重要。

模組

模組是編寫程式碼的區域。這是一個新的工作簿，因此沒有任何模組。

要插入模組，請導航到“插入”→“模組”。插入模組後，將建立“模組 1”。

在模組中，我們可以編寫 VBA 程式碼，程式碼寫在過程中。過程/子過程是一系列 VBA 語句，指示要執行的操作。

過程

過程是一組整體執行的語句，指示 Excel 如何執行特定任務。執行的任務可以是非常簡單的任務，也可以是非常複雜的任務。但是，最好將複雜的過程分解成較小的過程。

兩種主要型別的過程是 Sub 和 Function。

函式

函式是一組可重用的程式碼，可以在程式中的任何位置呼叫。這消除了反覆編寫相同程式碼的需要。這有助於程式設計師將大型程式分解成許多小型且易於管理的函式。

除了內建函式外，VBA 還允許編寫使用者定義的函式，語句寫在Function和End Function之間。

子過程

子過程的工作方式類似於函式。子過程不返回值，而函式可能返回也可能不返回值。子過程可以不用 Call 關鍵字呼叫。子過程始終包含在Sub和End Sub語句中。

VBA - 宏註釋

註釋用於記錄程式邏輯和使用者資訊，其他程式設計師將來可以無縫地處理相同的程式碼。

它包括開發人員、修改人員等資訊，還可以包括已合併的邏輯。直譯器在執行時會忽略註釋。

VBA 中的註釋用兩種方法表示。

• 任何以單引號 (') 開頭的語句都被視為註釋。以下是一個示例。

' This Script is invoked after successful login 
' Written by : TutorialsPoint 
' Return Value : True / False

• 任何以關鍵字“REM”開頭的語句。以下是一個示例。

REM This Script is written to Validate the Entered Input 
REM Modified by  : Tutorials point/user2

VBA - 訊息框

MsgBox 函式顯示一個訊息框，並等待使用者單擊一個按鈕，然後根據使用者單擊的按鈕執行操作。

語法

MsgBox(prompt[,buttons][,title][,helpfile,context])

引數說明

• Prompt - 必需引數。在對話方塊中顯示為訊息的字串。提示的最大長度約為 1024 個字元。如果訊息超過一行，則可以使用回車符 (Chr(13)) 或換行符 (Chr(10)) 在每一行之間分隔行。

• Buttons - 可選引數。一個數值表示式，指定要顯示的按鈕型別、要使用的圖示樣式、預設按鈕的標識以及訊息框的模式。如果留空，按鈕的預設值為 0。

• Title - 可選引數。在對話方塊的標題欄中顯示的字串表示式。如果標題留空，則應用程式名稱將放在標題欄中。

• Helpfile - 可選引數。一個字串表示式，標識用於為對話方塊提供上下文相關幫助的幫助檔案。

• Context - 可選引數。一個數值表示式，標識幫助作者分配給相應幫助主題的幫助上下文編號。如果提供了 context，則也必須提供 helpfile。

Buttons 引數可以採用以下任何值：

• 0 vbOKOnly - 只顯示“確定”按鈕。

• 1 vbOKCancel - 顯示“確定”和“取消”按鈕。

• 2 vbAbortRetryIgnore - 顯示“中止”、“重試”和“忽略”按鈕。

• 3 vbYesNoCancel - 顯示“是”、“否”和“取消”按鈕。

• 4 vbYesNo - 顯示“是”和“否”按鈕。

• 5 vbRetryCancel - 顯示“重試”和“取消”按鈕。

• 16 vbCritical - 顯示嚴重錯誤訊息圖示。

• 32 vbQuestion - 顯示警告查詢圖示。

• 48 vbExclamation - 顯示警告訊息圖示。

• 64 vbInformation - 顯示資訊訊息圖示。

• 0 vbDefaultButton1 - 第一個按鈕為預設按鈕。

• 256 vbDefaultButton2 - 第二個按鈕為預設按鈕。

• 512 vbDefaultButton3 - 第三個按鈕為預設按鈕。

• 768 vbDefaultButton4 - 第四個按鈕為預設按鈕。

• 0 vbApplicationModal 應用程式模式 - 在使用者響應訊息框之前，當前應用程式將無法工作。

• 4096 vbSystemModal 系統模式 - 在使用者響應訊息框之前，所有應用程式都將無法工作。

上述值在邏輯上分為四組：第一組（0 到 5）指示要在訊息框中顯示的按鈕。第二組（16、32、48、64）描述要顯示的圖示的樣式，第三組（0、256、512、768）指示哪個按鈕必須是預設按鈕，第四組（0、4096）確定訊息框的模式。

返回值

MsgBox 函式可以返回以下值之一，這些值可用於識別使用者在訊息框中單擊的按鈕。

• 1 - vbOK - 單擊了“確定”
• 2 - vbCancel - 單擊了“取消”
• 3 - vbAbort - 單擊了“中止”
• 4 - vbRetry - 單擊了“重試”
• 5 - vbIgnore - 單擊了“忽略”
• 6 - vbYes - 單擊了“是”
• 7 - vbNo - 單擊了“否”

示例

Function MessageBox_Demo() 
   'Message Box with just prompt message 
   MsgBox("Welcome")     
   
   'Message Box with title, yes no and cancel Butttons  
   int a = MsgBox("Do you like blue color?",3,"Choose options") 
   ' Assume that you press No Button  
   msgbox ("The Value of a is " & a) 
End Function

輸出

步驟 1 - 以上函式可以透過單擊 VBA 視窗上的“執行”按鈕或從 Excel 工作表呼叫函式來執行，如下面的螢幕截圖所示。

步驟 2 - 顯示一個簡單的訊息框，其中包含訊息“歡迎”和“確定”按鈕。

步驟 3 - 單擊“確定”後，將顯示另一個對話方塊，其中包含訊息以及“是”、“否”和“取消”按鈕。

步驟 4 - 單擊“否”按鈕後，該按鈕的值 (7) 將儲存為整數，並作為訊息框顯示給使用者，如下面的螢幕截圖所示。使用此值，可以瞭解使用者單擊了哪個按鈕。

VBA - InputBox

InputBox 函式提示使用者輸入值。輸入值後，如果使用者單擊“確定”按鈕或按鍵盤上的 ENTER 鍵，InputBox 函式將返回文字框中的文字。如果使用者單擊“取消”按鈕，則該函式將返回空字串 (“”)。

語法

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

引數說明

• Prompt - 必需引數。在對話方塊中顯示為訊息的字串。提示的最大長度約為 1024 個字元。如果訊息超過一行，則可以使用回車符 (Chr(13)) 或換行符 (Chr(10)) 在每一行之間分隔行。

• Title - 可選引數。在對話方塊的標題欄中顯示的字串表示式。如果標題留空，則應用程式名稱將放在標題欄中。

• Default - 可選引數。文字框中的預設文字，使用者希望顯示。

• XPos − 可選引數。X 軸的位置表示提示框距離螢幕左側水平方向的距離。如果留空，則輸入框水平居中。

• YPos − 可選引數。Y 軸的位置表示提示框距離螢幕左側垂直方向的距離。如果留空，則輸入框垂直居中。

• Helpfile − 可選引數。一個字串表示式，用於標識用於為對話方塊提供上下文相關幫助的幫助檔案。

• context − 可選引數。一個數值表示式，標識幫助作者分配給相應幫助主題的幫助上下文編號。如果提供 context，則也必須提供 helpfile。

示例

讓我們透過在執行時使用兩個輸入框（一個用於長度，一個用於寬度）從使用者處獲取值來計算矩形的面積。

Function findArea() 
   Dim Length As Double 
   Dim Width As Double 
   
   Length = InputBox("Enter Length ", "Enter a Number") 
   Width = InputBox("Enter Width", "Enter a Number") 
   findArea = Length * Width 
End Function

輸出

步驟 1 − 為執行此操作，請使用函式名稱呼叫並按 Enter 鍵，如下面的螢幕截圖所示。

步驟 2 − 執行後，將顯示第一個輸入框（長度）。在輸入框中輸入一個值。

步驟 3 − 輸入第一個值後，將顯示第二個輸入框（寬度）。

步驟 4 − 輸入第二個數字後，單擊“確定”按鈕。面積將顯示在下面的螢幕截圖中。

VBA - 變數

變數是用於儲存值的命名記憶體位置，該值可以在指令碼執行期間更改。以下是變數命名的基本規則。

• 必須使用字母作為第一個字元。

• 不能使用空格、句點 (.)、感嘆號 (!) 或字元 @、&、$、# 作為名稱的一部分。

• 名稱長度不能超過 255 個字元。

• 不能使用 Visual Basic 保留關鍵字作為變數名。

語法

在 VBA 中，需要在使用變數之前宣告它們。

Dim <<variable_name>> As <<variable_type>>

資料型別

VBA 有許多資料型別，可以分為兩大類：數值資料型別和非數值資料型別。

數值資料型別

下表顯示了數值資料型別和允許的值範圍。

非數值資料型別

下表顯示了非數值資料型別和允許的值範圍。

示例

讓我們建立一個按鈕，並將其命名為“Variables_demo”以演示變數的使用。

Private Sub say_helloworld_Click()
   Dim password As String
   password = "Admin#1"

   Dim num As Integer
   num = 1234

   Dim BirthDay As Date
   BirthDay = DateValue("30 / 10 / 2020")

   MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
      num & Chr(10) & "Value of Birthday is " & BirthDay
End Sub

輸出

執行指令碼後，輸出將如下面的螢幕截圖所示。

VBA - 常量

常量是用於儲存值的命名記憶體位置，在指令碼執行期間**不能**更改。如果使用者嘗試更改常量的值，則指令碼執行將導致錯誤。常量的宣告方式與變數的宣告方式相同。

以下是常量命名的規則。（與變數命名規則相同）

• 必須使用字母作為第一個字元。

• 不能使用空格、句點 (.)、感嘆號 (!) 或字元 @、&、$、# 作為名稱的一部分。

• 名稱長度不能超過 255 個字元。

• 不能使用 Visual Basic 保留關鍵字作為變數名。

語法

在 VBA 中，我們需要為已宣告的常量賦值。如果我們嘗試更改常量的值，則會丟擲錯誤。

Const <<constant_name>> As <<constant_type>> = <<constant_value>>

示例

讓我們建立一個名為“Constant_demo”的按鈕來演示如何使用常量。

Private Sub Constant_demo_Click() 
   Const MyInteger As Integer = 42 
   Const myDate As Date = #2/2/2020# 
   Const myDay As String = "Sunday" 
   
   MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is " 
      & myDate & Chr(10) & "myDay is " & myDay  
End Sub

輸出

執行指令碼後，輸出將如下面的螢幕截圖所示。

VBA - 運算子

運算子可以使用簡單的表示式來定義 - 4 + 5 等於 9。這裡，4 和 5 稱為運算元，+ 稱為運算子。VBA 支援以下型別的運算子：

• 算術運算子
• 比較運算子
• 邏輯（或關係）運算子
• 連線運算子

算術運算子

VBA 支援以下算術運算子。

假設變數 A 儲存 5，變數 B 儲存 10，則：

示例

比較運算子

VBA 支援以下比較運算子。

假設變數 A 儲存 10，變數 B 儲存 20，則：

示例

邏輯運算子

VBA 支援以下邏輯運算子。

假設變數 A 儲存 10，變數 B 儲存 0，則：

示例

連線運算子

VBA 支援以下連線運算子。

假設變數 A 儲存 5，變數 B 儲存 10，則：

示例

假設變數 A = "Microsoft" 和變數 B = "VBScript"，則：

注意 − 連線運算子可用於數字和字串。輸出取決於上下文，即變數儲存的是數值還是字串值。

VBA - 決策

決策允許程式設計師控制指令碼或其一部分的執行流程。執行由一個或多個條件語句控制。

以下是大多數程式語言中典型的決策結構的一般形式。

VBA 提供以下型別的決策語句。單擊以下連結以檢視其詳細資訊。

VBA - 迴圈

可能需要多次執行程式碼塊的情況。通常情況下，語句是按順序執行的：函式中的第一個語句首先執行，然後是第二個語句，依此類推。

程式語言提供了各種控制結構，允許更復雜的執行路徑。

迴圈語句允許我們多次執行語句或語句組。以下是 VBA 中迴圈語句的一般形式。

VBA 提供以下型別的迴圈來處理迴圈需求。單擊以下連結以檢視其詳細資訊。

迴圈控制語句

迴圈控制語句會改變其正常的執行順序。當執行離開作用域時，迴圈中所有剩餘的語句都不會執行。

VBA 支援以下控制語句。單擊以下連結以檢視其詳細資訊。

VBA - 字串

字串是由字元組成的序列，可以包含字母、數字、特殊字元或所有這些字元。如果一個變數用雙引號 " " 括起來，則稱其為字串。

語法

variablename = "string"

示例

str1 = "string"   ' Only Alphabets
str2 = "132.45"   ' Only Numbers
str3 = "!@#$;*"  ' Only Special Characters
Str4 = "Asc23@#"  ' Has all the above

字串函式

VBA 提供了預定義的字串函式，幫助開發人員更有效地處理字串。以下是 VBA 支援的字串方法。請點選每個方法以瞭解更多詳情。

VBA - 日期時間函式

VBScript 日期和時間函式幫助開發人員將日期和時間從一種格式轉換為另一種格式，或者以適合特定條件的格式表達日期或時間值。

日期函式

時間函式

VBA - 陣列

我們非常清楚，變數是用於儲存值的容器。有時，開發人員需要一次在一個變數中儲存多個值。當一系列值儲存在一個變數中時，它被稱為陣列變數。

陣列宣告

陣列的宣告方式與變數的宣告方式相同，只是陣列變數的宣告使用括號。在下面的示例中，陣列的大小在括號中指定。

'Method 1 : Using Dim
Dim arr1()	'Without Size

'Method 2 : Mentioning the Size
Dim arr2(5)  'Declared with size of 5

'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")

• 雖然陣列大小指示為 5，但它可以容納 6 個值，因為陣列索引從零開始。

• 陣列索引不能為負數。

• VBScript 陣列可以在陣列中儲存任何型別的變數。因此，一個數組可以在單個數組變數中儲存整數、字串或字元。

向陣列賦值

透過為每個要賦值的值指定陣列索引值來為陣列賦值。它可以是字串。

示例

新增一個按鈕並新增以下函式。

Private Sub Constant_demo_Click()
   Dim arr(5)
   arr(0) = "1"           'Number as String
   arr(1) = "VBScript"    'String
   arr(2) = 100 		     'Number
   arr(3) = 2.45 		     'Decimal Number
   arr(4) = #10/07/2013#  'Date
   arr(5) = #12.45 PM#    'Time
  
   msgbox("Value stored in Array index 0 : " & arr(0))
   msgbox("Value stored in Array index 1 : " & arr(1))
   msgbox("Value stored in Array index 2 : " & arr(2))
   msgbox("Value stored in Array index 3 : " & arr(3))
   msgbox("Value stored in Array index 4 : " & arr(4))
   msgbox("Value stored in Array index 5 : " & arr(5))
End Sub

執行上述函式時，會產生以下輸出。

Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM

多維陣列

陣列不僅限於一維，但最多可以有 60 維。二維陣列是最常用的陣列。

示例

在下面的示例中，聲明瞭一個具有 3 行 4 列的多維陣列。

Private Sub Constant_demo_Click()
   Dim arr(2,3) as Variant	' Which has 3 rows and 4 columns
   arr(0,0) = "Apple" 
   arr(0,1) = "Orange"
   arr(0,2) = "Grapes"           
   arr(0,3) = "pineapple" 
   arr(1,0) = "cucumber"           
   arr(1,1) = "beans"           
   arr(1,2) = "carrot"           
   arr(1,3) = "tomato"           
   arr(2,0) = "potato"             
   arr(2,1) = "sandwitch"            
   arr(2,2) = "coffee"             
   arr(2,3) = "nuts"            
           
   msgbox("Value in Array index 0,1 : " &  arr(0,1))
   msgbox("Value in Array index 2,2 : " &  arr(2,2))
End Sub

執行上述函式時，會產生以下輸出。

Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee

ReDim 語句

ReDim 語句用於宣告動態陣列變數並分配或重新分配儲存空間。

語法

ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

引數說明

• Preserve - 一個可選引數，用於在更改最後一維的大小是保留現有陣列中的資料。

• Varname - 一個必需引數，表示變數的名稱，應遵循標準變數命名約定。

• Subscripts - 一個必需引數，指示陣列的大小。

示例

在下面的示例中，重新定義了一個數組，然後在更改陣列的現有大小時保留這些值。

注意 - 將陣列大小調整為小於其原始大小後，已刪除元素中的資料將丟失。

Private Sub Constant_demo_Click()
   Dim a() as variant
   i = 0
   redim a(5)
   a(0) = "XYZ"
   a(1) = 41.25
   a(2) = 22
  
   REDIM PRESERVE a(7)
   For i = 3 to 7
   a(i) = i
   Next
  
   'to Fetch the output
   For i = 0 to ubound(a)
      Msgbox a(i)
   Next
End Sub

執行上述函式時，會產生以下輸出。

XYZ
41.25
22
3
4
5
6
7

陣列方法

VBScript 中有各種內建函式，可以幫助開發人員有效地處理陣列。下面列出了與陣列一起使用的所有方法。請點選方法名稱以瞭解詳細資訊。

VBA - 使用者自定義函式

函式是一組可重用的程式碼，可以在程式中的任何位置呼叫。這消除了重複編寫相同程式碼的需要。這使程式設計師能夠將大型程式劃分為許多小型且易於管理的函式。

除了內建函式外，VBA 還允許編寫使用者自定義函式。在本節中，您將學習如何在 VBA 中編寫自己的函式。

函式定義

VBA 函式可以有一個可選的 return 語句。如果您想從函式返回一個值，則需要此語句。

例如，您可以將兩個數字傳遞給函式，然後您可以期望函式在您的呼叫程式中返回它們的乘積。

注意 - 函式可以返回多個值，這些值以逗號分隔，作為分配給函式名本身的陣列。

在使用函式之前，我們需要定義該函式。在 VBA 中定義函式最常見的方法是使用Function關鍵字，後跟唯一的函式名，它可以包含或不包含引數列表和帶有End Function關鍵字的語句，該語句指示函式的結束。以下是基本語法。

語法

新增一個按鈕並新增以下函式。

Function Functionname(parameter-list)
   statement 1
   statement 2
   statement 3
   .......
   statement n
End Function

示例

新增以下返回面積的函式。請注意，可以使用函式名本身返回值/值。

Function findArea(Length As Double, Optional Width As Variant)
   If IsMissing(Width) Then
      findArea = Length * Length
   Else
      findArea = Length * Width
   End If
End Function

呼叫函式

要呼叫函式，請使用函式名呼叫函式，如下面的螢幕截圖所示。

如下所示的面積輸出將顯示給使用者。

VBA - 子過程

Sub 過程類似於函式，但有一些區別。

• Sub 過程不返回值，而函式可以返回值也可以不返回值。

• Sub 過程可以不使用 call 關鍵字進行呼叫。

• Sub 過程始終包含在 Sub 和 End Sub 語句中。

示例

Sub Area(x As Double, y As Double)
   MsgBox x * y
End Sub

呼叫過程

要在指令碼中的某個位置呼叫過程，您可以從函式進行呼叫。我們無法像函式那樣使用相同的方式，因為 Sub 過程不會返回值。

Function findArea(Length As Double, Width As Variant)
   area Length, Width    ' To Calculate Area 'area' sub proc is called
End Function

現在您只能呼叫函式，而不能呼叫 Sub 過程，如下面的螢幕截圖所示。

面積計算結果僅在訊息框中顯示。

結果單元格顯示零，因為面積值沒有從函式返回。簡而言之，您無法從 Excel 工作表直接呼叫 Sub 過程。

VBA - 事件

VBA 是一種事件驅動的程式設計，當您手動更改單元格或單元格值範圍時可以觸發。更改事件可以使事情更容易，但是您可以很快結束一頁的格式設定。事件有兩種。

• 工作表事件
• 工作簿事件

工作表事件

當工作表發生更改時，會觸發工作表事件。它是透過右鍵單擊工作表標籤並選擇“檢視程式碼”，然後貼上程式碼來建立的。

使用者可以選擇這些工作表中的每一個，並從下拉選單中選擇“工作表”以獲取所有受支援的工作表事件列表。

以下是使用者可以新增的受支援的工作表事件。

Private Sub Worksheet_Activate() 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) 
Private Sub Worksheet_Calculate() 
Private Sub Worksheet_Change(ByVal Target As Range) 
Private Sub Worksheet_Deactivate() 
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

示例

假設我們只需要在雙擊之前顯示一條訊息。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   MsgBox "Before Double Click"
End Sub

輸出

雙擊任何單元格時，會向用戶顯示訊息框，如下面的螢幕截圖所示。

工作簿事件

當整個工作簿發生更改時，會觸發工作簿事件。我們可以透過選擇“ThisWorkbook”並從下拉選單中選擇“工作簿”來新增工作簿事件的程式碼，如下面的螢幕截圖所示。立即向用戶顯示 Workbook_open 子過程，如下面的螢幕截圖所示。

以下是使用者可以新增的受支援的工作簿事件。

Private Sub Workbook_AddinUninstall() 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Private Sub Workbook_BeforePrint(Cancel As Boolean) 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
Private Sub Workbook_Deactivate() 
Private Sub Workbook_NewSheet(ByVal Sh As Object) 
Private Sub Workbook_Open() 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
Private Sub Workbook_WindowActivate(ByVal Wn As Window) 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) 
Private Sub Workbook_WindowResize(ByVal Wn As Window)

示例

假設我們只需要在建立新工作表時向用戶顯示一條訊息，指示新工作表已成功建立。

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   MsgBox "New Sheet Created Successfully"
End Sub

輸出

建立新的 Excel 工作表時，會向用戶顯示一條訊息，如下面的螢幕截圖所示。

VBA - 錯誤處理

程式設計中存在三種類型的錯誤：（a）語法錯誤，（b）執行時錯誤和（c）邏輯錯誤。

語法錯誤

語法錯誤，也稱為解析錯誤，發生在VBScript的解釋時間。例如，以下行會導致語法錯誤，因為它缺少一個右括號。

Function ErrorHanlding_Demo()
   dim x,y
   x = "Tutorialspoint"
   y = Ucase(x
End Function

執行時錯誤

執行時錯誤，也稱為異常，發生在解釋之後，執行期間。

例如，以下行會導致執行時錯誤，因為這裡的語法是正確的，但在執行時它試圖呼叫fnmultiply，這是一個不存在的函式。

Function ErrorHanlding_Demo1()
   Dim x,y
   x = 10
   y = 20
   z = fnadd(x,y)
   a = fnmultiply(x,y)
End Function

Function fnadd(x,y)
   fnadd = x + y
End Function

邏輯錯誤

邏輯錯誤可能是最難追蹤的錯誤型別。這些錯誤不是語法錯誤或執行時錯誤的結果。相反，當你在驅動指令碼的邏輯中犯錯並且沒有得到你期望的結果時，就會發生這些錯誤。

你無法捕獲這些錯誤，因為這取決於你的業務需求，你想要在你的程式中使用什麼樣的邏輯。

例如，將一個數字除以零，或編寫一個進入無限迴圈的指令碼。

Err物件

假設如果我們有一個執行時錯誤，那麼執行將停止並顯示錯誤訊息。作為開發者，如果我們想捕獲錯誤，則使用Err物件。

示例

在下面的例子中，Err.Number給出錯誤號，Err.Description給出錯誤描述。

Err.Raise 6   ' Raise an overflow error.
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear   ' Clear the error.

錯誤處理

VBA啟用錯誤處理例程，也可以用於停用錯誤處理例程。如果沒有On Error語句，發生的任何執行時錯誤都是致命的：會顯示一條錯誤訊息，並且執行會突然停止。

On Error { GoTo [ line | 0 | -1 ] | Resume Next }

示例

Public Sub OnErrorDemo()
   On Error GoTo ErrorHandler   ' Enable error-handling routine.
   Dim x, y, z As Integer
   x = 50
   y = 0
   z = x / y   ' Divide by ZERO Error Raises
  
   ErrorHandler:    ' Error-handling routine.
   Select Case Err.Number   ' Evaluate error number.
      Case 10   ' Divide by zero error
         MsgBox ("You attempted to divide by zero!")
      Case Else
         MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
   End Select
   Resume Next
End Sub

VBA - Excel 物件

使用VBA程式設計時，使用者會處理一些重要的物件。

• 應用程式物件
• 工作簿物件
• 工作表物件
• 區域物件

應用程式物件

Application物件包含以下內容：

• 應用程式範圍的設定和選項。
• 返回頂級物件的方法，例如ActiveCell、ActiveSheet等等。

示例

'Example 1 :
Set xlapp = CreateObject("Excel.Sheet") 
xlapp.Application.Workbooks.Open "C:\test.xls"

'Example 2 :
Application.Windows("test.xls").Activate

'Example 3:
Application.ActiveCell.Font.Bold = True

工作簿物件

Workbook物件是Workbooks集合的成員，包含當前在Microsoft Excel中開啟的所有Workbook物件。

示例

'Ex 1 : To close Workbooks
Workbooks.Close

'Ex 2 : To Add an Empty Work Book
Workbooks.Add

'Ex 3: To Open a Workbook
Workbooks.Open FileName:="Test.xls", ReadOnly:=True

'Ex : 4 - To Activate WorkBooks
Workbooks("Test.xls").Worksheets("Sheet1").Activate

工作表物件

Worksheet物件是Worksheets集合的成員，包含工作簿中的所有Worksheet物件。

示例

'Ex 1 : To make it Invisible
Worksheets(1).Visible = False

'Ex 2 : To protect an WorkSheet
Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True

區域物件

Range物件表示一個單元格、一行、一列或包含一個或多個連續單元格塊的單元格選擇。

'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"

'Ex 2 : To put a value in range of Cells
Worksheets("Sheet1").Range("A1:A4").Value = 5

VBA - 文字檔案

您還可以使用VBA讀取Excel檔案並將單元格內容寫入文字檔案。VBA允許使用者使用兩種方法處理文字檔案：

• 檔案系統物件
• 使用Write命令

檔案系統物件 (FSO)

顧名思義，FSO幫助開發人員處理驅動器、資料夾和檔案。在本節中，我們將討論如何使用FSO。

驅動器 (Drive)

Drive是一個物件，它提供對特定磁碟驅動器或網路共享的屬性的訪問。Drive物件支援以下屬性：

• 可用空間 (AvailableSpace)
• 驅動器號 (DriveLetter)
• 驅動器型別 (DriveType)
• 檔案系統 (FileSystem)
• 可用空間 (FreeSpace)
• 就緒狀態 (IsReady)
• 路徑 (Path)
• 根資料夾 (RootFolder)
• 序列號 (SerialNumber)
• 共享名 (ShareName)
• 總大小 (TotalSize)
• 卷標 (VolumeName)

示例

步驟1 - 在繼續使用FSO進行指令碼編寫之前，我們應該啟用Microsoft Scripting Runtime。為此，請導航到工具→引用，如下面的螢幕截圖所示。

步驟2 - 新增“Microsoft Scripting RunTime”並單擊“確定”。

步驟3 - 新增要寫入文字檔案的資料並新增一個命令按鈕。

步驟4 - 現在是編寫指令碼的時候了。

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   Dim fso As FileSystemObject
   Set fso = New FileSystemObject
   Dim stream As TextStream
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   ' Create a TextStream.
   Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True)
  
   CellData = ""
  
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = Trim(ActiveCell(i, j).Value)
         stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
      Next j
   Next i
  
   stream.Close
   MsgBox ("Job Done")
End Sub

輸出

執行指令碼時，請確保將游標放在工作表的第一個單元格中。“D:\Try”下建立Support.log檔案，如下面的螢幕截圖所示。

檔案內容如下面的螢幕截圖所示。

Write命令

與FSO不同，我們不需要新增任何引用，但是，我們將無法處理驅動器、檔案和資料夾。我們只能將流新增到文字檔案。

示例

Private Sub fn_write_to_text_Click()
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
  
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
    
   FilePath = "D:\Try\write.txt"
   Open FilePath For Output As #2
  
   CellData = ""
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
         Write #2, CellData
      Next j
   Next i
  
   Close #2
   MsgBox ("Job Done")
End Sub

輸出

執行指令碼後，“D:\Try”位置將建立“write.txt”檔案，如下面的螢幕截圖所示。

檔案內容如下面的螢幕截圖所示。

VBA - 圖表程式設計

使用VBA，您可以根據特定條件生成圖表。讓我們透過一個例子來看一下。

步驟1 - 輸入要生成圖表的資料。

步驟2 - 建立3個按鈕 - 一個生成條形圖，另一個生成餅圖，另一個生成柱狀圖。

步驟3 - 開發一個宏來生成每種型別的圖表。

' Procedure to Generate Pie Chart
Private Sub fn_generate_pie_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlPie
   Next cht
End Sub

' Procedure to Generate Bar Graph
Private Sub fn_Generate_Bar_Graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlBar
   Next cht
End Sub

' Procedure to Generate Column Graph
Private Sub fn_generate_column_graph_Click()
   Dim cht As ChartObject
   For Each cht In Worksheets(1).ChartObjects
      cht.Chart.Type = xlColumn
   Next cht
End Sub

步驟4 - 單擊相應的按鈕，將建立圖表。在下面的輸出中，單擊“生成餅圖”按鈕。

VBA - 使用者窗體

使用者窗體是一個自定義的對話方塊，它使使用者資料輸入更可控，更容易使用。在本節中，您將學習如何設計一個簡單的窗體並將資料新增到Excel中。

步驟1 - 按Alt+F11導航到VBA視窗，然後導航到“插入”選單並選擇“使用者窗體”。選擇後，將顯示使用者窗體，如下面的螢幕截圖所示。

步驟2 - 使用給定的控制元件設計窗體。

步驟3 - 新增每個控制元件後，必須為控制元件命名。Caption對應於窗體上顯示的內容，name對應於編寫該元素的VBA程式碼時將出現的邏輯名稱。

步驟4 - 以下是每個新增控制元件的名稱。

步驟5 - 透過右鍵單擊窗體並選擇“檢視程式碼”來新增窗體載入事件的程式碼。

步驟6 - 從物件下拉列表中選擇“使用者窗體”，然後選擇“Initialize”方法，如下面的螢幕截圖所示。

步驟7 - 載入窗體時，確保文字框已清除，下拉框已填充，並且單選按鈕已重置。

Private Sub UserForm_Initialize()
   'Empty Emp ID Text box and Set the Cursor 
   txtempid.Value = ""
   txtempid.SetFocus
   
   'Empty all other text box fields
   txtfirstname.Value = ""
   txtlastname.Value = ""
   txtemailid.Value = ""
   
   'Clear All Date of Birth Related Fields
   cmbdate.Clear
   cmbmonth.Clear
   cmbyear.Clear
   
   'Fill Date Drop Down box - Takes 1 to 31
   With cmbdate
      .AddItem "1"
      .AddItem "2"
      .AddItem "3"
      .AddItem "4"
      .AddItem "5"
      .AddItem "6"
      .AddItem "7"
      .AddItem "8"
      .AddItem "9"
      .AddItem "10"
      .AddItem "11"
      .AddItem "12"
      .AddItem "13"
      .AddItem "14"
      .AddItem "15"
      .AddItem "16"
      .AddItem "17"
      .AddItem "18"
      .AddItem "19"
      .AddItem "20"
      .AddItem "21"
      .AddItem "22"
      .AddItem "23"
      .AddItem "24"
      .AddItem "25"
      .AddItem "26"
      .AddItem "27"
      .AddItem "28"
      .AddItem "29"
      .AddItem "30"
      .AddItem "31"
   End With
   
   'Fill Month Drop Down box - Takes Jan to Dec
   With cmbmonth
      .AddItem "JAN"
      .AddItem "FEB"
      .AddItem "MAR"
      .AddItem "APR"
      .AddItem "MAY"
      .AddItem "JUN"
      .AddItem "JUL"
      .AddItem "AUG"
      .AddItem "SEP"
      .AddItem "OCT"
      .AddItem "NOV"
      .AddItem "DEC"
   End With
   
   'Fill Year Drop Down box - Takes 1980 to 2014
   With cmbyear
      .AddItem "1980"
      .AddItem "1981"
      .AddItem "1982"
      .AddItem "1983"
      .AddItem "1984"
      .AddItem "1985"
      .AddItem "1986"
      .AddItem "1987"
      .AddItem "1988"
      .AddItem "1989"
      .AddItem "1990"
      .AddItem "1991"
      .AddItem "1992"
      .AddItem "1993"
      .AddItem "1994"
      .AddItem "1995"
      .AddItem "1996"
      .AddItem "1997"
      .AddItem "1998"
      .AddItem "1999"
      .AddItem "2000"
      .AddItem "2001"
      .AddItem "2002"
      .AddItem "2003"
      .AddItem "2004"
      .AddItem "2005"
      .AddItem "2006"
      .AddItem "2007"
      .AddItem "2008"
      .AddItem "2009"
      .AddItem "2010"
      .AddItem "2011"
      .AddItem "2012"
      .AddItem "2013"
      .AddItem "2014"
   End With
   
   'Reset Radio Button. Set it to False when form loads.
   radioyes.Value = False
   radiono.Value = False

End Sub

步驟8 - 現在將程式碼新增到“提交”按鈕。單擊“提交”按鈕後，使用者應該能夠將值新增到工作表中。

Private Sub btnsubmit_Click()
   Dim emptyRow As Long
  
   'Make Sheet1 active
   Sheet1.Activate
  
   'Determine emptyRow
   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
  
   'Transfer information
   Cells(emptyRow, 1).Value = txtempid.Value
   Cells(emptyRow, 2).Value = txtfirstname.Value
   Cells(emptyRow, 3).Value = txtlastname.Value
   Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
   Cells(emptyRow, 5).Value = txtemailid.Value
  
   If radioyes.Value = True Then
      Cells(emptyRow, 6).Value = "Yes"
   Else
      Cells(emptyRow, 6).Value = "No"
   End If
End Sub

步驟9 - 當用戶單擊“取消”按鈕時，新增一個方法來關閉窗體。

Private Sub btncancel_Click()
   Unload Me
End Sub

步驟10 - 透過單擊“執行”按鈕來執行窗體。將值輸入窗體並單擊“提交”按鈕。值將自動流入工作表，如下面的螢幕截圖所示。