Microsoft Word - VBA講義手冊.docx

Size: px

Start display at page:

Download "Microsoft Word - VBA講義手冊.docx"

粗司庞
5 years ago
Views:

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

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

$瀏覽 C:\Program Files\Microsoft Office\Templates\ 確定 ) 三巨集之錄製 1. 開發人員以相對位置錄製錄製巨集 2. 巨集名稱 : 快速鍵確定 3. 操作 4. 開發人員停止錄製 5. 另存成.$

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

1( 薪水條 ), 練習如何錄製巨集執行巨集及存檔例 2( 基本運算 ), 練習如何錄製巨集執行巨集及存檔五開啟

4 四按鈕設計 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)

5 六程式架構編輯及除錯語法 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

6 八資料型態類型資料型態說明布林 Boolean True/False 位元組 Byte 0~255 整數 Integer 32768~32767 長整數 Long ~ 貨幣 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

7 九算術運算子運算子說明 + 加減 * 乘 / 除 ^ 次方 \ 商數 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

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

9 例 8: 下載 ForDo.xlsx 練習迴圈 =? Sub Ex1() Dim Total As Integer Dim I As Integer Total = 0 For I = 1 To 10 Total = Total + I Next I MsgBox ("(1) = " & 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) = " & 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) = " & Total) Range("B3").Value = Total 7

10 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) = " & 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) = " & Total) Range("B5").Value = Total 8

11 十一條件分支之處理 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

12 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

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

14 十三副程式 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

15 例 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

16 十四常使用的物件物件名稱說明 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

17 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

18 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

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(" 專北一 "," 專北二 "," 專北三 "," 專桃園 "," 專桃竹 "," 專中苗 ", " 專台中 "," 專台南 ","