Microsoft Word - ch doc

第一個 Excel VBA 程式 認識 Excel VBA 的物件架構 學習物件的屬性 方法和事件 學習如何指定儲存格 學習如何自行撰寫程序 認識 Excel VBA 編輯環境 學習 VBA 程式如何編輯和除錯 學習如何設定 Excel VBA 專案的保護

2.1 Excel VBA 物件架構 物件導向程式就是模擬真實世界所發展出來的概念, 適合用來發展大型的程式 基本上 Excel VBA 是符合物件導向程式設計理念, 所以想要學習 Excel VBA 就要對物件 (Object) 有所了解 2.1.1 類別與物件在真實世界中每個人或事物都是物件, 例如 : 張慧妹 周傑倫 貴賓狗 籃球... 等都是物件 物件具有其可識別的特性或稱屬性 (Attribute), 具有相同屬性而屬性值不同的物件可歸成同一個類別 (Class), 例如 : 張慧妹和周傑倫都同屬 人 這個類別 ; 貴賓狗 博美狗都同屬 犬 這個類別 至於張慧妹和周傑倫雖同屬人類, 但由於屬性值不同視為不同的物件 在物件導向程式中, 物件是由類別所建立的實體 物件在 物件模型 階層結構中, 被有系統地彼此相關聯,Excel 中基本物件模型包含 :Application(Excel 應用程式 ) Workbooks( 活頁簿集合 ) Worksheets( 工作表集合 ) Charts( 圖表集合 ) Range( 儲存格範圍 ) Cells ( 儲存格 ) 等多種物件 其物件架構如下圖所示 : Application Workbooks( 活頁簿集合 ) Workbook Worksheets( 工作表集合 ) Range Worksheet 2-2

二. 使用 Cells Cells 也是一個儲存格的範圍, 只是指定時儲存格是使用 [R1C1] 欄名列號表示法, 列和欄都是以數字標示, 其指定的語法為 : 語法 : Cells ( 水平列編號, 垂直欄編號 ) 因為列和欄都是以數字標示, 所以特別適合於用變數指定儲存格時 例如 : 要指定 B4 儲存格時, 列編號為 4 欄編號則為 2(A 是 1 B 是 2...), 所以其寫法為 : Cells(4, 2) ' 相當於 Range("B4") 注意 存取某一範圍資料時, 若使用 For Next 迴圈時使用 Cells 較方便 ; 若使用 For Each Next 迴圈時, 使用 Range 較有效率 2.2 自行撰寫巨集程序 在第一章中介紹錄製巨集和執行巨集的方法時, 雖然錄製巨集可自動產生程式碼, 但是功能有限而且使用上綁手綁腳 若能了解 Excel VBA 物件的基本架構, 以及指定儲存格的語法後, 便可自行撰寫巨集程序的程式碼 本節將透過一個簡單的實作, 來介紹自行撰寫巨集程序的基本步驟 FileName:First.xlsm 設計一個在格的巨集程序 按鈕控制項上按一下, 會將目前時間顯示在 B1 儲存 2-6

第一個 Excel VBA 程式 操作步驟一. 建立工作表內容 1. 新增一個空白活頁簿, 並在工作表名稱為 工作表 1 的工作表上建立下面內容 : 2. 以 First 為檔名, 存檔類型選取 Excel 啟用巨集的活頁簿 存檔, 儲存後該 Excel 應用程式的檔名設為 First.xlsm 二. 新增巨集程序 1. 在功能區上點選 開發人員 索引標籤頁, 然後按其中的巨集鈕圖示, 此時會出現下圖 巨集 對話方塊 1 4 3 2 2. 上圖 巨集 對話方塊中 : 在 巨集名稱 (M) 文字方塊內輸入巨集名稱 Time 在 巨集存放在 (A) 清單中選取 First.xlsm, 指定巨集存放的位置 按鈕, 自動開啟 VBA 編輯器, 並且在 Module1 模組中新增一個巨集名稱為 Time 的巨集 2-7

三. 撰寫程式碼 1. 先在上圖 Sub Time() 的下一行敘述按 <Tab> 鍵採向右縮排方式編寫程式碼以方便閱讀 由鍵盤鍵入 range( 後, 如下圖馬上出現 Range 語法提示訊息提醒寫法 注意 range 我們是用小寫字母, 當輸入完畢後系統會自動轉成大寫, 如果沒有轉換表示拼寫錯誤 2. 輸入 range("b1"). 後, 清單會自動列出適用的屬性或方法, 而且輸入字母越多清單項目就會越接近 若我們需要的屬性或方法變成第一個項目時, 只要按 <Tab> 鍵或用滑鼠點選, 就會自動完成, 這就是編輯器的智慧感知 (IntelliSense) 功能 1 3. 輸入 range("b1").value=now() 後, 輸入游標移到下一行時, 系統會自動調整程式碼, 該變大寫 加空白... 等都會自動完成 2-8

第一個 Excel VBA 程式 4. 上面 "Range("B1").Value = Now()" 敘述的意義, 是將執行 Now() 函數所取得目前的時間設為 B1 儲存格的值, 即將資料顯示到 B1 儲存格上 5. 完成程式碼 FileName: First.xlsm ( Module1 程式碼 ) 01 Sub Time() 02 Range("B1").Value = Now() 03 End Sub 四. 建立按鈕控制項並指定巨集 1. 在功能區上點選 開發人員 索引標籤頁, 然後按插入圖示鈕, 由清單中選取適當的表單按鈕控制項 2. 移動滑鼠到要安置按鈕的位置上按一下, 此時會出現 指定巨集 對話方塊, 點選 Time 巨集名稱後, 按鈕會在工作表上出現一個按鈕控制項 3. 將按鈕上面預設的 Command1 文字修改為 目前時間, 變成按鈕並調整按鈕控制項的大小 五. 測試巨集 1. 按鈕測試執行結果是否正確? 隨堂測驗在上面實作中增加一個 Clear 巨集, 使用 Cells 格式將 B1 儲存格的內容清空, 並將該巨集指定給按鈕 [ 提示 ] 要清空儲存格的內容, 可以將 Value 屬性值設為 ""( 空字串 ) 2-9

2.3 Excel VBA 程式編輯器 在上一節我們透過實作, 認識了自行撰寫巨集程序的基本方法 本節將介紹 Excel VBA 程式編輯器的環境, 以及撰寫程式的基本概念 2.3.1 開啟 Excel VBA 程式編輯器要進入 Excel VBA 程式編輯器 (Visual Basic Editor), 除了第一章所介紹由開啟巨集進入外, 也可以在 開發人員 索引標籤, 按其中的鈕, 來開啟 VBA 編輯視窗 利用 <Alt> + <F11> 快速鍵, 也可以直接開啟 Excel VBA 程式編器 專案總管視窗屬性視窗 程式碼視窗 工具列 功能表列 2.3.2 功能表與工具列 一. 功能表列 VBA 編輯視窗的功能都分門別類, 集中在功能表列內供使用者選用 2-10

第一個 Excel VBA 程式 二. 工具列 VBA 編輯視窗的主要功能以圖示鈕的形式, 集中在工具列供使用者快速選用 功能說明 1. : 檢視 Excel 視窗, 按此圖示鈕會切換到 Excel 視窗 2. : 共有四種選項 ( 插入自訂表單 ) ( 插入模組 ) ( 插入物件類別模組 ) ( 插入程序 ) 3. : 分別是執行 暫停 停止程序或自訂表單 (UserForm) 4. : 開啟或關閉設計模式, 可用來編輯控制項屬性 5. : 分別開啟專案總管 屬性 瀏覽物件視窗 2.3.3 專案總管視窗 專案總管視窗將每個 Excel 活頁簿檔案視為一個專案 (Project), 視窗內用階層方式來顯示專案中的所有項目 可折疊資料夾 說明 1. 專案的最上層為 Excel 活頁簿檔案, 目前下面有 Microsoft Excel 物件 模組兩個資料夾, 以樹狀圖形式呈現 Microsoft Excel 物件資料夾內包含 ThisWorkbook( 代表整個活頁簿 ) 和工作表物件 (WorkSheet) 等物件 2-11

模組資料夾內包含模組 (Module), 模組內含各種巨集程序 預設第一個模組名稱為 Module1, 第二個模組名稱為 Module2 依此類推 如果有插入其他物件, 則會有表單 ( 內含自訂表單 UserForm) 和物件類別模組資料夾 2. 專案總管視窗內除列出物件名稱外, 還會在 () 括弧內顯示物件的 Name 屬性值, 例如 : 工作表 1( 工作表 1) 3. 專案總管的工具列有三個工具視窗 : ( 檢視程式碼 ): 先點選工作表 模組... 等物件, 然後按就可以開啟該物件的程式碼視窗 ( 檢視物件 ): 先點選工作表 自訂表單... 等物件, 然後按就可以切換到該物件的視窗 例如點選工作表 2 然後按該鈕, 就會切換到 Excel 的工作表 2 ( 切換資料夾 ): 顯示或隱藏物件資料夾 顯示物件資料夾 隱藏物件資料夾 如果同時開啟多個 Excel 活頁簿檔案, 這些 Excel 檔案會共用一個 VBA 程式編輯器 在專案總管視窗中會以樹狀圖清楚呈現階層關係, 編輯和儲存巨集程序時要特別注意 2-12

14.1 圖表物件簡介 14.1.1 圖表物件簡介圖表是 Excel 非常重要的工具之一, 俗語說 : 一圖勝於千言萬語, 它可以將數據以圖形方式來呈現數據資料 圖表在 Excel 中有兩種呈現的方式 : 1. 內嵌圖表圖表是工作表中的一個物件, 當希望圖表和數據資料放在同一個工作表中, 或是有多個圖表同時顯現時, 就可以採用內嵌圖表 ( 或稱嵌入圖表 ) 每一個內嵌圖表就是一個 Chart 物件, 包含在 ChartObject 物件中 ChartObject 物件是 Chart 物件的容器, 透過 ChartObject 物件的屬性和方法可以設定內嵌圖表的外觀和大小 每個工作表都有一個 ChartObjects 集合,ChartObject 物件會存在其中 2. 圖表工作表圖表單獨成為一個工作表, 當希望圖表以最大尺寸顯示時, 就可以採用圖表工作表 Chart 物件本身就是圖表工作表, 和內嵌圖表不同, 此時並不需要包含在 ChartObject 物件中, 所以圖表工作表中圖表的位置是固定, 大小是取決於工作表的大小 每個活頁簿都有一個 Charts 集合, 集合中包含該活頁簿中的所有圖表工作表, 但不會包含內嵌圖表 要特別注意, 活頁簿的 Sheets 集合也會包含 Charts 物件 14.1.2 圖表物件的建立 1. 內嵌圖表 要建立內嵌圖表時可以使用 ChartObjects 集合的 Add 方法, 其語法如下 : 語法 : 工作表.ChartObjects.Add(Left, Top, Width, Height) 說明 Left Top 引數是指定圖表位置,Width Height 引數指定圖表尺寸, 單位 14-2

圖表 Chart 物件介紹 為點 (points),1 point = 1/72 英吋 因為內嵌圖表會存在 ChartObjects 集合中, 所以可以用索引值來指定內嵌圖表, 例如 :Worksheets(1).ChartObjects(1) 可以指定第一個內嵌圖表 建立時系統會自動為圖表命名, 預設名稱為圖表 1 圖表 2... 依此類推, 要注意的是數字前有一個空白字元, 當然也可以使用 Name 屬性自行命名 如果是目前作用的圖表, 則用 ActiveChart 表示 例如 : 在 < 工作表 1> 座標值 (50, 100) 上, 建立寬度 300 點 高度為 200 點的空白內嵌圖表, 寫法如下 : Dim co As ChartObject : Dim ch As Chart Set co = Worksheets(" 工作表 1").ChartObjects.Add(50, 100, 300, 200) Set ch = co.chart 如果要逐一選取 < 工作表 1> 中的各個內嵌圖表, 寫法為 : For Each co In Worksheets(" 工作表 1").ChartObjects co.select Next 2. 圖表工作表要建立圖表工作表時, 可以使用活頁簿 Charts 集合的 Add 方法, 其語法如下 : 語法 : Charts.Add([Before ] [, After ] [, Count ]) 說明 在 Add 方法中可以用 Before 或 After 引數來指定在哪個工作表之前或之後, 省略時會新增在作用工作表的前面 Count 引數可以指定新增圖表工作表的數量, 預設為一個 圖表工作表會存在 Charts 集合中, 所以可以用索引值來指定圖表工作表, 例如 : ThisWorkbook.Charts(1) 是指定第一個圖表工作表 14-3

建立時系統會為圖表工作表命名, 預設名稱為 Chart1 Chart2... 依此類推 可以用名稱來指定圖表工作表, 例如 :ThisWorkbook.Charts("Chart1") 如果是目前作用的圖表工作表, 則用 ActiveChart 表示 例如 : 在目前作用活頁簿的最後一個工作表前面, 新增一個空白圖表工作表, 寫法如下 : Dim ch As Chart Set ch = ActiveWorkbook.Charts.Add(Before:=Worksheets(Worksheets.Count)) 如果要逐一選取活頁簿中的各個圖表工作表, 寫法為 : For i = 1 To ActiveWorkbook.Charts.Count ActiveWorkbook.Charts(i).Activate Next 14.1.3 設定資料來源用 Add 方法建立空白圖表之後, 可以使用 SetSourceData 方法設定圖表的資料來源, 其語法為 : 語法 : 圖表物件.SetSourceData(Source, [PlotBy]) 說明 1. Source 引數是指定資料來源, 其資料型別為儲存格範圍 2. PlotBy 引數是指定繪製資料的方式, 其引數值為 : xlrows( 類別軸的項目依水平列, 預設值 ) 和 xlcolumns( 類別軸的項目依垂直欄 ) 3. 例如 : 指定 ch 圖表物件的資料來源為工作表 1 的 A1:E4 儲存格範圍, 且資料數列在欄中, 寫法為 : ch.setsourcedata Source:= Worksheets(" 工作表 1").Range("A1:E4"), PlotBy:=xlRows 14-4

圖表 Chart 物件介紹 14.1.4 圖表物件的組成 新增一個圖表後, 圖表中主要的組成項目如下 : 資料來源 圖表區 數列 1~ 數列 3 資料 圖表標題 垂直 ( 數值 ) 軸主要格線 繪圖區 垂直 ( 數值 ) 軸 水平 ( 類別 ) 軸 圖例 數列 1 數列 2 數列 3 FileName:AddChart.xlsm 按鈕會將 A1:F5 儲存格範圍的資料建立成一個內嵌圖表 如果原來已經有圖表, 就先將圖表刪除 圖表位置接在 A1:F5 儲存格範圍下面, 寬度為 300 點 高度為 200 點 輸出要求 14-5

解題技巧建立輸出入介面 1. 新增活頁簿並以 AddChart 為新活頁簿名稱 2. 在 < 工作表 1> 中建立如下表格, 和一個 ActiveX 命令按鈕控制項 : cmdadd 問題分析 1. 如果 ChartObjects.Count > 0 表示工作表上已經有圖表, 可透過 Delecte 方法將圖表刪除圖件, 假設工作表物件為 ws, 其寫法為 :ws.chartobjects.delete 2. 先使用 Add 方法建立 ChartObject 物件, 再設定 SetSourceData 屬性值為 A1:F5 儲存格範圍 編寫程式碼 FileName: AddChart.xlsm ( 工作表 1 程式碼 ) 01 Private Sub cmdadd_click() 02 Dim ws As Worksheet ' 宣告 ws 為工作表物件 03 Dim co As ChartObject ' 宣告 co 為 ChartObject 物件 04 Dim ch As Chart ' 宣告 ch 為圖表物件 05 Set ws = ThisWorkbook.Worksheets(" 工作表 1") ' 設 ws 為工作表 1 06 If ws.chartobjects.count > 0 Then ' 如果 ChartObject 物件集合數量 >0 07 ws.chartobjects.delete ' 移除內嵌圖表 08 End If 09 ' 在 A6 儲存格上建立寬 300 高 200 的圖表 10 Set co = ws.chartobjects.add(ws.range("a6").left, ws.range("a6").top, 300, 200) 11 Set ch = co.chart ' 設 ch 為 co 的圖表 12 ch.setsourcedata Source:=ws.Range("A1:F5") ' 設 ch 的資料來源為 A1:F5 儲存格範圍 13 End Sub 14-6

圖表 Chart 物件介紹 隨堂練習 將上面實作改為建立成圖表工作表 14.2 圖表物件常用成員 圖表雖然分成內嵌圖表和圖表工作表兩種, 但是圖表的成員部分是相同, 下面介紹常用的共通成員 : 14.2.1 圖表類型與標題 1. ChartType 屬性使用 ChartType 屬性可以設定或讀取圖表類型, 預設值為 xlcolumnclustered ( 群組直條圖 ), 其它屬性值請參考附錄 C 例如: 設 ch 圖表物件的圖表類型為折線圖, 寫法為 : ch.charttype = xlline 例如 : 當 ch 圖表物件的圖表類型為圓形圖時就顯示提示訊息, 寫法為 : If ch.charttype = xlpie Then MsgBox " 圖表類型為圓形圖 " 14-7

2. HasTitle/ChartTitle 屬性 使用 HasTitle 屬性可以設定和取得圖表或座標軸是否有標題, 屬性值為 True 時表標題可見 當 HasTitle 屬性值為 True 時, 才可以使用 ChartTitle 屬性來設定圖表標題的文字內容和樣式 ChartTitle 屬性是一個物件, 可以透過 Text 屬性來設定圖表標題的文字內容 ;Left 和 Top 屬性來設定圖表標題的位置 ;Font 屬性來設定圖表標題的字體樣式 ;Interior 屬性來設定圖表標題的背景樣式 例如 :ch 為圖表物件設定該圖表標題文字為 年度報表, 寫法為 : ch.hastitle = True ch.charttitle.text = " 年度報表 " FileName:ChartType.xlsm 使用者可以由下拉式清單方塊中, 選取 xlcolumnclustered( 群組直條圖 ; 預設值 ) xl3dbarclustered( 立體群組橫條圖 ) xl3dcolumn( 立體直條圖 ) xlline( 折線圖 ) 等四種圖表類型 圖表標題預設為 消費統計圖表, 使用者也可以自行輸入 按鈕時即會根據設定值建立圖表工作表 輸出要求 14-8

圖表 Chart 物件介紹 圖表標題 解題技巧建立輸出入介面 1. 新增活頁簿並以 ChartType 為新活頁簿名稱 2. 在 < 工作表 1> 工作表中建立如下表格, 以及 ActiveX 下拉式清單方塊 文字方塊和命令按鈕控制項 : cbotype txttitle cmdadd 問題分析 1. 在 Workbook_Open 事件程序中設定各控制項的屬性, 和各控制項的預設值 2. 建立一個 Array 物件 arytype, 來儲存各圖表類型的參數值 利用 arytype 和下拉式清單方塊的清單索引值, 設圖表的 ChartType 屬性值為 arytype( 索引值 ) 就可以指定圖表類型 3. 設定圖表的 HasTitle 屬性值為 True, 顯示圖表標題 設定圖表的 ChartTitle.Text 屬性值為 txttitle.text, 設圖表標題等於文字方塊的 Text 屬性值 14-9

編寫程式碼 FileName: ChartType.xlsm (ThisWorkbook 程式碼 ) 01 Private Sub Workbook_Open() 02 Dim arytype As Variant 03 arytype = Array("xlColumnClustered", "xl3dbarclustered", "xl3dcolumn", "xlline") 04 With Sheets(" 工作表 1") 05.cboType.List = arytype ' 將陣列設為清單項目 06.cboType.ListIndex = 0 ' 預設選取第一個項目 07.txtTitle.Text = " 消費統計圖表 " 08 End With 09 End Sub FileName: ChartType.xlsm ( 工作表 1 程式碼 ) 01 Private Sub cmdadd_click() 02 Dim ws As Worksheet ' 宣告 ws 為工作表物件 03 Dim ch As Chart ' 宣告 ch 為圖表物件 04 Set ws = ThisWorkbook.Worksheets(" 工作表 1") ' 設 ws 為工作表 1 05 If ActiveWorkbook.Charts.Count > 0 Then ' 如果 Charts 物件集合數量 >0 06 ActiveWorkbook.Charts.Delete ' 移除圖表工作表 07 End If 08 Set ch = ActiveWorkbook.Charts.Add 09 Dim arytype As Variant 10 arytype = Array(xlColumnClustered, xl3dbarclustered, xl3dcolumn, xlline) 11 ch.setsourcedata Source:=ws.Range("A1:F5") ' 設 ch 的資料來源為 A1:F5 儲存格範圍 12 ch.charttype = arytype(cbotype.listindex) ' 設 ChartType 屬性值為清單的索引值 13 ch.hastitle = True ' 顯示圖表標題 14 ch.charttitle.text = txttitle.text ' 設圖表標題為文字方塊的 Text 屬性值 15 End Sub 隨堂練習 將上面實作改為建立內嵌圖表 14-10