Excel VBA - 強大的自動化工具
近幾年，或許也是大數據被吹捧之下，愈來愈多數據分析和報告工具流出市面，分析師現在能更方便地利用這些工具。其實筆者是個香港人，在港工作數年，發現大部分香港企業主要用Microsoft Excel來做分析的。這也算不上奇怪的，香港的企業文化偏向保守及避免錯誤，所以趨向選用比較舊式和穏定的software。而Excel本身有強大的功能，無論在數據轉換，報表和統計分析，條件式格式和設計，再加上內置和自定義的函數，用家可以製造出不同的Application（應用）。
我相信大部分的朋友也對Excel不是很陌生，可是一說到VBA，大家的反應不是覺得很難，就是沒有聽過這東西。 其實在Excel寫VBA真的不是太困難，能把一些重覆性的動作，自動的執行，筆者當時在工作用上了VBA，基本上兩個小時的工作，大概10分鐘就完成了，效率令我嘆為觀止，從此我就愛上了它了。但VBA是不是值得學的，筆者認為就數據分析而言，VBA不久將來會被遺忘，因為現在和未來的統計軟件會比Excel的更多功能和更快的運算速度。加上R和Python，Excel也不再是他們的首選，更為Excel VBA響起警號了。但以定期報告來說，有一些香港的打工仔，需要使用Excel來做每月或每年的報告，在巨大的工作壓力和超時工作的情況下，VBA實在是舒緩工作量的良方。
在這裡我會介紹Excel VBA的功能和常用的函數，希望對初學習VBA的朋友有幫助！
簡介
因為我們在當前的活頁簿裡寫VB script，所以就算你不寫下ActiveWorkbook, VB編輯器會把工作表視為在當前的活頁簿裡面，也因此我們也能夠簡化這段coding：
在VBA的世界裡，物件也有它自己的特性，例如：
Address, 是Range其中一個的特性，你會獲得A1的位置值： "$A$1".
Microsoft Excel 2010
至於2010的版本要在Excel選項，選擇"自訂功能區"和在開發人員的Checkbox打勾，再確定就可以了，現在你可以看到Developer的Tab顯示在頭頂上！
巨集安全性 (Macro Security)
先更改巨集安全性的設定，才開啟Visual Basic Editor。
終於可以開啟VBE了！按下Visual Basic會有一個視窗彈出來。我們可以寫下自己的script，讓工作自動化了！
你可能會問會為甚麼我的editor是特別的黑暗，不用擔心，那個只是我自定義我的介面，Style會比較偏向Programmer Style而已～
即時運算視窗
它是用作既時測試並獲得結果，可以測試Sub或Function或者做一些即時的計算，這是一個很好的功能，去尋找一些自己不肯定的運算的答案，尤其是在Debug Mode（除錯模式），可以即是獲得物件特性的值，來知道自己的program那裡出了錯誤。
模組 (Module)
先按下右鍵，插入一個新的Module，如圖：
在VBE(Visual Basic Editor)裡，你可以自由執行自己的腳本，一起寫一個簡單的應用吧！
2. 按下"執行"或者"F5"執行程式
3. 一個視窗會彈出來說： Hello！
容易嗎？當然還有更多更複雜的應用可以開發的，現在還只是個熱身。
物件及其特性 (Object & Its Properties)
一些常用的物件和它的特性，我已把它列成一個表格以備忘，方便自己或者大家查閱！
工作表 (Worksheet)
我相信大部分的朋友也對Excel不是很陌生，可是一說到VBA，大家的反應不是覺得很難，就是沒有聽過這東西。 其實在Excel寫VBA真的不是太困難，能把一些重覆性的動作，自動的執行，筆者當時在工作用上了VBA，基本上兩個小時的工作，大概10分鐘就完成了，效率令我嘆為觀止，從此我就愛上了它了。但VBA是不是值得學的，筆者認為就數據分析而言，VBA不久將來會被遺忘，因為現在和未來的統計軟件會比Excel的更多功能和更快的運算速度。加上R和Python，Excel也不再是他們的首選，更為Excel VBA響起警號了。但以定期報告來說，有一些香港的打工仔，需要使用Excel來做每月或每年的報告，在巨大的工作壓力和超時工作的情況下，VBA實在是舒緩工作量的良方。
在這裡我會介紹Excel VBA的功能和常用的函數，希望對初學習VBA的朋友有幫助！
巨集 (Macro)
你可能曾經聽過巨集，其實是一個集合了數個用VB script寫的Sub和Function。一般，我們能透過點擊按扭或是快捷鍵來啟動巨集。
Sub和Function
筆者還是VBA菜鳥也是搞不清楚，Sub跟Function的分別。基本上，它們只是本質不同：
你可能曾經聽過巨集，其實是一個集合了數個用VB script寫的Sub和Function。一般，我們能透過點擊按扭或是快捷鍵來啟動巨集。
Sub和Function
筆者還是VBA菜鳥也是搞不清楚，Sub跟Function的分別。基本上，它們只是本質不同：
- Sub 可以透過指定巨集，來啟動巨集，但它不能夠輸出任何值或物件
- Function 是能夠輸出任何值或物件，但不能由按鍵來激活。此外，在VBA定義的Function能夠應用在儲存格裡面（例如： MyFunction($A$1, $B$1)）
Excel裡的物件早已被定義在Visual Basic Library。為了更清楚顯示Excel的常用物件，我把它們列在層次結構裡：
現在，打開活頁簿並選擇A1儲存格。那麼你正在打開的活頁簿就是叫作ActiveWorkbook，當前的工作表叫作ActiveSheet，你選擇的A1儲存格正在ActiveWorkbook和ActiveSheet的裡面，如果我們要指明這個儲存格，可以在VB編輯器裡輸入：
 |
| Excel的常用物件 |
現在，打開活頁簿並選擇A1儲存格。那麼你正在打開的活頁簿就是叫作ActiveWorkbook，當前的工作表叫作ActiveSheet，你選擇的A1儲存格正在ActiveWorkbook和ActiveSheet的裡面，如果我們要指明這個儲存格，可以在VB編輯器裡輸入：
ActiveWorkbook.ActiveSheet.Range("A1")
因為我們在當前的活頁簿裡寫VB script，所以就算你不寫下ActiveWorkbook, VB編輯器會把工作表視為在當前的活頁簿裡面，也因此我們也能夠簡化這段coding：
ActiveSheet.Range("A1")
 |
| ActiveSheet.Range("A1") / ActiveSheet.Cells(1,"A") |
在VBA的世界裡，物件也有它自己的特性，例如：
Range("A1").Address
此外，物件也有Event的特性，可以被打開，激活或者選擇，例如：
Range("A1").Activate
Range("A1").Select
以上的script會讓儲存格A1被激活之後再被選擇
變數 (Variable)
變數 (Variable)
變數的用處簡單的說就是取代一些硬編碼，令整個巨集能適應在不同的情況，提高程式的靈活性，維護的時候變得更方便了，有什麼要修改的話，只要更改一些參數就可以了，不用重新寫過或者大改。
另外，對於程式的初心者， 最容易把常數(Constant) 跟變數(Variable)搞亂了，不過，時間久了，我相信你會掌握到的！變數的種類其實有很多的，包括整數，字串，貨幣，陣列類，等等，其中最常用已列下表：
另外，對於程式的初心者， 最容易把常數(Constant) 跟變數(Variable)搞亂了，不過，時間久了，我相信你會掌握到的！變數的種類其實有很多的，包括整數，字串，貨幣，陣列類，等等，其中最常用已列下表：
| Type | Sign | Type | Sign |
| Integer | % | String | $ |
| Long | & | Date | |
| Single | ! | Array | |
| Double | # | Objects | |
| Currency | @ | Variant |
啟動 Visual Basic
Microsoft Excel 2007
首先要先把Developer Tab顯示出來，才能啟動VB，你可以依照和參考下面的步驟：
Microsoft Excel 2007
首先要先把Developer Tab顯示出來，才能啟動VB，你可以依照和參考下面的步驟：
 |
| Click Excel Options |
 |
| Tick the "Show Developer Tab in the Ribbon |
Microsoft Excel 2010
至於2010的版本要在Excel選項，選擇"自訂功能區"和在開發人員的Checkbox打勾，再確定就可以了，現在你可以看到Developer的Tab顯示在頭頂上！
巨集安全性 (Macro Security)
先更改巨集安全性的設定，才開啟Visual Basic Editor。
 |
| Click the "Macro Security" |
 |
| Select the fourth option in "Macro Setting" |
終於可以開啟VBE了！按下Visual Basic會有一個視窗彈出來。我們可以寫下自己的script，讓工作自動化了！
 |
| Click "Visual Basic" |
 |
| VB Editor |
即時運算視窗
它是用作既時測試並獲得結果，可以測試Sub或Function或者做一些即時的計算，這是一個很好的功能，去尋找一些自己不肯定的運算的答案，尤其是在Debug Mode（除錯模式），可以即是獲得物件特性的值，來知道自己的program那裡出了錯誤。
 |
| Input "?1+2" and Press enter to get the result |
模組 (Module)
先按下右鍵，插入一個新的Module，如圖：
在VBE(Visual Basic Editor)裡，你可以自由執行自己的腳本，一起寫一個簡單的應用吧！
Sub sayHelloToMyself() myName = "______" 'Define your name first MsgBox "Hello! " & myName & "!" 'Pop up a message box End Sub1. 先輸入你的名字，把它放進一個變數上, myName
myName = "Horace"
容易嗎？當然還有更多更複雜的應用可以開發的，現在還只是個熱身。
物件及其特性 (Object & Its Properties)
一些常用的物件和它的特性，我已把它列成一個表格以備忘，方便自己或者大家查閱！
應用 (Application)
活頁簿 (Workbooks)
| 編碼 | 值 | 結果 |
| Application.ScreenUpdating | True (False) | Show (Hide) results for each operation |
| Application.DisplayAlert | True (False) | Show (Hide) the Alert |
| Application.EnableEvents | True (False) | Enable (Disable) the event |
| Application.WorksheetFunction | Use excel function (e.g. countif, sum..) | |
| Application.Caption | Update the Caption name | |
| Application.DisplayFormulaBar | True (False) | Show (Hide) the Formula Bar |
| Application.DisplayStatusBar | True (False) | Show (Hide) the Status Bar |
| Application.StatusBar | "Updating" | Replace the status content |
| Application.OnTime | Now() | Execute the Sub now |
| Application.GetOpenFilename | Get the file fullname | |
| ActiveWindow.DisplayHeadings | True (False) | Show (Hide) the headings |
| ActiveWindow.DisplayWorkbookTabs | True (False) | Show (Hide) the label of workbook |
| ActiveWindow.DisplayHorizontalScrollBar | True (False) | Show (Hide) the horizontal scroll bar |
| ActiveWindow.DisplayVerticalScrollBar | True (False) | Show (Hide) the vertical scroll bar |
| ActiveWindow.CommandBars("Drawing").Visible | True (False) | Show (Hide) the comment bar(Drawing) |
| ActiveWindow.CommandBars("Standard").Visible | True (False) | Show (Hide) the comment bar(Standard) |
| ActiveWindow.DisplayGridlines | True (False) | Show (Hide) the gridlines (格線) |
| ActiveWindow.WindowState | xlMaximized | Maximize the window |
活頁簿 (Workbooks)
| 編碼 | 結果 |
| ActiveWorkbook | The workbook activated |
| ThisWorkbook | the current workbook |
| Workbooks(3) | The third workbook |
| Workbooks("Book 2") | The workbook named "Book 2" |
| Workbooks.Name | The name of the workbook |
| Workbooks.Path | The path (C:\...) |
| Workbooks.FullName | The path (C:\...\Book1.xlsx) |
| Workbooks.Add "C:\.." | Add workbook in path |
| Workbooks.Activate | |
| Workbooks.Save | Save the file |
| Workbooks.SaveAs Filename:="C:\Book2.xlsx" | Save the file as .. |
| Workbooks.Close | Close all books and save (do not save) the file |
工作表 (Worksheet)
| 編碼 | 結果 |
| Sheets | The sheets with more values |
| ActiveSheet | The sheets activated |
| Worksheets.Add before (after) :=Worksheets(2) | Add worksheets before (after) the second worksheets |
| Worksheets.Add Count:=3 | Add 3 worksheets |
| Worksheets.Add (after:=Worksheets.Count).Name="Form" | Add the worksheets named Form |
| Worksheets.Delete | Delete the worksheets |
| Worksheets("").Copy before (after) :=Worksheets(1) | Copy the specific worksheets before (after) the first worksheets |
| Worksheets.Count | Number of all worksheets |
| Worksheets.Visible | True, False, xlSheetVisible, -1, 0, 1, 2 |
範圍 (Range)
| 編碼 | 結果 |
| Range("A1:A10,A4:B3,B2:C3") | The range union (OR) |
| Range("A1:A10 A2:C3") | The range common (AND) |
| Range("A1:A10","A4:B3") | The min matrix from the range |
| Range("").Select | Select the range |
| Range("").Activate | The range activated |
| Range("").Interior.Color | RGB(255,255,0) |
| Range("").Interior.ColorIndex | 5 |
| Range(" : ").Cells( , ) | The cell within the range: |
| Union(Range("A1:A10"),Range("D1:D5") | Union the range as one range |
| Range("").offset(2,3) | Move 2 cells downward and 3 cells right |
| Range("").Resize(4,5) | Expend to 4 x 5 Matrix |
| UsedRange | The matrix formed by usedrange |
| Range("").CurrentRegion | The matrix formed by current region |
| Range("").End(xlUp).Offset(1,0) | xlup means moving upward direction |
| Range(" : ").Copy Destination:=Range(" ") | Copy the range 1 in range 2 |
| Range(" : ").Cut | Cut the specified range |
儲存格 (Cells)
| 編碼 | 結果 |
| ActiveCell | The cell activated |
| Cells.ClearContent | Clear the value in cell |
| Cells.(3,4) | Row = 3, Column = "D" |
| Cells.(3,"D") | Row = 3, Column = "D" |
| Cells.Value | The value of cells |
運算子 & 關鍵詞
在VBE裡，運算子的大小階會被自動改正，不用理會大小階實在很方便！
If else 語句
在VBE裡，運算子的大小階會被自動改正，不用理會大小階實在很方便！
| 運算子 | 描述 | 運算子 | 描述 |
| + | Sum | <= | Smaller or equal to |
| - | Subtraction | >= | Bigger or equal to |
| * | Product | <> | Not Equal to |
| / | Divide | Is | Object Equal to |
| \ | Divisible | Like | String Equal to |
| ^ | Index | And | 1 x False -> False |
| Mod | Remainder | Or | 2 x True -> True |
| = | Equal | Not | True -> False |
| < | Smaller | Xor | True,False -> True |
| > | Bigger | Eqv | 1+2 Eqv 4-1 -> True |
If else 語句
If <condition1> Then <result1> ElseIf <condition2> Then <result2> Else <result3> End ifCase
Select Case <variable> Case <string1> <result1> Case <string2> <result2> End SelectDo-Loop
Do <condition> <content> LoopFor-Loop
For i = 1 to 100 Step n <content> NextWith
With <object> .<properties1> = <value1> .<properties2> = <value2> End with
Comments
Post a Comment