Excel VBA - The Powerful Automatic Tools
Nowadays, many tools for data analytic and reporting dashboard have been well developed and taken advantage of by the analysts. Frankly speaking, I am HongKonger and have worked in several companies. The most common analytic tools in the Hong Kong enterprises should be Microsoft Excel. For the data transformation and calculation, it consists of built-in formula with aspects of logical, mathematical, statistical and financial function. Conditional Formatting also saves much time to update the formats in the summary report.
I believe many people should know how to use Excel but they seldom hear about Excel VBA. Even heard that, they still think VBA is difficult to learn. In fact, the processing time of repetitive task in Excel still could be eliminated by running macro which consists of visual basic scripts. As you may know, many employees in Hong Kong need to work overtime and under the pressure. Consequently, VBA plays an important role to deal with such heavy workload.
I am going to share own insight towards Excel VBA and frequently-used function. Hope that you could find it useful and understanding more about VBA.
VBE treats the current workbook as the ActiveWorkbook and store the module in here. It is not necessary to indicate ActiveWorkbook in the script. As a result, you could simplified the code as:
In the VBA, objects are defined with corresponding properties.
For instance,
Address, one of properties of the Range object, will return the values: "$A$1".
I believe many people should know how to use Excel but they seldom hear about Excel VBA. Even heard that, they still think VBA is difficult to learn. In fact, the processing time of repetitive task in Excel still could be eliminated by running macro which consists of visual basic scripts. As you may know, many employees in Hong Kong need to work overtime and under the pressure. Consequently, VBA plays an important role to deal with such heavy workload.
I am going to share own insight towards Excel VBA and frequently-used function. Hope that you could find it useful and understanding more about VBA.
Introduction
Macro
A macro is set of script in forms of Sub / Function. It could be recorded of a series of tasks or developed the VB script. Common methods to trigger the macro is to click the button or press the keyboard shortcut, which has assigned the macro.
Sub & Function
It is quite confused for VBA beginner to distinguish the difference between "Sub" and "Function"
Objects
Macro
A macro is set of script in forms of Sub / Function. It could be recorded of a series of tasks or developed the VB script. Common methods to trigger the macro is to click the button or press the keyboard shortcut, which has assigned the macro.
Sub & Function
It is quite confused for VBA beginner to distinguish the difference between "Sub" and "Function"
- "Sub" could be triggered directly after the macro assignment but cannot return anything.
- "Function" outputs the values or objects. However, it is unable to trigger the function by button / shortcut. You can define your function in VBA and apply it in the cells in Excel. (e.g. =MyFunction($A$1, $B$1) )
Objects
Excel Objects have been well defined and stored in the visual basic library once you installed the Microsoft Excel. To put it simply, the hierarchy of basic objects are as follows:
 |
| Hierarchy of Excel Objects |
- Obviously, the object of Application indicates the Microsoft Excel
- One or more than one workbooks (.xlsx, .xlsm, etc) are allowed to exist in one application
- It contains one or more than one worksheets (Default: Sheet1, Sheet2, Sheet3) in one workbook
- Similarly, there are 1048576 x 16384 cells and ranges formed by single/multiple cells in one worksheet
Assume you have opened the workbooks and select the cells of "A1" . The "Workbooks" and "Worksheets" could be described as ActiveWorkbook as you have activated it. When describing the range of "A1" in VBE, the code submitted is that:
ActiveWorkbook.ActiveSheet.Range("A1")
VBE treats the current workbook as the ActiveWorkbook and store the module in here. It is not necessary to indicate ActiveWorkbook in the script. As a result, you could simplified the code as:
ActiveSheet.Range("A1")
 |
| ActiveSheet.Range("A1") / ActiveSheet.Cells(1,"A") |
In the VBA, objects are defined with corresponding properties.
For instance,
Range("A1").Address
Built-in Sub/Function are members of Objects. Generally, it triggers the events of objects.
For example, series of events are triggered as the range of "A1" are activated and then selected.
Variables
Microsoft Excel 2010
For the 2010 version, please click the "Excel Option" and select "Customize Ribbon" to activate the checkbox of Developer Tab. (Sorry for that I am using Chinese Language in the version 2010). Then, you can the find the tab of "Developer" now!
Macro Security
Update the setting in Macro Security before open Visual Basic Editor.
I bet you cannot wait to open the VBE at this moment. A window pops up after you click the "Visual Basic". Right! This is the VBA Projects and you can edit own code to run the tasks automatically soon.
You might raise a question why there is some difference between the projects you open and mine. Don't worry! I just customized the color of projects interface in options before.
Immediate Window
It allows to execute the Sub/Function or immediate calculation to return the value. This is useful for querying the current value of variables especially in debug mode.
Modules
Insert a new module by right click the mouse.
In the VBE (Visual Basic Editor), you could run your own scripts in here now!
Here is the very simple example:
2. Run the program by click "Run" button / F5
3. A message box pops up successfully
Is it simple and easy? Enjoy!
Objects & Properties Lists
Some common object lists are summarized by the objects for quick search and reference.
Worksheet
Range("A1").Activate
Range("A1").Select
Variables
Variables is also used for beginner to replace some hard-code by the variables after recording a macro.They could help you to develop more flexible and automatic macros as well as maintenance of coding.
Additionally, it is confused for beginning programmers to distinguish among the numbers, string, variables and objects and you might ask how to distinguish them. A variable can be declared as the type which is a integer, string, object (Workbook, Sheet..) or variant (vary with code). The lists of type are shown below:
| Type | Sign | Type | Sign |
| Integer | % | String | $ |
| Long | & | Date | |
| Single | ! | Array | |
| Double | # | Objects | |
| Currency | @ | Variant |
Launching Visual Basic
Microsoft Excel 2007
Before starting the VB, you can follow below procedures to show the Developer Tab in the Microsoft Excel first.
Microsoft Excel 2007
Before starting the VB, you can follow below procedures to show the Developer Tab in the Microsoft Excel first.
 |
| Click Excel Options |
 |
| Tick the "Show Developer Tab in the Ribbon |
Microsoft Excel 2010
For the 2010 version, please click the "Excel Option" and select "Customize Ribbon" to activate the checkbox of Developer Tab. (Sorry for that I am using Chinese Language in the version 2010). Then, you can the find the tab of "Developer" now!
Macro Security
Update the setting in Macro Security before open Visual Basic Editor.
 |
| Click the "Macro Security" |
 |
| Select the fourth option in "Macro Setting" |
I bet you cannot wait to open the VBE at this moment. A window pops up after you click the "Visual Basic". Right! This is the VBA Projects and you can edit own code to run the tasks automatically soon.
 |
| Click "Visual Basic" |
 |
| VB Editor |
Immediate Window
It allows to execute the Sub/Function or immediate calculation to return the value. This is useful for querying the current value of variables especially in debug mode.
 |
| Input "?1+2" and Press enter to get the result |
Modules
Insert a new module by right click the mouse.
In the VBE (Visual Basic Editor), you could run your own scripts in here now!
Here is the very simple example:
Sub sayHelloToMyself() myName = "______" 'Define your name first MsgBox "Hello! " & myName & "!" 'Pop up a message box End Sub1. Input your name in ______ first
myName = "Horace"
Is it simple and easy? Enjoy!
Objects & Properties Lists
Some common object lists are summarized by the objects for quick search and reference.
Application
Workbooks
| Coding | Value | Results |
| 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
| Coding | Results |
| 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
| Coding | Results |
| 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
| Coding | Results |
| 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
| Coding | Results |
| 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 |
Operators & Keywords
In VBE, it is quite convenient that the operators and keywords will be auto-corrected when type them in wrong case.
If else statement
In VBE, it is quite convenient that the operators and keywords will be auto-corrected when type them in wrong case.
| Operator | Description | Operator | Description |
| + | Sum | <= | Smaller or equal to |
| - | Subtration | >= | 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 statement
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