excelvba宏的认识和应用.ppt

台灣微軟公司台灣微軟公司台灣微軟公司台灣微軟公司 軟體顧問軟體顧問軟體顧問軟體顧問洪士吉洪士吉洪士吉洪士吉 編撰編撰編撰編撰新手入門不用怕，高手應用更拉風新手入門不用怕，高手應用更拉風 Excel VBA 巨集的認識及應用巨集的認識及應用用錄的也會通用錄的也會通-從巨集錄寫出發從巨集錄寫出發巨集的錄寫程序巨集的錄寫程序巨集的儲存位置巨集的儲存位置Excel的巨集錄寫功能，能將使用者對Excel功能表中所選定的命令及相關的滑鼠動作自動轉換成 Visual Basic 程式語句，從而製作成巨集程式來供使用者執行。因此，使用者以指定結果的方式來操作 Excel，即可不費吹灰之力完成VBA巨集的撰寫作業。編修巨集編修巨集相對錄寫與絕對錄寫相對錄寫與絕對錄寫透視透視VBA語言語言-Visual Basic 編輯環境編輯環境程式的窩程式的窩-Visual Basic 編輯環境編輯環境VBA的敲門磚的敲門磚-物件物件(Object)、屬性、屬性(Property)、方法、方法(Method)和事件和事件(Event)v物件的意義與層次結構物件的意義與層次結構v屬性的意義與例舉屬性的意義與例舉v方法的意義與例舉方法的意義與例舉v事件的意義事件的意義與例舉與例舉Run!Run!Run!-執行巨集執行巨集使用 工具/巨集 命令 使用便捷鍵將巨集指定至工具列上的按鈕將巨集指定至工作表或圖表上的物件將巨集指定至表單上的物件將巨集指定至功能表將巨集指定至快顯功能表將巨集指定至 Office 小幫手將巨集指定至事件中，當事件發生時自動執行該巨集在在Visual Basic 編輯環境中撰寫程式編輯環境中撰寫程式在程式碼視窗內內使用英文小寫來輸入關鍵字。輸入sub及巨集名稱後，當巨集程序不處理引數時，勿需再輸入”()”文字，而直接按下 Enter 鍵即可。使用 Tab 按鍵來縮排巨集程序內的各行程式碼。每行陳述式中若需輸入運算子(operator)時，其前後勿需輸入空白。編修巨集程序時，可使用拖曳方式或快顯功能表來複製或搬移文字。利用 自動完成 功能來輔助文字之輸入並使用 自動列示方法及屬性 及 列出常數 的功能來挑選相互配合的方法及屬性。VBA巨集程式的結構巨集程式的結構頂端宣告區 設定 VBA Option模組應用範圍 宣告變數 宣告常數程式區 程式碼專案視窗內各物件的程式碼視窗所登錄各區段的VBA巨集程式碼有其結構性。想要瞭解及撰寫VBA 巨集，必須對VBA巨集程式的結構有所認識。VBA的陳述式的陳述式(Statement)宣告陳述式的用途在於對程序、變數、陣列以及常數等進行命名以及定義。宣告陳述式宣告陳述式指定陳述式用於指定數值或運算式給變數或是常數。指定陳述式通常會包含一個等號（=）。設定屬性值的陳述式也是一個指定陳述式。指定陳述式指定陳述式可執行的陳述式會執行相關的動作。其可以執行函數、方法、迴圈或從某一區塊的程式碼中做分支執行。可執行的陳述式可執行的陳述式常用的物件、屬性、方法與事件常用的物件、屬性、方法與事件Excel 應用軟體被切割成上百個物件，且各物件下轄了眾多的屬性與方法。雖然絕大數的物件、屬性與方法都可以使用錄製方式來產生程式碼。然而，還是有一些物件及其相關的屬性與方法無法以錄製方式完成。況且在閱讀或編輯巨集程式碼時，必須對各行陳述式有所暸解，方能進行相關的編修。Application 群組物件及其相關方法Workbooks 群組物件及其相關方法Windows 群組物件及其相關方法Sheets 群組物件及其相關方法Range 群組物件及其相關方法Workbook_BeforeClose 事件Workbook_Open 事件Worksheet_Activate 事件Worksheet_Change事件Worksheet_SelectionChange 事件Chart_SeriesChange 事件Visual Basic 編輯環境中最佳幫手編輯環境中最佳幫手-物件瀏覽視窗物件瀏覽視窗Excel VBA具有數百個物件，而各物件又下轄眾多的屬性、方法及事件。要想強記所有的屬性、方法及事件不但是不可能且不切實際。唯有熟悉少數常用的屬性、方法及事件外，對於不熟悉的物件、屬性、方法及事件，可運用Visual Basic 編輯環境下的物件瀏覽視窗，來作為諮詢參考的工具。Excel VBA巨集程式例巨集程式例舉一舉一各縣市銷售績效報告各縣市銷售績效報告設計主控畫面設計主控畫面使用主控畫面工作表，並佈置若干物件作為介面控制工具。Range(選定地區選定地區).Formula=Application.Caller佈置工作表及圖表佈置工作表及圖表編撰巨集程式編撰巨集程式依據繪圖資料工作表內各組數據，進而繪製相關圖表。按地域性將業績資料記錄建立於工作表中，並對此業績資料庫進行彙總報告及圖形分析，在實務上係十分普遍與實用的作業。本例舉旨在使用臺灣地圖作為操作介面，就業績資料進行資料匯集、彙總報表及圖形分析。Excel VBA巨集程式例巨集程式例舉二舉二各縣市銷售績效報告各縣市銷售績效報告設計主控畫面設計主控畫面建立彙總報表及圖表建立彙總報表及圖表編撰巨集程式編撰巨集程式ActiveSheet.PivotTableWizard SourceType:=xlDatabase,SourceData:=_ 業績記錄資料庫業績記錄資料庫!Database,TableDestination:=,TableName:=_ 樞紐分析表樞紐分析表1 ActiveSheet.PivotTables(樞紐分析表樞紐分析表1).AddFields RowFields:=品名品名,_ ColumnFields:=日期日期,PageFields:=倉儲單位倉儲單位 With ActiveSheet.PivotTables(樞紐分析表樞紐分析表1).PivotFields(金額金額).Orientation=xlDataField .NumberFormat=#,#0 End With操控樞紐分析表操控樞紐分析表及圖報表及圖報表PivotTablePivotTable物件物件範例展示：全球銷售類別分析範例展示：全球銷售類別分析1.佈置資料清單。2.建置樞紐分析表及物件連結作業。3.使用VBA操控樞紐分析表及圖報表作業。Active X控制項物件控制項物件與自訂表單與自訂表單認識認識工具箱工具箱工具列工具列使用使用工具箱工具箱編製自訂表單編製自訂表單在工作表中使用在工作表中使用控制工具箱控制工具箱工具列工具列操控指令列操控指令列(CommandBar)CommandBar CommandBar 物件模型物件模型範例展示範例展示操控操控Office小幫手小幫手Assistant Assistant 與與 BalloonBalloon 物件物件範例展示範例展示建立建立 Excel 增益集增益集製作自訂式的製作自訂式的Add-In內建式的內建式的Add-In以以VBA 程式碼控制安程式碼控制安裝及使用裝及使用 Add-In保護原始程式碼保護原始程式碼 Addins(“Sample.xla”).Install=TrueAddins.add“C:Addin Sample.xla”使用使用增益功能的優點增益功能的優點提昇執行效率保護原始程式碼幕後作業，不干擾使用者強迫使用者執行WorkBook_Open或AutoOpen巨集程序更簡易地存取函數更易於在Excel Add-In對話方塊中取用更容易被 Excel 自動載入關閉離開無雜訊在在Excel內自動啟動其他應用軟體內自動啟動其他應用軟體使用使用Shell函數啟動其他應用軟體函數啟動其他應用軟體藉由藉由VBA的的Shell函數，可以開啟其他應用軟體並將該應用軟體啟動成函數，可以開啟其他應用軟體並將該應用軟體啟動成活動中的應用軟體。活動中的應用軟體。使用使用AppActive函數啟動其他應用軟體函數啟動其他應用軟體使用使用AppActivate函數可以將目前的視窗切換到其他的應用軟體視窗中。函數可以將目前的視窗切換到其他的應用軟體視窗中。不過該應用軟體視窗必須事先已經啟動。不過該應用軟體視窗必須事先已經啟動。使用使用Application物件的物件的ActivateMicrosoftAp方方法啟動法啟動Microsoft 自家的應用軟體自家的應用軟體Application物件的物件的ActivateMicrosoftAp方法具有一系列的方法具有一系列的xlMicrosoft內建內建常數，運用此些內建常數可以啟動常數，運用此些內建常數可以啟動Microsoft 自家的應用軟體。自家的應用軟體。使用使用Sendkey函數函數SenKeys函數可以送出一或多個按鍵訊息到其他應用程式視窗中，就如函數可以送出一或多個按鍵訊息到其他應用程式視窗中，就如同在鍵盤上按下的一樣。同在鍵盤上按下的一樣。Automation的連結的連結(Binding)方式方式Binding是什麼是什麼?當當Automation controller向向 Automation serve r取得物件時，取得物件時，VBA必須驗必須驗證該物件是否已經存在，而且對該物件所使用的屬性與方法皆為正證該物件是否已經存在，而且對該物件所使用的屬性與方法皆為正確。此種驗證的程序在確。此種驗證的程序在VBA中稱之為連結中稱之為連結(Binding)事先連結事先連結(Early Binding)及事後連結及事後連結(Late Binding)Early binding occurs at compile time rather than run timeEarly Binding 的優點的優點1.Performance。2.Compile-Ttime Ssyntax Cchecking。3.Code readability。4.Viewing objects。5.Getting help。Automation DEMO使用使用Excel工作表內資料建立工作表內資料建立Word文件文件使用使用Excel工作表內資料建立工作表內資料建立PowerPoint文件文件使用使用Excel工作表內資料建立工作表內資料建立Word文件文件使用使用Excel操控送操控送Outlook郵件郵件使用使用Excel VBA巨集之應用策略巨集之應用策略將將Microsoft Excel視為整個應用系統視為整個應用系統之前端之前端(Front-End)處理工具處理工具將將Microsoft Excel視為整個應用系統視為整個應用系統之終端之終端(Terminal-End)處理工具處理工具使用使用Microsoft Excel處理所有資料處理所有資料利用利用Microsoft Excel來建立作業模式來建立作業模式之之原型原型(Prototype)Q&A台灣微軟公司台灣微軟公司 軟體顧問軟體顧問洪士吉洪士吉 編撰編撰