IT必備良藥，Excel 2007 VBA 大解密

Size: px

Start display at page:

Download "IT必備良藥，Excel 2007 VBA 大解密"

辈岑
6 years ago
Views:

2 IT 必備良藥 Excel 2007 VBA 大解密盧昊產品行銷經理台灣微軟

3 賓果遊戲請每一位學員檢查是否有一張賓果卡每一張賓果卡上有 5x5 的關鍵字在簡報過程中會出現關鍵字, 請在您的賓果卡上將關鍵字圈起來若有直橫斜的五格數字都畫圈了, 就可以連成一條線連成二條線時請喊 VBA 賓果, 並將賓果卡交由工作人員檢查前 10 名 VBA 賓果者可獲小禮物當連成四條線時, 請大聲喊 Office 2007 賓果可獲神秘大獎

4 課程大綱了解 Excel 巨集以及 Visual Basic for Applications (VBA) 是甚麼, 能為你做哪些事了解如何在 Office 2007 中執行巨集從錄製巨集到撰寫程式. 熟悉開發環境熟悉 Visual Basics 編輯器認識 PowerTools for Open XML

5 本課程的目的不是要教你成為 Excel VBA 專家是讓你了解 Excel VBA 帶來的便利性了解 Office 2007 對巨集的支援性了解在伺服器上產生 Office 文件的新方法

6 為什麼要談 VBA VBA 可以幫忙解決 IT 人員在日常生活中常見的問題, 達到三省運動省錢省事省麻煩 VBA 是辦公室自動化最簡單最方便的方式廣大的學習資源, 眾多的範例程式使用者容易使用, 不需教育訓練

7 VBA 百萬小學堂何謂巨集 (Macro, 宏 )? 巨集是只要按一下即可套用的指令的集合它們可以自動化您可以在程式中執行的幾乎所有工作, 甚至可讓您執行您想像不到的工作巨集是一種程式設計嗎? 雖然巨集是程式設計, 但是您不需要成為開發人員或具有程式設計的知識就可以使用它們大部份可以在 Office 程式中建立的巨集是利用稱為 Microsoft Visual Basic for Applications (VBA) 的語言所撰寫何時以及為什麼要使用巨集? 巨集可以節省時間, 並擴充您每天使用的程式功能它們可以用於自動化重複的文件製作工作簡化繁瑣的工作或建立解決方案用聰明的方法, 做對的事

8 Office 2007 對於巨集處理不同之處管理巨集的安全性雖然大部份的巨集是無害且有助益的, 巨集仍然是一個重要的安全性問題惡意建立的巨集中會包含具破壞性的程式碼, 並對文件或系統造成傷害使用者介面改變了命令列 (Command Bar) 不見了所有舊的功能表列以及工具列會放到增益集中

9 管理巨集的安全性 Office 2007 預設的安裝方式會停用巨集使用者可點選安全性警告之選項, 勾選啟用這個內容

10 管理巨集的安全性 Office 2007 預設的安裝方式會停用巨集使用者可點選安全性警告之選項, 勾選啟用這個內容改變安全性設定進階人員可以在信任中心的巨集設定項次中選擇改變巨集安全性設定或可以在信任位置中加入可以信任之檔案路徑安裝時就設定好改變 setup.exe /config

11 Demo 錄製 EXCEL 巨集程式沒有比這更簡單的方式了!

12 用錄的也會通 --- 從錄製巨集 ( 宏 ) 出發 Excel 的錄製巨集功能, 能將使用者對 Excel 功能表中所選定的命令及相關的滑鼠動作自動轉換成 Visual Basic, 從而製作成巨集程式來供使用者執行使用者以指定結果的方式來操作 Excel, 即可不費吹灰之力完成 VBA 巨集的撰寫作業

13 錄製巨集開始錄製巨集把需要經常重複執行的工作錄製成為巨集, 讓步驟執行自動化操作步驟停止錄製執行巨集的方法使用快捷鍵點選巨集按鈕執行插入控制項執行巨集

14 Visual Basic for Application 既然能錄製巨集, 為什麼還要用 VBA? 記錄了不必要的步驟記錄了不需要的資料無法使用較深入的功能 ( 如對話框內容輸入 ) 無法產生循環運作無法進行條件判斷千變萬化.. 無限想像巨集 VBA

15 Demo 資料合併

16 常見的問題, 如何快速將資料合併整併報表 ( 如 Log Data) 一次把資料全部放在同一個頁面以利後續分析新增合併結果工作表 Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = 合併結果 " 從第二欄開始 StartRow = 2 找到所有的工作表然後複製到合併結果工作表 For Each sh In ActiveWorkbook.Worksheets 找到每個工作表的最後一行, 使用 LastRow() 函式 Last = LastRow(DestSh) shlast = LastRow(sh) 如果工作表不是空的, 開始貼上資料 If shlast > 0 And shlast >= StartRow Then End If Next Set CopyRng = sh.range(sh.rows(startrow), sh.rows(shlast)) CopyRng.Copy With DestSh.Cells(Last + 1, "A").PasteSpecial xlpastevalues.pastespecial xlpasteformats Application.CutCopyMode = False End With 函式 (Function) vs. 副程式 (Subroutine) Range vs. Cell 使用 ALT-F11 切換在 VBE 與 Excel 之間遇到不清楚的物件的就用 F1 找答案 MSDN 上有詳細說明

17 Excel 2007 的重大功能改進較大容量的試算表可供探索大量的資料 Office Excel 2007 的試算表提供了高達 16,384 欄位 104 萬列數的超大工作表 ( 原本為 256 X 65535) 使用豐富的資料視覺化配置例如 : 漸層臨界值及圖示等, 即可輕易地套用條件化格式, 以尋找資料中的例外狀況及特別重要的趨勢使用易於建立及探索的 PivotTable 及 PivotChart 檢視來尋找總結及重新導向所需的答案使用 Office Excel 2007 中的彈性及嶄新的 cube 功能從 On-Line Analytical Processing (OLAP) 資料庫中建立自訂報表

18 Demo EXCEL 賓果卡沒有比這更簡單的方式了!

19 怎麼用 Excel VBA 製作賓果卡遊戲 :Buzzword Bingo ( 關鍵字賓果 ) 重要元素決定關鍵字清單決定列印賓果卡片數賓果卡產生迴圈繪製賓果卡框線依據亂數產生關鍵字陣列將關鍵字陣列填入賓果卡

20 Demo EXCEL 賓果卡沒有比這更簡單的方式了!

21 發揮你的創意用 Excel 建立 RSS Feed 用 Excel 查詢股價用 Excel 追蹤拍賣價格

22 Demo Open XML & PowerShell 輕鬆自如地在伺服器上自動產生文件!

23 Open XML Documents & PowerShell 微軟開放了 Open XML 檔案格式, 讓文件的解讀更輕鬆除了 VBA,PowerShell 也是可以處理文件一個非常簡單而有效的方式 PowerTools for Open XML 在伺服器上產生 Office 文件工具中包含了能夠存取編修 Office 文件的 PowerShell cmdlets, 以及一些應用範例

PowerTools for Open XML 安裝 You need to have any edition (including Visual C# 2008 Express Edition) of Visual Studio 2008 installed. You can download Express Edition at http://www.microsoft.

24 PowerTools for Open XML 安裝 You need to have any edition (including Visual C# 2008 Express Edition) of Visual Studio 2008 installed. You can download Express Edition at You need to have PowerShell 1.0 installed. You can download PowerShell at /powershell/download.mspx. PowerTools for Open XML is built using the Open XML SDK. You can download the SDK at You also need the PowerTools source code, which is available in the Releases tab above. Assembly registration execute InstallUtil, which is located at %WINDIR%\Microsoft.NET\Framework\v \InstallUtil.exe%WIND IR%\Microsoft.NET\Framework\v \InstallUtil OpenXml.PowerTools.dll

25 結語 Excel VBA 不需要是專家也可以用 Excel VBA 帶來高度的便利性 Office 2007 持續對巨集的支援用 PowerShell 可以在伺服器上產生 Office OpenXML 檔案格式的文件賓果很好玩.

26 Thank You

Microsoft Word - 学字〔2015〕16号

Microsoft Word - 学字〔2015〕16号山东省教育厅鲁教学字 2015 16 号山东省教育厅关于做好 2016 届师范类高校毕业生生源信息和特困家庭毕业生信息审核工作的通知有关高等学校 : 为全面准确掌握全省 2016 届师范类高校毕业生生源信息和特困家庭毕业生情况, 进