Microsoft Word - VBA講義手冊.docx

Excel VBA 設計與應用 黃老師自編講義

目錄 一 開啟開發人員之功能... 1 二 巨集安全性處理... 1 三 巨集之錄製... 1 四 按鈕設計... 2 五 開啟 VBA 進入 VBE... 2 六 程式架構 編輯及除錯... 3 七 基礎程式語法... 3 八 資料型態... 4 九 算術運算子... 5 十 迴圈... 6 十一 條件分支之處理... 9 十二 陣列... 11 十三 副程式... 12 十四 常使用的物件... 14 i

一 開啟開發人員之功能 開啟 Excel 檔案 選項 自訂功能區 主要索引標籤 / v 開發人員 確定 1 2 3 4 二 巨集安全性處理 巨集安全性 巨集設定 停用所有巨集 ( 事先通知 ) 確定 ** 若已有就不需設定 : 巨集安全性 信任位置 新增 瀏覽 C:\Program Files\Microsoft Office\Templates\ 確定 ) 三 巨集之錄製 1. 開發人員 以相對位置錄製 錄製巨集 2. 巨集名稱 : 快速鍵 確定 3. 操作 4. 開發人員 停止錄製 5. 另存成.xlsm 1

四 按鈕設計 1. 插入 表單控制項 / 按鈕 拖曳區塊 2. 選巨集名稱 確定 3. 修改按鈕區塊文字內容簡易方法 : 插入 圖案 選圖並拖曳完成圖形 打入文字 按滑鼠右鍵 指定巨集 選對應的巨集 確定 例 1( 薪水條 ), 練習如何錄製巨集 執行巨集及存檔 例 2( 基本運算 ), 練習如何錄製巨集 執行巨集及存檔 五 開啟 VBA 進入 VBE 開啟 VBA(Visual Basic Application) 進入 VBE(Visual Basic Editor) 1. 開發人員 Vasiual Basic ( 或按 Alt+F11 Excel 與 Visual Basic 互換鍵 ) 2. 在專案視窗的 VBA Project 按滑鼠右鍵 插入 模組, 進入 VBE 畫面 ( 專案視窗的模組會出現 Module1) 1 2 3 2

六 程式架構 編輯及除錯 語法 Sub 程序名稱 () 程式碼 ( 程式語法 ) ** 程式架構 編輯及除錯之詳細內容於課堂內說明 七 基礎程式語法 語法 說明 MsgBox( 文字內容 ) 顯示文字內容 InputBox( 文字內容 ) 輸入資料之交談視窗 Dim 變數名稱 As 資料型態 指定變數之資料型態 文字內容 備註 Const 常數名稱 As 資料型態 = 常數值 常數宣告 ( 或 Const 常數名稱 = 常數值 ) 例 3 Sub Ex1( ) MsgBox( 大家好 ) 例 4 Sub Ex2( ) InputBox( 請輸入一個數字 ) 例 5 Sub Ex3( ) InputBox( 請輸入一個數字 ) a= InputBox( 請輸入另一個數字 ) MsgBox(a) ** 程式要存成.xlsm 之存檔類型 3

八 資料型態 類型 資料型態 說明 布林 Boolean True/False 位元組 Byte 0~255 整數 Integer 32768~32767 長整數 Long 2147483648~2147483647 貨幣 Currency 單精度浮點數 Single 雙精度浮點數 Double 日期時間 Date 字串 String 物件 Object 通用型態 Variant 可以是任何資料類型 例 6: 練習資料型態 Sub Ex1() Dim a As Integer Dim b As Integer a=5 b=a MsgBox(b) 4

九 算術運算子 運算子說明 + 加 減 * 乘 / 除 ^ 次方 \ 商數 Mod 餘數 例 7: 練習算術運算 Sub Ex1() Dim a As Integer Dim b As Integer a=5 b=3 c=a*b MsgBox ("a=" & a & ",b=" & b & ",a*b=" & c) 5

十 迴圈 1. 迴圈次數從起始值執行到結束值語法 For 迴圈變數 = 起始值 to 結束值敘述 Next 2. 先判斷成立與否, 不成立則執行, 成立則不執行語法 DO Until 條件敘述 Loop 3. 先執行, 再判斷成立與否, 不成立則執行, 成立則不執行語法 DO 敘述 Loop Until 條件 4. 先判斷成立與否, 成立則執行, 不成立則不執行語法 DO While 條件敘述 Loop 5. 先執行, 再判斷成立與否, 成立則執行, 不成立則不執行語法 DO 敘述 Loop While 條件 6

例 8: 下載 ForDo.xlsx 練習迴圈 1+2+3+.+10=? Sub Ex1() Dim Total As Integer Dim I As Integer Total = 0 For I = 1 To 10 Total = Total + I Next I MsgBox ("(1)1+2+3+4+5+6+7+8+9+10 = " & Total) Range("B1").Value = Total Sub Ex2() Dim Total As Integer Dim I As Integer Total = 0 I = 0 Do Until I = 10 I = I + 1 Total = Total + I Loop MsgBox ("(2)1+2+3+4+5+6+7+8+9+10 = " & Total) Range("B2").Value = Total Sub Ex3() Dim Total As Integer Dim I As Integer Total = 0 I = 0 Do I = I + 1 Total = Total + I Loop Until I = 10 MsgBox ("(3)1+2+3+4+5+6+7+8+9+10 = " & Total) Range("B3").Value = Total 7

Sub Ex4() Dim Total As Integer Dim I As Integer Total = 0 I = 0 Do While I < 10 I = I + 1 Total = Total + I Loop MsgBox ("(4)1+2+3+4+5+6+7+8+9+10 = " & Total) Range("B4").Value = Total Sub Ex5() Dim Total As Integer Dim I As Integer Total = 0 I = 0 Do I = I + 1 Total = Total + I Loop While I < 10 MsgBox ("(5)1+2+3+4+5+6+7+8+9+10 = " & Total) Range("B5").Value = Total 8

十一 條件分支之處理 1. 單一條件語法 If 條件 Then 敘述 1 End if 2. 多個條件語法 If 條件 1 Then 敘述 1 ElseIf 條件 2 Then 敘述 2 ElseIf 條件 3 Then 敘述 3 Else 敘述 4 End If 例 9: 下載 ifcase.xlsx 利用 if 完成等第欄位 Sub test() irow=2 For i= 1 to 42 irow=irow+1 If Range( J & irow).value>=90 Then Range( K & irow).value= 甲 ElseIf Range( J & irow).value>=80 Then Range( K & irow).value= 乙 ElseIf Range( J & irow).value>=70 Then Range( K & irow).value= 丙 ElseIf Range( J & irow).value>=60 Then Range( K & irow).value= 丁 Else Range( K & irow).value= 戊 End If Next i 9

3. Select Case 語法 Select Case 變數 Case 條件值 1 敘述 1 Case 條件值 2 敘述 2 Case 條件值 3 敘述 3 Case Else 敘述 4 End Select **Case 條件值之例子 Case 1,3,5 Case 2 to 5 Case Is>=5 Case A 例 10: 下載 ifcase.xlsx 利用 Select case 完成等第欄位 Sub test() irow=2 For i= 1 to 42 irow=irow+1 Select Case Range( J & irow).value Case Is >=90 Range( K & irow).value= 甲 Case Is >=80 Range( K & irow).value= 乙 Case Is >=70 Range( K & irow).value= 丙 Case Is >=60 Range( K & irow).value= 丁 Case Else Range( K & irow).value= 戊 End Select Next i 10

十二 陣列 Option Base 1 宣告陣列起始為 1 Dim 陣列名稱 ( ) As 資料型態 宣告陣列大小 Array( 元素 1, 元素 2, 元素 3,.) 新增陣列 例 11: 練習陣列, 課堂內說明 11

十三 副程式 VBA 之函數字串函數 Replace( 字串, 搜尋字串, UCase( 字串 ) 將小寫轉大寫 Lcase( 字串 ) 將大寫轉小寫 StrConv( 字串, 轉變類型 ) 將字串轉換顯示方式 vbuppercase 轉成大寫 vblowercase 轉成小寫 vbpropercase 第一字大寫 vbwide 轉成全形 vbnarrow 轉成半形 Len( 字串 ) 字串字數 Left( 字串, 字數長度 ) 回傳從左到右指定字數 Right( 字串, 字數長度 ) 回傳從右到左指定字數 Mid( 字串, 起始位置, 字數長度 ) 回傳從起始位置開始指定字數 InStr( 字串, 搜尋字串 ) 字串在搜尋字串第幾位置 InStrRev( 字串, 搜尋字串 ) 字串在反向搜尋字串第幾位置 trim( 字串 ) 將字串左右多餘空白移除 取代字串 ) 將搜尋字串換成取代字串 String( 次數, 字串 ) 依指定的次數重複字串 數值函數 Abs( 數值 ) 絕對值 Log( 數值 ) 述職的自然對數 Exp( 數值 ) 以 e 為底的數值次方 Sqr( 數值 ) 數值的平方根 Fix( 數值 ) 整數的部分, 捨小數 Int( 數值 ) 不大於該數的最大整數 Round( 數值, 小數位數 ) 四捨五入留指定的位數, 日期時間函數 DateAdd( 單位, 數值, 日期 ) 日期加上單位數值後的日期單位 yyy 年,q 季,m 月,y 年,d 日,w 週日,ww 週,h 時,n 分,s 秒 DateDiff( 單位, 日期 1, 日期 2) 兩個日期的單位差 Now 回傳現在日期時間 Date 回傳現在日期 Time 回傳現在時間 Year( 日期資料 ) Date 資料中的西元年 Month( 日期資料 ) Date 資料中的月 Day( 日期資料 ) Date 資料中的日 Hour( 日期資料 ) Date 資料中的時 Minute( 日期資料 ) Date 資料中的分 Second( 日期資料 ) Date 資料中的秒 Weekday( 日期資料 ) Date 資料中的星期 其他函數 RGB( 紅數值, 綠數值, 藍數值 ) 紅, 綠, 藍顏色值 0~255 12

例 12: 下載 function.xlsx, 練習函數, Sub Ex() Dim a As integer Dim b As String Dim c As integer Dim d As integer Dim e As Date Dim F As Date a=len( VBA ) b=trim( VBA ) c=len(b) d=round(23.59,1) e=now f=year(e) 13

十四 常使用的物件 物件名稱 說明 Range( 儲存格位址 ) 儲存格 Cells( 列號, 欄號 ) 儲存格 Worksheet 工作表 Workbook 活頁簿 Window Excel 視窗 Application Excel 相關設定 ** 物件的屬性 ( 或方法 ) 在物件後方加上. 屬性 ( 或方法 ) 1.Range * 常見之屬性 : Value( 值 ),Address( 位址 ),Font( 字型 ),Borders( 四邊框線集合, 可再加下層屬性 Weight,Color,LineStyle),Name ( 儲存格範圍名稱 ),Interior ( 儲存格格式 ),RowHeight(),Row( 第幾列 ),Column( 第幾欄 ),Rows( 範圍列 ), Columns( 範圍欄 ),Height( 儲存格高度 ),Width( 儲存格寬度 ),End( 方向最終儲存格集合 ),Formula( 儲存格公式 ), 例 13: 下載 formula.xlsx 完成總分欄位 Sub formula1() Range("E2:E16").FormulaR1C1 = "=RC[ 2]+RC[ 1]" * 常見之方法 : Clear( 清除 ),Delete( 刪除 ),Selection( 選取多格 ),ActiveCell( 只選單一格 ), AutoFilter( 篩選功能 ),AddComment( 註解內容 ),ClearComments( 清除註解內容 ),Merge( 合併儲存格 ),UnMerge( 解除合併儲存格 ),Insert( 插入儲存格 ),Copy( 複製貼上儲存格 ),Cut( 剪下貼上儲存格 ) 例 14: 下載 border.xlsx 練習 Sub Test1() Range("B2:C2").Borders.LineStyle = xlcontinuous B2 到 C2 加一般框線 14

Sub Test2() Range("D2:E2").Borders(xlEdgeTop).Weight = xlthin Range("D2:E2").Borders(xlEdgeTop).LineStyle = xldot D2 到 E2 上方加細框線, 再用點線顯示 Sub Test3() Range("A2").Borders(xlDiagonalUp).Color = vbred A2 加上右上往左下紅線 2. Worksheets * 常見之屬性 : Index( 回傳工作表索引編號 ),Previous( 回傳左邊工作表物件 ),Next( 回傳右邊工作表物件 ),Count( 工作表個數 ),Name ( 工作表名稱 ),UsedRange( 已使用儲存格範圍 ),Visible( 顯示或隱藏 ) * 常見之方法 : Select( 選取工作表 ),Delete( 刪除工作表 ),Add( 插入工作表 ),Copy( 複製工作表 ),Move( 移動工作表 ),PrintOut( 列印工作表 ),PrintPreview( 預覽列印工作表 ),Activate( 啟動工作表 ) 3. WorkBooks * 常見之屬性 : Open( 開啟活頁簿 ),Count( 活頁簿個數 ),Name( 活頁簿名稱 ),FullName( 路徑及檔名 ) * 常見之方法 : Add( 新增活頁簿 ),Svae( 儲存活頁簿 ),SaveAs( 將活頁簿另存 ),PrintOut( 列印活頁簿 ),PrintPreview( 預覽列印活頁簿 ),Close( 關閉活頁簿 ) ** WorkBooks( 路徑 \ 活頁簿名稱 ).Close(True) 存檔再關檔 WorkBooks( 路徑 \ 活頁簿名稱 ).Close(False) 不存檔就關檔 例 15: 下載 object.xlsx 練習物件 Sub Ex1() Range("A3").Copy Range("A12") 將儲存格 A3 複製到儲存格 A12 15

Sub Ex2() Range("A12").Cut Range("A13") 將儲存格 A12 剪貼到儲存格 A13 Sub Ex3() Range("A2:A5").Copy Range("A15") 將儲存格範圍 A2 到 A5 複製到儲存格 A15 起點 Sub Ex4() Worksheets(2).Activate 開啟工作表 2 Sub Ex5() Worksheets(1).Select 開啟工作表 1 Sub Ex6() Worksheets(1).Copy After:=Worksheets(2) 將第 1 工作表複製到工作表 2 後面 Sub Ex7() Worksheets(3).Visible = False 隱藏第 3 張工作表 Sub Ex8() Worksheets.Add 在目前工作表前加新工作表 Sub Ex9() Workbooks(1).Save ' 存檔 16