COMP2022 Programming for FinTech Applications
Spring 2020
Professor: Dr. Grace Wang
Week II: EXCEL & VBA
1
1
Lecture Arrangement
qNormal schedule from week 2
§ 1:30-3:20 professor’s lecture session § 3:30-4:20 TA’s lab session
qI may adjust it based on my discretion
qToday:
§ I will cover the 3 hours and you need to do home exercise after class
2
2
1
How to learn a programming language and
use it to solve problems?
qHave a big picture about the structure of the language § Structure
§ Basic grammar and component
§ API (functions/formulas): know where to search which category of the
functions
qGiven a problem, know how to find the solution § Read documents
§ Google, online forum, ……
qNote: you cannot learn every detail, but you need to know to how to solve a problem
qAn appetizer example
3
3
Use Excel Efficiently
qAutofill
qSelect efficiently qAddressing
4
4
2
5
6
3
7
8
4
9
10
5
11
12
6
13
14
7
Select efficiently
15
16
16
8
17
17
18
18
9
19
19
20
20
10
Relative and Absolution Addressing
21
22
22
11
+
23
23
24
24
12
25
25
26
26
13
27
27
28
28
14
29
29
30
30
15
+
+
31
31
32
32
16
33
33
34
34
17
35
35
36
36
18
37
37
Case study
qMortgage calculation sheet
38
38
19
VBA
qIntroduction of Visual Basic for Applications qVBA Programming Fundamentals
qVBA Sub Procedures
qVBA Function Procedures
qVBA Programming Examples
39
39
Introduction
qVBA (Visual Basic for Applications):
§ The programming language built into Excel. § It supports object modes.
qBASIC :
§ Beginner’s All-purpose Symbolic Instruction Code
§ Has evolved and improved
üIn early days, BASIC was an interpreted language. üNowadays, BASIC allows the code to be compiled.
40
40
20
Basics of VBA1
qCode:
§You can perform actions in VBA by executing VBA code.
§You write (or record) VBA code, which is stored in a VBA module.
41
41
Basics of VBA2
qModule:
§VBA modules are stored in an Excel workbook file, but you view or edit
a module by using the Visual Basic Editor (VBE). §A VBA module consists of procedures.
42
42
21
Basics of VBA3
qProcedures:
§A procedure is basically a unit of computer code that performs some
action.
§VBA supports two types of procedures: Sub procedures and Function procedures.
üSub: A Sub procedure consists of a series of statements and can be executed in a number of ways.
üFunction: A Function procedure returns a single value (or possibly an array).
43
43
Basics of VBA 5
q Objects:
§ The term Excel Objects (collectively referred to as the Excel Object Model) refers to the
entities that make up an Excel workbook, such as Worksheets, Rows, Columns, Cell Ranges, and the Excel Workbook itself.
q Object model: the arrangement of these objects
§ Object classes are arranged in a hierarchy.
§ Objects also can act as containers for other objects.
For example, Excel is an object called Application, and it contains other objects, such as Workbook objects. The Workbook object contains other objects, such as Worksheet objects and Chart objects. A Worksheet object contains objects such as Range objects, PivotTable objects, and so on.
44
44
22
Basics of VBA 4
qCollections:
§Like objects form a collection.
For example, the Worksheets collection consists of all the worksheets in a particular workbook. Collections are objects in themselves.
45
45
Basics of VBA 6
qObject hierarchy:
§ When you refer to a contained or member object, you specify its position in the object hierarchy by using a period (also known as a dot) as a separator between the container and the member.
§ For example, you can refer to a workbook named Book1.xlsx as:
§ You can take it to still another level and refer to a specific cell as follows:
46
46
23
Basics of VBA 7
qActive objects:
§ If you omit a specific reference to an object, Excel uses the active objects.
§ For example: If Book1 is the active workbook, the preceding reference can be simplified as
47
47
Basics of VBA 8
qObjects properties:
§ Objects have properties. A property can be thought of as a setting for an object.
For example, a range object has properties such as Value and Address. A chart object has properties such as HasTitle and Type.
§ You can use VBA to determine object properties and also to change them.
§ Some properties are read-only properties and can’t be changed by using VBA.
§ You refer to properties by combining the object with the property, separated by a period.
For example, you can refer to the value in cell A1 on Sheet1 as:
48
48
24
Basics of VBA 9
qVBA variables:
§ You can assign values to VBA variables. Think of a variable as a name that you
can use to store a particular value.
§ For example: To assign the value in cell A1 on Sheet1 to a variable called Interest, use the following VBA statement:
49
49
Basics of VBA 10
qObject methods:
§ Objects have methods. A method is an action that is performed with the object.
For example, one of the methods for a Range object is ClearContents.
§ This method clears the contents of the range. You specify methods by combining the object with the method, separated by a period.
For example, to clear the contents of cell A1 on the active worksheet, use:
50
50
25
Basics of VBA 11
qEvents:
§ Some objects recognize specific events, and you can write VBA code that is executed when the event occurs.
For example, opening a workbook triggers a Workbook_Open event. Changing a cell in a worksheet triggers a Worksheet_Change event.
51
51
Basics of VBA 12
qStandard programming constructs:
§ VBA also includes many constructs found in modern programming languages, including arrays, loops, and so on.
52
52
26
Visual Basic Editor
qAll your VBA work is done in the Visual Basic Editor (VBE).
qThe VBE is a separate application that works seamlessly with Excel.
qYou can’t run VBE separately; Excel must be running in order for the VBE to run.
53
53
Displaying Excel’s Developer tab
54
54
27
Activating the VBE
55
55
Adding and Removing a VBA module
qAdding a new VBA module:
§To add a new VBA module to a project, select the project’s name in the Project Explorer window and choose Insert➜Module. Or you can just right- click the project’s name and choose Insert➜Module from the shortcut menu.
§ When you record a macro, Excel automatically inserts a VBA module to hold the recorded code.
qRemoving a VBA module:
§ If you need to remove a VBA module, a class module, or a UserForm from a project, select the module’s name in the Project Explorer window and choose File➜Remove xxx (where xxx is the name of the module).
56
56
28
Storing VBA code
qIn general, a code window can hold four types of code:
§ Sub procedures: A procedure is a set of instructions that performs some
action.
§ Function procedures: A function is a set of instructions that returns a single value or an array.
§ Property procedures: These are special procedures used in class modules.
§ Declarations: A declaration is information about a variable that you provide to VBA. For example, you can declare the data type for variables you plan to use.
qA single VBA module can store any number of Sub procedures, Function procedures, and declarations.
57
57
Entering VBA code
qEnter the code manually.
qUse the macro-recorder feature. qCopy and paste.
58
58
29
Enter code manually
qSayHello example:
Sub SayHello()
Msg = “Is your name ” & Application.UserName & “?”
Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then
MsgBox “Oh, never mind.” Else
MsgBox “I must be clairvoyant!” End If
End Sub
§ Click the Run button on the Standard toolbar.
59
59
Using the macro recorder
q One example: how to record a macro that simply changes the page setup to landscape orientation:
1. Start with a blank workbook:
2. Activate a worksheet in the workbook.
3. Choose Developer➜Code➜Record Macro.
4. Click OK to accept the default setting for the macro.
5. Choose Page Layout➜Page Setup➜Orientation➜Landscape.
6. Select Developer➜Code➜Stop Recording
q You will this change:
60
60
30
The Macro Recorder
qThe macro recorder is an extremely useful tool, but remember the following points:
1. The macro recorder is appropriate only for simple macros or for recording a small part of a more complex macro.
2. Not all the actions you make in Excel get recorded.
3. The macro recorder can’t generate code that performs looping, assigns
variables, executes statements conditionally, displays dialog boxes, and so on. 4. The macro recorder always creates Sub procedures. You can’t create a
Function procedure by using the macro recorder.
5. The code that is generated depends on certain settings that you specify.
6. You’ll often want to clean up the recorded code to remove extraneous commands.
61
61
About Objects and Collections
qThe object hierarchy:
§ At the top of this model is the Application object Excel itself. – Application
– Workbooks (a collection of all Workbook objects) – Worksheets (a collection of Worksheet objects)
+ ChartObjects (a collection of ChartObject objects) – Range
– PageSetup
+ PivotTables (a collection of PivotTable objects) +…
+ Charts (a collection of Chart objects)
+ Names (a collection of Name objects) +…
+ Windows(acollectionofallWindowobjects) + AddIns(acollectionofallAddInobjects) +…
62
62
31
About collections
qA collection is a group of objects of the same class, and a collection is itself an object.
qFor example: Workbooks is a collection of all Workbook objects currently open. Worksheets is a collection of all Worksheet objects contained in a particular Workbook object.
qTo reference a single object from a collection, you put the object’s name or index number in parentheses after the name of the collection, like this:
or
63
63
Referring to objects
qWhen you refer to an object using VBA, you often must qualify the object by connecting object names with a period (also known as a dot operator).
qFor example:
64
64
32
Properties and Methods
qObject properties: qObject methods:
§ Without arguments: § With arguments:
65
65
VBA Programming Fundamentals
qComments
qVariables, Data Types, and Constants qAssignment Statements
qArrays
qObject Variables
qBuild-in Functions
qManipulating Objects and Collections qControlling Code Execution
66
66
33
Comments
q A comment is descriptive text embedded within your code and ignored by VBA. § VBA ignores any text that follows an apostrophe up until the end of the line.
Except when the apostrophe is contained within quotation marks
§ The keyword Rem can also be used to mark a line as a comment
67
67
Variables
qA variable is simply a named storage location in your computer’s memory. qA few rules regarding variable names:
§ You can use alphabetic characters, numbers, and some punctuation characters, but the first character must be alphabetic.
§ You can’t use spaces or periods. To make variable names more readable, programmers often use the underscore character (Interest_Rate).
§ You can’t embed special type declaration characters (#, $, %, &, or !) in a variable name.
§ Variable names can be as long as 254 characters.
68
68
34
Data Types
qData type refers to how data is stored in memory:
§ Byte, Boolean, Integer, Long, Single, Double, Currency, Decimal, Date, Object, String (variable length), String (fixed length), Variant (with numbers), Variant (with characters), User-defined.
qVBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data.
69
69
Different variables
qLocal variables: Use a Dim statement qPublic variables: Use a Public
qStatic variables: Use a Static keyword qConstant variables: use a Const keyword
70
70
35
Assignment Statements
qOperator (Precedence from high to low): §^, * and /, + and -, &, =, <, >, <=, >=, <>.
qLogical Operators:
§ Not, And, Or, Xor, Eqv, Imp
71
71
Arrays
qDeclaring arrays:
qDeclaring multidimensional arrays: qDeclaring dynamic arrays:
72
72
36
Object Variables
qAn object variable is a variable that represents an entire object, such as a range or a worksheet.
73
73
User-Defined Data Types
qVBA lets you create custom, or user-defined, data types. (Data Structure)
74
74
37
Built-in Functions
qVBA has a variety of built-in functions that simplify calculations and operations.
qMany VBA functions are similar (or identical) to Excel worksheet functions.
qTo get a list of VBA functions while you’re writing your code, type VBA followed by a period (.).
§ Provide an example or two? Show them
75
75
Manipulating Objects and Collections 1
qWith-End With constructs:
§ The With-End With construct enables you to perform multiple operations on a single object.
76
76
38
Manipulating Objects and Collections 2
qFor Each-Next constructs:
§ You want to perform some action on all objects in a collection
77
77
Controlling Code Execution
qGoTo statements qIf-Then constructs qSelect Case constructs qFor-Next loops
qDo While loops qDo Until loops
78
78
39
GoTo statements
qThe most straightforward way to change the flow of a program is to use a GoTo statement.
79
79
If-Then constructs
qBasic syntax: qAnother syntax
80
80
40
Select Case constructs
81
81
For-Next loops
82
82
41
Do While loops
qA Do While loop executes as long as a specified condition is met. § Repeat the loop until condition is False.
or
83
83
Do Until loops
qA Do Until loop executes until the condition is True.
or
84
84
42
VBA Sub Procedures
qDeclaring a Sub procedure:
§ Private: (Optional) Indicates that the procedure is accessible only to other procedures in the same module.
§ Public: (Optional) Indicates that the procedure is accessible to all other procedures in all other modules in the workbook. If used in a module that contains an Option Private Module statement, the procedure is not available outside the project.
§ Static: (Optional) Indicates that the procedure’s variables are preserved when the procedure ends.
85
85
Executing Sub Procedures
qExecuting a procedure with the Run Sub/UserForm command qExecuting a procedure from the Macro dialog box
qExecuting a procedure with a Ctrl+shortcut key combination
86
86
43
Executing a procedure from another procedure
qEnter the procedure’s name, followed by its arguments (if any) separated by commas.
qUse the Call keyword followed by the procedure’s name and then its arguments (if any) enclosed in parentheses and separated by commas.
qUse the Run method of the Application object. The Run method is useful when you need to run a procedure whose name is assigned to a variable.
87
87
Passing Arguments to Procedures
qBy reference: Passing an argument by reference simply passes the memory address of the variable. Changes to the argument within the procedure are made to the original variable.
This is the default method of passing an argument.
qBy value: Passing an argument by value passes a copy of the original variable. Consequently, changes to the argument within the procedure are not reflected in the original variable.
Use the ByVal keyword.
88
88
44
VBA Function Procedures
qFunction procedures are quite versatile, and you can use them in two situations:
§ As part of an expression in a VBA procedure § In formulas that you create in a worksheet
qThe syntax for declaring a function is as follows:
89
89
Executing function procedures
qCall it from another procedure. qUse it in a worksheet formula.
qUse it in a formula that’s used to specify conditional formatting. qCall it from the VBE Immediate window.
90
90
45
Function Arguments
q Arguments can be variables (including arrays), constants, literals, or expressions. q Some functions don’t have arguments.
q Some functions have a fixed number of required arguments (from 1 to 60).
q Some functions have a combination of required and optional arguments.
q Some functions have an indefinite number of arguments.
91
91
Debugging Functions
q Place MsgBox functions at strategic locations to monitor the value of specific variables. Message boxes in Function procedures do pop up when the procedure is executed.
q Test the procedure by calling it from a Sub procedure, not from a worksheet formula.
q Set a breakpoint in the function and then step through the function. To set a breakpoint, move the cursor to the statement at which you want to pause execution and then choose Debug➜Toggle Breakpoint (or press F9).
q Use one or more temporary Debug.Print statements in your code to write values to the VBE Immediate window.
92
92
46
VBA Programming Examples
qRanges Examples
qWorkbooks and Sheets Examples qOther Examples
93
93
Ranges Example 1
qDuplicating rows a variable number of times
94
94
47
Ranges Example 2
qCopying a noncontinuous range
95
95
Workbook and worksheets Example
qSynchronizing worksheets
96
96
48
Other Examples 1
qGetting a list of fonts
97
97
Other Examples 2
qProcessing a series of files
98
98
49
Other Examples 3
qEmulating Excel’s SUM function
99
99
Week 3
qVBA – cont.
qPortfolio selection using excel. Matrix operation involved. qExcel charting
qStart R (depending on the progress)
qQuiz of Excel/VBA: Week 4
100
100
50