VBA簡介

Size: px

Start display at page:

Download "VBA簡介"

樗还贡
5 years ago
Views:

1 目錄課程 :Excel VBA 辦公室自動化程式開發專題班議師 : 孫在陽 (teacher.sun@msa.hinet.net)

2 目錄 1. 認識 VBA... 1 VBA 簡介... 1 模組與程序... 5 VBA 基本程式技巧... 5 簡易流程控制巨集開始... 9 啟用巨集... 9 設定安全性... 9 製作格式巨集製作填滿巨集修改填滿巨集插入列巨集插入指定列數巨集刪除空白資料巨集資料轉換 : 全部自動資料清單自動編號表單結合工具按鈕實際案例同工作表的固定插入自動選範圍合併工作表自訂函數 (area) 自訂函數 (πr2) 負數變正數設計 [ 任意範圍 ] 新增多張工作表工作表排序自動程序設計匯入檔案區域號碼 : 儲存格值的控制 [ 工作表 ] 清除全部註解取出註解活頁簿控制... 28

3 1. 認識 VBA VBA 簡介 VBA 為 Visual Basic For Application 的簡稱, 在 Microsoft Office 中包括 Excel Word PowerPoint Access Visio 均提供 VBA 程式的功能敘述 VBA 的語法與 Visual Basic 大多數均是相同的, 下列這幾行正確的 VBA 敘述 index1=1 index1=index1+1 If index1 > 10 Then index1=10 value1=0.01: alpha=0.85*index1 連續行 VBA 連續字元符號是底線 ( _ ), 當程式執行到某一列是以底線符號結尾時, 他會假設有一個連續的行, 並將下一行視為目前敘述的一部份 : Response = MsgBox("Do you want to continue?", vbyesno + vbcritical + vbdefaultbutton2, "MsgBox Demonstration", "DEMO.HLP", Ctxt = 1000) 如果程式列包含很長的列, 比較好的方法是加一空白字元和底線符號在該行結束的地方, 並在下一列繼續此一列敘述如下所示 Response = MsgBox("Do you want to continue?", vbyesno + vbcritical + _vbdefaultbutton2, "MsgBox Demonstration", "DEMO.HLP", Ctxt = 1000) 第 1 頁

4 連續行的最末列的最後面不需要加上底線符號, 而且底線符號之後也不可以再加上任何文字或數字, 註解也不可以註解註解列可讓程式易被看懂, 又如果要讓程式中的某一列不執行, 只要將這一列標示為註解即可 VBA 中的註解列符號為單引號 ( ' ), 若是一整列都要作為註解, 也可在這列以 REM 敘述開頭 REM 註解列示範 This is a remark demo MyStr1 = "Hello" : Rem 將註解加在陳述式之後, 用冒號隔開 MyStr2 = "Goodbye" ' 此亦為註解, 且不需要冒號常數與變數常數在程式中只包含固定的數值, 您不能在程式進行中更動他, 常數以關鍵字 Const 始, 例如下一列將 beta 定義為常數, 其值為 0.85: Const beta=0.85 VBA 中已有預先定義了某些常數, 例如在 MsgBox 函數中 vbyesno 代表常數值 4 詳細的常數定義可在 VBA 的編輯器中取得說明變數是指到程式可以儲存值的記憶體位置的代表名稱變數與常數不同的是變數可在程式執行期間改變其值在 VBA 中變數可有不同的內容型態, 例如 :Byte Boolean Integer Long Currency Decimal Single Double Date String Object Variant, 其中預設的變數型態是 Variant 以下三列是變數的使用範例 : 第 2 頁

5 price=45 數值 (integer) 變數 Item= 水果文字 (string) 變數 Set xlapp = CreateObject("excel.application") 物件 (object) 變數 Option Explicit 敘述這一敘述通知 VBA 編譯器找出未宣告即使用的數數在預設的情況下 VBA 允許變數不經宣告即可使用, 但這也造成程式除錯上的某些困難您也可在程式的第一行使用 Option Explicit 敘述, 可強制程式在使用變數前先需宣告, 這樣有避免拼錯字母造成程式上邏輯錯誤的問題 Dim 敘述您可以使用 Dim 在程序或函數中明確的定義變數或陣列變數若您使用了 Option Explicit 敘述, 則您 ; 所使用的變數皆需使用 Dim 先行宣告才可使用以下三列皆是正確的變數宣告 Dim text 宣告了一個變數 Dim price,value,sum 同時宣告兩個以上的變數 Dim Item(100) 宣告了 101 個元素的陣列 VBA 亦可使用陣列, 陣列的第一個元素預設為 0, 所以上述最後一列中宣告了 Item(0) 至 Item(100) 共計 101 個變數 Public 與 Private 關鍵字 Public 與 Private 關鍵字宣告變數用來限制變數的有效範圍, 使用 Public 陳述式宣告的變數, 可以在所有模組裡使用 ( 除非使用 Option Private Module); 變數有效範圍為它們所在的專案裡第 3 頁

6 使用 Public 陳述式來宣告變數的資料型態舉例來說, 下列的陳述式宣告一個變數為 Integer: Public NumberOfEmployees As Integer 您也可用 Public 陳述式來宣告變數的物件型態下列的陳述式宣告一個變數為工作表的新執行個體 Public X As New Worksheet Public 陳述式無法在物件類別模組裏宣告固定長度的字串變數 Private 用以宣告變數只適用於宣告它們的模組中 Private 陳述式也可以用來宣告變數的資料型態舉例來說, 下列的陳述式宣告變數為 Integer: Private NumberOfEmployees As Integer 您也可使用 Private 陳述式加上空白括號來宣告動態陣列宣告之後, 在程序裏使用 ReDim 陳述式來定義陣列的維數和元素變數的大小已由 Private Public 或 Dim 陳述式所明確指定, 若重新宣告陣列變數的維數, 則會發生錯誤變數名稱如同在 Visual Basic 中一般, 一個變數名稱最多可使用 255 個字元, 變數名稱的命名規則如下 : 1. 變數的第一個字元需英文字母例如 Person1 為有效變數名,23men 則是不合規則的變數名稱 2. 變數名稱不可包含空白點, 逗號或其他特殊字元 3. 變數名稱不可為關鍵字, 例如 for next while public 等第 4 頁

7 模組與程序模組 (Module): 功能 : 是存放程式碼的視窗, 使用 : 可以建立很多模組, 分別存放不同特性的程式碼, 包含 : 在模組中可以建立各種的程序 (Procedure) 函數(Function) 程序 (Procedure) 與函數 (Function) 程序 (Procedure): 呼叫這個程序後, 會自動執行其內部的程式碼 Sub 程序名稱 () 程式內容. 函數 (Function): 在執行這個函數後, 會傳回資料到主程式 Function 函數名稱 ( 參數名稱 ) 程式內容函數名稱 ( 參數名稱 ) = End Function VBA 基本程式技巧變數的宣告 Dim x as Integer Dim str1,str2 as String Dim total as Long Dim t_date as date 第 5 頁

8 變數型態大小資料範圍 Byte 1 位元組 0~255 Boolean 2 位元組 True, False Integer 整數 2 位元組 ~32767 Long 長整數 4 位元組 ~ Currency 貨幣 8 位元組 ~ Decimal 十進位 10 位元組沒有小數位 : 有小數 28 位 : Single 單精度浮 4 位元組 E38 ~ E-45 點 Double 倍精度浮 8 位元組 e308 ~ 點 e-324 Date 日期 8 位元組 String 字串可變動 Object 物件 4 位元組可引用任何物件 Variant 預設視型態而定此具有可彈性的資料型態使用者自訂型態不定運算符號 / 算數運算加 +, 減 -, 乘 *, 除 /, 餘數 mod, 指數 ^ a^b a mod b 邏輯運算符號說明範例 And 且 X and y Or 或 X or y 第 6 頁

9 Not 否 Not x Eqv 相等 X eqv y Xor 互斥 X xor y Imp 包含 X imp y 關係運算符號說明範例 = 等於 X = y <> 不等於 X <>y < 小於 X<y > >= <= 字串運算符號說明範例 + 連結 X1+x2+x3 & 連結 X1 & x2 & x3 先轉換成字串後再連結簡易流程控制 If 條件 then 描述式 End If Select case 運算式 Case 測試值 1 描述式 Case 測試值 2 描述式 If 條件 then 描述式 Else 描述式 End If Select case x Case 1 to 3 Str= 第一季 Case 4 to 6 Str= 第二季第 7 頁

10 End Select For 變數 = 初值 to 終值描述式 next Do while 條件描述式 loop Do 描述式 loop while 條件 Do until 條件描述式 loop Do 描述式 loop until 條件 Do 描述式 loop End Select Sum=0 For I= 1 to 10 step 1 Sum=sum+I Next X=0 Do while x<5 X=x+1 Loop X=0 Do X=x+1 Loop while x<5 X=0 Do until x<5 X=x+1 Loop X=0 Do X=x+1 Loop until x<5 X=0 Do X=x+1 loop 第 8 頁

11 2. 巨集開始啟用巨集 1. 選取 OFFICE 按鈕 / EXCEL 選項清單 2. 選取常用標籤 / 勾選開發人員索引標籤項目 3. 按確定按鈕設定安全性選取開發人員索引標籤 / 巨集安全性項目設定啟用所有巨集項目 / 確定按鈕第 9 頁

12 製作格式巨集選取開發人員索引標籤 / 錄製巨集項目輸入巨集名稱如 : 藍色的字第 10 頁

13 選取確定鈕選取常用索引標籤 / 字型項目 / 設定色彩如 : 藍色選取開發人員索引標籤 / 停止錄製項目製作填滿巨集選取有數值的任一儲存格如 :B2 選取開發人員索引標籤 / 錄製新巨集項目輸入巨集名稱如 : 填滿 / 選取確定鈕選取常用索引標籤 / 填滿清單 / 數列項目設定欄 / 設定等差級數 / 設定終止值如:50 / 確定選取開發人員索引標籤 / 停止錄製項目修改填滿巨集選取開發人員索引標籤 / 巨集項目選取巨集名稱如 : 填滿 / 選取編輯鈕修改程式碼第 11 頁

14 Dim x x = Range("E2").Value Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Stop:=x, Trend:=False 插入列巨集選取開發人員索引標籤 / 巨集項目輸入巨集名稱如 : 插入列 / 選取建立鈕 Sub 插入列 () For X = ActiveCell.Row To 10 Step 2 Rows(X).Select Selection.Insert Shift:=xlDown Next 插入指定列數巨集選取開發人員索引標籤 / 巨集項目選取巨集名稱如 : 插入列 / 選取編輯鈕 Sub 插入列 () X1 = ActiveCell.Row Y = InputBox(" 請輸入列數 ") * 2 + X1 For X = ActiveCell.Row To Y Step 2 第 12 頁

15 Rows(X).Select Selection.Insert Shift:=xlDown Next 註 : Selection.Delete Shift:=xlUp 刪除刪除空白資料巨集選取開發人員索引標籤 / 巨集項目輸入巨集名稱如 : 插入列 / 選取建立鈕 Sub 刪除空白列 () For X = 1 To 100 Step 1 If ActiveCell.Value = "" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Next 資料轉換 : 全部自動 Sub 全部正數 () Dim z1, z2 第 13 頁

16 z1 = ActiveCell.Column Selection.End(xlToRight).Select z2 = ActiveCell.Column Selection.End(xlToLeft).Select For z = 1 To (z2 - z1 + 1) Step 1 Call 正數 ActiveCell.Offset(0, 1).Select Next 資料清單 Sub 清單設計 () With Selection.Validation.Delete.Add Type:=xlValidateList, Operator:= _ AlertStyle:=xlValidAlertStop, xlbetween, Formula1:=" 人事, 行政, 財務, 總務 ".IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "" 第 14 頁

17 .InputMessage = "".ErrorMessage = "".IMEMode = xlimemodenocontrol.showinput = True.ShowError = True End With 自動編號 Sub 自動編號 () Dim x1, x2, x, y y = ActiveCell.Address Selection.Copy Range("A1").Select ActiveSheet.Paste Selection.End(xlUp).Select x1 = ActiveCell.Row Selection.End(xlDown).Select x2 = ActiveCell.Row Range(Selection, Selection.End(xlUp)).Select Selection.ClearContents 第 15 頁

18 Range(y).Select For x = 1 To (x2 - x1 + 1) Step 1 y = "." & ActiveCell.Value ActiveCell.Value = x & y ActiveCell.Offset(1, 0).Select Next 表單選取開發人員索引標籤 / 選取 VB 編輯器選取插入索引標籤 / 選取自訂表單項目設計表單內容 / 確定結合工具按鈕第 16 頁

19 選取 OFFICE 按鈕 / EXCEL 選項按鈕選取自訂項目 / 巨集由此選擇命令選取巨集命令項目 / 選取新增按鈕 / 確定按鈕第 17 頁

20 3. 實際案例同工作表的固定插入 Sub 插入 () 固定 5 列做 10 次 Dim i For i = 0 To 10 Step 1 x = ActiveCell.Row Rows(x + 5).Select Selection.Insert Shift:=xlDown ActiveCell.Offset(1, 0).Select Next 自動選範圍 Range(selection,selection.end(xldown)).select Range(selection,selection.end(xlToRight)).select Cells.Select 合併工作表 Sub 合併工作表 () Dim mysht As Worksheet Dim myrng1 As Range Dim myrng2 As Range 第 18 頁

21 Dim i Set mysht = Worksheets.Add(After:=Sheets(Sheets.Count)) With mysht.name = "new" ' 設定表名 End With Worksheets("new").Move AFTER:=Worksheets(Sheets.count) Set mysht = Worksheets(1) For i = 2 To 4 Step 1 Worksheets(i).Select Range("A2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Application.CutCopyMode = False Selection.Copy mysht.select ActiveSheet.Paste Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Next 第 19 頁

22 Worksheets(2).Select ' 加入標題 Rows("1:1").Select Selection.Copy mysht.select Rows("1:1").Select Selection.Insert Shift:=xlDown Range("a1").Select 自訂函數 (area) Function area(l,w) clp=int(w*l) End 自訂函數 (πr2) Function clp(r) clp= Application.WorksheetFunction.Round(3.14*r*r,2) End 負數變正數設計 [ 任意範圍 ] Sub 負數變正數 () Dim myrng As Range Dim myrng2 As Range 第 20 頁

23 Dim mysht As Worksheet Dim x, x1, y, y1, i, j, n Set mysht = Worksheets(1) ' 任意的工作表 Set myrng = mysht.usedrange Range(myRng.Address).Select Set myrng = ActiveCell x = ActiveCell.Row y = ActiveCell.Column Set myrng2 = myrng.specialcells(xlcelltypelastcell) Range(myRng2.Address).Select x1 = ActiveCell.Row y1 = ActiveCell.Column Range(myRng.Address).Select Set myrng = ActiveCell ActiveCell.Select n = myrng.address For j = 0 To (y1 - y) For i = 0 To (x1 - x) If ActiveCell.Value < 0 Then ActiveCell.Value = ActiveCell.Value * -1 End If 第 21 頁

24 ActiveCell.Offset(1, 0).Select Next i For i = 0 To (x1 - x) ActiveCell.Offset(-1, 0).Select Next i ActiveCell.Offset(0, 1).Select Next j Set myrng = Nothing Set myrng2 = Nothing Set mysht = Nothing 新增多張工作表 Sub 方法一 () Dim x, y x = InputBox(" 請輸入增加工作表的數量 ", " 增加工作表 ") With Worksheets.Add Count:=x ' 指定張數來新增 End With Sub 新增多張工作表 () Dim mysht As Worksheet 第 22 頁

25 Dim x, y x = InputBox(" 請輸入增加工作表的數量 ", " 增加工作表 ") For y = 1 To x Step 1 Set mysht = Worksheets.Add(After:=Sheets(Sheets.Count)) With mysht.name = y ' 設定工作表名稱 End With Next y Set mysht = Nothing ' 物件的釋放工作表排序 Sub 排序工作表 () Dim mysht As Worksheet Dim myarray() As String Dim i As Long ReDim myarray(1 To Worksheets.Count, 1 To 1) 工作表陣列 For Each mysht In Worksheets myarray(mysht.index, 1) = mysht.name Next With Worksheets.Add.Range("A1").Resize(UBound(myArray), 1) 第 23 頁

26 .Value = myarray.sort _ Key1:=.Range("A1"), _ Header:=xlNo, _ Orientation:=xlTopToBottom For i =.Cells.Count To 1 Step -1 Worksheets(.Cells(i).Value).Move _ Before:=Worksheets(1) Next Application.DisplayAlerts = False.Parent.Delete Application.DisplayAlerts = True End With 自動程序設計 Sub Auto_Open() Application.OnKey "%{F11}", "SHOW" SUB SHOW() RANGE( A1 ).VALUE=123 END SUB 第 24 頁

27 組合鍵組合鍵符號組合鍵符號 ALT % SHIFT + CTRL ^ ALT+F11 %{F11} 一般鍵鍵盤符號鍵盤符號鍵盤符號 BACKSPACE {bs} HOME {HOME} ENTER {ENTER} -> {RIGHT} {LEFT} {UP} {DOWN} F1 F12 {F1} {F12} ESC {ESC} 匯入檔案 Sub 匯入檔案 () Dim mycnc1 Dim mycnc2 As String As String Dim myfilename As String 讀入檔案 myfilename = "F_Data.csv" ' Worksheets.Add ' 工作表的新增 mycnc1 = "TEXT;" mycnc2 = ThisWorkbook.Path & "\" & myfilename With ActiveSheet.QueryTables.Add( Connection:=myCnc1 & mycnc2,destination:=range("a1")) ' 讀入目標儲存格.TextFilePlatform = 950 ' 第 25 頁

28 文字碼的指定.TextFileCommaDelimiter = True ' 格式的指定 ( 標準 ).TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1) ' 在欄位中指定格式時 '.TextFileColumnDataTypes = Array(2, 1, 9, 9, 9, 9, 9, 1).Refresh End With 區域號碼 : 儲存格值的控制 [ 工作表 ] ' 工作表模組 Private Sub Worksheet_Change(ByVal Target As Range) Dim x x = Left(Target.Address, 2) If x = "$A" Then Select Case Target.Value Case 2 Target.Value = " 台北 " Case 3 第 26 頁

29 Target.Value = " 桃園 " Case 4 Target.Value = " 台中 " Case 5 Target.Value = " 嘉義 " Case 6 Target.Value = " 台東 " Case 7 Target.Value = " 高雄 " End Select End If 清除全部註解 Sub 清除全部註解 () Cells.Select Selection.ClearComments ActiveCell.Select 取出註解 Sub 取出註解 () 第 27 頁

30 Dim myrng As Range Dim mycmt As Comment Dim i For i = 0 To 10 Step 1 Set myrng = ActiveCell.Offset(i, 0) ' 任意的儲存格 With myrng On Error Resume Next Set mycmt =.Comment On Error GoTo 0 If mycmt Is Nothing Then Else ActiveCell.Offset(i, 1).Value = mycmt.text End If End With Next Set myrng = Nothing ' 物件的釋放活頁簿控制 Set mycmt = Nothing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 第 28 頁

31 Dim x x = Left(Target.Address, 2) If x = "$A" Then Select Case Target.Value Case 2 Target.Value = " 台北 " Case 3 Target.Value = " 桃園 " Case 4 Target.Value = " 台中 " Case 5 Target.Value = " 嘉義 " Case 6 Target.Value = " 台東 " Case 7 Target.Value = " 高雄 " End select End If 第 29 頁

Excel VBA Excel Visual Basic for Application

Excel VBA Excel Visual Basic for Application Excel VBA Jun5,00 Sub 分頁 () Dim i As Integer Dim Cname As String Dim Code As Variant Set score=thisworkbook.sheets("sheet") Code=Array(" 專北一 "," 專北二 "," 專北三 "," 專桃園 "," 專桃竹 "," 專中苗 ", " 專台中 "," 專台南 ","