1 巨集 2 資料型態 3 物件、屬性、方法與事件 4 陳述式與副函式 5 其他注意事項 6 範例 Introduction of VBA 1 巨集 2 資料型態 3 物件、屬性、方法與事件 4 陳述式與副函式 5 其他注意事項 6 範例 1

1 巨集 「開發人員」索引標籤

錄製巨集 絕對位置錄製 vs. 相對位置錄製

2 資料型態 主要資料型態 宣告變數之資料型態 Boolean (布林) True or False Integer (整數) Data Type Value Boolean (布林) True or False Integer (整數) -32,768 to 32,767 Long (長整數) -2,147,483,648 to 2,147,483,647 Single (single-precision floating-point) (單精準浮點數) -3.402823E38 to 1.401298E45 Double (double-precision floating-point) (雙精準浮點數) -1.79769313486232E308 to 1.79769313486232E308 String (字串) Ex. “book 11” Variant Any data type 宣告變數之資料型態 Dim variable_x as Double Dim i, j, k as Integer ※ Dim originally (in BASIC) stood for Dimension, as it was used to define the dimensions of an array (陣列). However, Dim is now used to define any variable

陣列宣告 動態陣列宣告 資料型態轉換 Dim my_array(10) As Integer ‘ 一維陣列 (11個Integer空間) ※ Please refer to “Array, String, and Number.xlsm” 陣列宣告 Dim my_array(10) As Integer ‘ 一維陣列 (11個Integer空間) Dim my_Array2(10,20) As Double ‘ 二維陣列 (11x21個Double空間) 動態陣列宣告 Dim my_array3() as Double ' 不定大小的一維陣列 ReDim my_array3(100) as Double ' 重新宣告大小 資料型態轉換 CInt(expression), CLng(expression), CDbl(expression), CStr(expression) a = 100.3 ' a is a Double c = CInt(a) ' convert a to be an Integer c = 100 b = 60000 ‘ b is a Long s = CStr(b) ' convert a to be a String s = “60000”

字串聯結 (string concatenation) s1 = "Hello" & s s2 = "工作表" & "1" 字串處理函數 Len: 計算字串長度 x = Len("NTU_IB") ⇒ x = 6 Mid: 擷取固定長度字串 str = Mid("NTU_IB",1,3) ⇒ str = "NTU" Replace: 取代字串中的某字串 str = Replace("NTU_IB", "_", "3") ⇒ str = "NTU3IB" Split: 字串分割 str = Split("NTU_IB", "_") ⇒ str(0) = "NTU" and str(1) = "IB" StrComp: 字串比較 x = StrComp(str(0), str(1)) ⇒ x = 1 x = StrComp(str(0), "NTU") ⇒ x = 0

邏輯運算子 (logic operators) And、Or、Not x = 3 y = 2 aa = x >= 3 And y >= 2 ' aa is True bb = x >= 3 Or y <= 2 ' bb is True cc = x >= 3 And Not y >= 2 ' cc is False

3 物件、屬性、方法與事件 活頁簿 (Workbooks) and 工作表 (Worksheets) ThisWorkbook.Close ' 關閉目前使用的活頁簿 (即Excel 檔案) Workbooks("Array, String, and Number.xlsm").Close ' 關閉檔案名為 “Array, String, and Number.xlsm”的Excel 檔案 Workbooks.Open "C:\demo.xlsx" ' 開啟C:\demo.xlsx這個檔案 Worksheets("Sheet2").Activate   ' 切換至Sheet2工作表 ActiveWorkbook.Worksheets("工作表1").Columns("A:A"). EntireColumn.Select ' 選取目前活頁簿中的"工作表1"這個worksheet的 column A

在VBA中呼叫Excel提供的函數 Application.WorksheetFunction.Average(陣列名稱) ' 計算平均 ※ Please refer to “Call functions provided by Excel.xlsm” 在VBA中呼叫Excel提供的函數 Application.WorksheetFunction.Average(陣列名稱) ' 計算平均 Application.WorksheetFunction.StDev(陣列名稱) ' 計算標準差 Application.WorksheetFunction.SumProduct(矩陣1, 矩陣2) ' 計算乘積和 Application.WorksheetFunction.Average(Application.WorksheetFunction.Sum Product(矩陣1, 矩陣2)) ' 組合乘積和與平均兩個Excel提供的公式 Application.WorksheetFunction.NormSInv(Rnd()) ' 從標準常態分配中抽樣 ※ Rnd() is a VB function for drawing uniformly distributed random variables (In Excel, RAND() can do the same thing) ※ NormSInv() is a function provided by Excel ※ 注意若在VBA中組合NormSInv() 與 RAND() 兩個Excel函數如下 Application.WorksheetFunction.NormSInv(Application.WorksheetFunction.Rnd()) 雖然理論上可行，在寫程式時VBA不會出現錯誤訊息，但在跑程式時，會出現 “物件不支援此屬性或方法” 之錯誤訊息

專案 (Project) 與模組 (Module) ※ Please refer to “Macro in Module.xlsm” 專案 (Project) 與模組 (Module) 整個Excel檔 (或是說活頁簿) 就是一個project 錄製巨集時，會自動存在「Module1」內，可供任意工作表使用 可將類似功能的函式放在同一個模組中，例如處理字串的放在 「Module1」，處理計算的放在「Module2」 點選「Module1」，「檢視」，「屬性視窗」可修改Module的名字

物件 (Object) 屬性 (Property) 方法 (Method) 事件 (Event) ※ Please refer to “CommandButton and Checkbox.xlsm” 物件 (Object) Workbook、Worksheets、CommandButton、CheckBox都為物件 屬性 (Property) 屬性就是物件的特徵描述 物件的名字、值、長寬、顏色、其上顯示文字與字型等，均是其屬性 ※ 在設計模式下，對某個物件壓滑鼠右鍵，選 “內容”，可以看到此物件的屬性 方法 (Method) 方法就是對物件做動的函式 ThisWorkbook.Close其中Close即為Workbook的一個方法 事件 (Event) 按一個CommandButton1，即驅動 (trigger) 了CommandButton1_Click() 這個事件與其專屬的程式碼 ※ 當在VBA中打入任何的物件，之後再加 “.”，此時VBA會提供所有有關這 個物件的Property與Method供使用者選擇

4 陳述式與副函式 If-then, If-Then-Else, If-Then-ElseIf-Else ※ Please refer to “For and Do While.xlsm” If-then, If-Then-Else, If-Then-ElseIf-Else If 條件 Then     敘述 End If If 條件 Then     敘述 Else     敘述 End If If 條件 Then     敘述 ElseIf 條件 Then     敘述 ElseIf 條件 Then     敘述 Else     敘述 End If ※ “條件” 即為能產生Boolean結果的式子 (見 p. 7)，“敘述”則為一般的程式碼

Do While…Loop Do …Loop Until For…Next Do While 條件 敘述 Loop For 數值變數 = 初始值 To 終止值 [Step 增量]     敘述 Next [數值變數]

Sub程序 Function程序 Event程序 Sub aaa () Range(“A1”) = 1 End Sub ※ Please refer to “Project and Module.xlsm” and “Function or Sub.xlsm” Sub程序 Sub aaa () Range(“A1”) = 1 End Sub Sub bbb (a, b) cells(1,1) = a + b End Sub or Function程序 Function f (a, b) f = a + b End Function Event程序 Sub CommandButton1_Click() aaa Call bbb(2, 4) c = f(1, 2) End Sub

Public vs. Private Private Sub 程序名稱 () Private Function 程序名稱 () or End Sub End Function 上述程序僅供同一模組或同一WorkSheet內之程序呼叫使用 Public Sub 程序名稱 () Public Function 程序名稱 () End Sub End Function 上述程序可供同一模組或同一WorkSheet與外部程序呼叫 ※ 沒有註明Private 就是 Public ※ Public 放在模組 (Module) 中，供各個工作表使用

GoTo 陳述式 ※ Please refer to “GoTo and Exit.xlsm” Sub ccc () If Range("A1") = 1 Then Range("B1") = 2 GoTo any_label_2 ‘跳到any_label_2這個位置 ElseIf Range("A1") = 2 Then GoTo any_label_1 '不再繼續進行If的指令，跳到any_label_1這個位置 Else Range("B1") = 3 GoTo any_label_2 '跳到any_label_2這個位置 End If any_label_1: MsgBox "GoTo any_label1" any_label_2: End Sub

跳離指令 Exit Do '強制離開Do Loop迴圈 Exit For '強制離開For Next迴圈 Exit Sub '強制離開Sub程序 Exit Function '強制離開Function程序 ‘Exit For 範例 For i = 1 To 20 Cells(i, 1) = i If i = 18 Then MsgBox "Exit For when i=18" Exit For End If Next i

使用者定義函數 (User-defined function) ※ Please refer to “User-Defined RMSE function.xlsm” 使用者定義函數 (User-defined function) 放在模組 (Module) 中的Function程序，可以類似Excel本身所提供的函數一樣，在Excel的Cells中被使用

5 其他注意事項 寫程式小技巧 變數名稱顧名思義 盡量使用Sub與Function程序使得程式的邏輯更清楚與有組織 鋸齒狀撰寫程式 時時加上註解 (「'」後加註解) 以幫助自己記得每行 (或每段) 程式碼 是要處理何事

6 範例 Implement the matrix addition Inputs: matrices A and B ※ Please refer to “Matrix Addition (use mouse to select input cells).xlsm” Implement the matrix addition Inputs: matrices A and B Output: C = A + B ※ Learning goals: 1. 如何讀入矩陣參數 2. 如何使用For-Next做nested loops

Implement the matrix multiplication ※ Please refer to “Matrix Multiplication (call Excel function MMULT).xlsm” Implement the matrix multiplication Inputs: matrices A and B Output: C = AB ※ Learning goals: 1. 如何呼叫使用Excel所提供的函式

Implement interchanging two rows ※ Please refer to “Interchange two rows.xlsm” Implement interchanging two rows Inputs: matrix A and row indices i, j Output: perform Ii,j for A ※ Learning goals: 1. 了解如何交換陣列中兩個entries或是rows之值的演算法