excelvba宏的认识和应用.ppt

新手入門不用怕 高手應用更拉風ExcelVBA巨集的認識及應用 用錄的也會通 從巨集錄寫出發 巨集的錄寫程序 巨集的儲存位置 Excel的巨集錄寫功能 能將使用者對Excel功能表中所選定的命令及相關的滑鼠動作自動轉換成VisualBasic程式語句 從而製作成巨集程式來供使用者執行 因此 使用者以指定結果的方式來操作Excel 即可不費吹灰之力完成VBA巨集的撰寫作業 編修巨集 相對錄寫與絕對錄寫 透視VBA語言 VisualBasic編輯環境 程式的窩 VisualBasic編輯環境 VBA的敲門磚 物件 Object 屬性 Property 方法 Method 和事件 Event 物件的意義與層次結構屬性的意義與例舉方法的意義與例舉事件的意義與例舉 Run Run Run 執行巨集 使用 工具 巨集 命令使用 便捷鍵 將巨集指定至工具列上的按鈕將巨集指定至工作表或圖表上的物件將巨集指定至 表單 上的物件將巨集指定至功能表將巨集指定至快顯功能表將巨集指定至Office小幫手將巨集指定至事件中 當事件發生時自動執行該巨集 在VisualBasic編輯環境中撰寫程式 在程式碼視窗內內使用英文小寫來輸入關鍵字 輸入sub及巨集名稱後 當巨集程序不處理引數時 勿需再輸入 文字 而直接按下Enter鍵即可 使用Tab按鍵來縮排巨集程序內的各行程式碼 每行陳述式中若需輸入運算子 operator 時 其前後勿需輸入 空白 編修巨集程序時 可使用拖曳方式或快顯功能表來複製或搬移文字 利用 自動完成 功能來輔助文字之輸入並使用 自動列示方法及屬性 及 列出常數 的功能來挑選相互配合的方法及屬性 VBA巨集程式的結構 頂端宣告區設定VBAOption模組應用範圍宣告變數宣告常數程式區程式碼 專案視窗內各物件的程式碼視窗所登錄各區段的VBA巨集程式碼有其結構性 想要瞭解及撰寫VBA巨集 必須對VBA巨集程式的結構有所認識 VBA的陳述式 Statement 宣告陳述式的用途在於對程序 變數 陣列以及常數等進行命名以及定義 宣告陳述式 指定陳述式用於指定數值或運算式給變數或是常數 指定陳述式通常會包含一個等號 設定屬性值的陳述式也是一個指定陳述式 指定陳述式 可執行的陳述式會執行相關的動作 其可以執行函數 方法 迴圈或從某一區塊的程式碼中做分支執行 可執行的陳述式 常用的物件 屬性 方法與事件 Excel應用軟體被切割成上百個物件 且各物件下轄了眾多的屬性與方法 雖然絕大數的物件 屬性與方法都可以使用錄製方式來產生程式碼 然而 還是有一些物件及其相關的屬性與方法無法以錄製方式完成 況且在閱讀或編輯巨集程式碼時 必須對各行陳述式有所暸解 方能進行相關的編修 Application群組物件及其相關方法Workbooks群組物件及其相關方法Windows群組物件及其相關方法Sheets群組物件及其相關方法Range群組物件及其相關方法 Workbook BeforeClose事件Workbook Open事件Worksheet Activate事件Worksheet Change事件Worksheet SelectionChange事件Chart SeriesChange事件 VisualBasic編輯環境中最佳幫手 物件瀏覽視窗 ExcelVBA具有數百個物件 而各物件又下轄眾多的屬性 方法及事件 要想強記所有的屬性 方法及事件不但是不可能且不切實際 唯有熟悉少數常用的屬性 方法及事件外 對於不熟悉的物件 屬性 方法及事件 可運用VisualBasic編輯環境下的物件瀏覽視窗 來作為諮詢參考的工具 ExcelVBA巨集程式例舉一各縣市銷售績效報告 設計主控畫面 使用 主控畫面 工作表 並佈置若干物件作為介面控制工具 Range 選定地區 Formula Application Caller 佈置工作表及圖表 編撰巨集程式 依據 繪圖資料 工作表內各組數據 進而繪製相關圖表 按地域性將業績資料記錄建立於工作表中 並對此業績資料庫進行彙總報告及圖形分析 在實務上係十分普遍與實用的作業 本例舉旨在使用臺灣地圖作為操作介面 就業績資料進行資料匯集 彙總報表及圖形分析 ExcelVBA巨集程式例舉二各縣市銷售績效報告 設計主控畫面 建立彙總報表及圖表 編撰巨集程式 ActiveSheet PivotTableWizardSourceType xlDatabase SourceData 業績記錄資料庫 Database TableDestination TableName 樞紐分析表1 ActiveSheet PivotTables 樞紐分析表1 AddFieldsRowFields 品名 ColumnFields 日期 PageFields 倉儲單位 WithActiveSheet PivotTables 樞紐分析表1 PivotFields 金額 Orientation xlDataField NumberFormat 0 EndWith 操控樞紐分析表及圖報表 PivotTable物件 範例展示 全球銷售類別分析 1 佈置資料清單 2 建置樞紐分析表及物件連結作業 3 使用VBA操控樞紐分析表及圖報表作業 ActiveX控制項物件與自訂表單 認識 工具箱 工具列 使用 工具箱 編製自訂表單 在工作表中使用 控制工具箱 工具列 操控指令列 CommandBar CommandBar物件模型 範例展示 操控Office小幫手 Assistant與Balloon物件 範例展示 建立Excel增益集 製作自訂式的Add In 內建式的Add In 以VBA程式碼控制安裝及使用Add In 保護原始程式碼 Addins Sample xla Install True Addins add C Addin Sample xla 使用增益功能的優點 提昇執行效率保護原始程式碼幕後作業 不干擾使用者強迫使用者執行WorkBook Open或AutoOpen巨集程序更簡易地存取函數更易於在ExcelAdd In對話方塊中取用更容易被Excel自動載入關閉離開無雜訊 在Excel內自動啟動其他應用軟體 使用Shell函數啟動其他應用軟體藉由VBA的Shell函數 可以開啟其他應用軟體並將該應用軟體啟動成活動中的應用軟體 使用AppActive函數啟動其他應用軟體使用AppActivate函數可以將目前的視窗切換到其他的應用軟體視窗中 不過該應用軟體視窗必須事先已經啟動 使用Application物件的ActivateMicrosoftAp方法啟動Microsoft自家的應用軟體Application物件的ActivateMicrosoftAp方法具有一系列的xlMicrosoft內建常數 運用此些內建常數可以啟動Microsoft自家的應用軟體 使用Sendkey函數SenKeys函數可以送出一或多個按鍵訊息到其他應用程式視窗中 就如同在鍵盤上按下的一樣 Automation的連結 Binding 方式 Binding是什麼 當Automationcontroller向Automationserver取得物件時 VBA必須驗證該物件是否已經存在 而且對該物件所使用的屬性與方法皆為正確 此種驗證的程序在VBA中稱之為連結 Binding 事先連結 EarlyBinding 及事後連結 LateBinding Earlybindingoccursatcompiletimeratherthanruntime EarlyBinding的優點1 Performance 2 Compile TtimeSsyntaxCchecking 3 Codereadability 4 Viewingobjects 5 Gettinghelp AutomationDEMO 使用Excel工作表內資料建立Word文件 使用Excel工作表內資料建立PowerPoint文件 使用Excel工作表內資料建立Word文件 使用Excel操控送