VBA Programming
for Finance
Indian Institute of Quantitative Finance
Understanding the basics
The need for VBA programming in Excel

Often, we encounter tasks that:
– Tend to be Repetitive
– Follow standard procedures
– Involve Complex Execution (not logic)
– Might involve Reiterations / Simulations
– Might involve complex formula setup in Excel
– Might be beyond the scope of Excel
– Require confidentiality to be maintained
2
Indian Institute of Quantitative Finance
Understanding the basics
What is a macro? UDF?

Such tasks can be effectively handled using Macros and User
Defined Functions (UDF)

Macro is a set of code to execute certain tasks

It is written in the VBA environment

It uses Visual Basic as the programming language

Basic understanding of code and sound logic sense is the key
3
Indian Institute of Quantitative Finance
Understanding the basics
Your first Macro

Let’s try building a simple macro

As soon as you open a workbook you would like to see a
message “Welcome to VBA”

For this we need to use a Message box. In VBA, the keyword
for the same is msgbox

Put out the following code in This Workboook Module
Private Sub Workbook_Open()
Msgbox “Welcome to VBA”
End Sub
4
Indian Institute of Quantitative Finance
Recording a Macro
The Basics


To start with, you do not have to worry about writing codes
and remembering syntaxes
You could simply record what you want to achieve and then
apply it repeatedly
Proprietary Material – Guruprasad Jambunathan
5
Recording a Macro
The Basics
Each macro so recorded would be saved in a module
 Below you see the window for recording a macro
 You can set the name, short-cut key and save location of the
macro
Naming a Macro
Assign a short-cut
key

Saving location
Proprietary Material – Guruprasad Jambunathan
6
Recording a Macro
Exercise

Let’s look at Record Macro Exercise 1

Let’s say we want to sort by descending order of scores

Let’s record a macro to do this task

Then, let’s change some scores and run the macro

Then, let’s try and decipher the macro.

Now, let’s try the following complication:
– Allow for data check up to row 25
– First sort by Score (desc.) and then by Education (asc.)

Recording and tweaking is the best way to learn
Proprietary Material – Guruprasad Jambunathan
7
Recording a Macro
Exercise

Let’s say
– I am not aware of rows and columns in the data set
– I would like to offer sorting criteria to the user
– I would like to do a custom sorting

It may not be possible to capture all these requirements by
just recording and making minor adjustments

Good understanding of various options is needed

Further, we may need to understand how to build
interactivity with Excel / user
Proprietary Material – Guruprasad Jambunathan
8
Recording a Macro
Exercise
• Let’s do one more macro recording – Exercise 2
• Let’s say, we would like to shade those days with returns
above +15 % or below -15% in a different colour
• Run a Descriptive Statistic for the data until Row 450
• Record a macro to highlight the more favourable Mean,
Median, Standard error values – Benchmark or Strategy
• Now, re-run the Descriptive Stats with complete data
• Apply the macro. Does it work fine?
9
Indian Institute of Quantitative Finance
Introduction to
Programming
Indian Institute of Quantitative Finance
Programming Basics
Algorithms, Pseudocode and Flow Charts
 Programming Languages
 VB and VBA Language

Indian Institute of Quantitative Finance
Algorithms and Flow Charts
Elements of a flow chart
 Terminal Box
 Process Box
 Input and Output Box
 Decision Box
 Disk File
 Data store Box
 Flow lines
 Data – Constant and Variable
Indian Institute of Quantitative Finance
Elements of a flow chart
Indian Institute of Quantitative Finance
Elements of a flow chart
Indian Institute of Quantitative Finance
Algorithms and Flow Charts
A flow chart for adding two user input
numbers
 A flow chart for printing the greater between
two user input numbers
 A flow chart for printing the greatest number
among five user input numbers
 A flow chart for printing the greatest number
among any sequence user input numbers

Indian Institute of Quantitative Finance
Flow chart for printing
the Factorial of a
number
Indian Institute of Quantitative Finance
Introduction to
VBA Programming
Indian Institute of Quantitative Finance
Excel VBA Editor
Getting started

VBA Editor window is initiated by pressing Alt+F11
Project
Explorer
Module
Window
Properties
Window
Project
Tree
18
Indian Institute of Quantitative Finance
Excel VBA Editor
Getting started

Project Explorer
– Name given to the overall editor that includes 3 components

Project Tree
– Basic Navigation tool across various modules arranged in hierarchy
– Starts with ‘Microsoft Excel Objects’ broken down into sheets/forms
– ‘ThisWorkbook’ module is key as it facilitates codes for actions to be
performed across the work book

Properties Window
– Carries basic default properties of the various modules

Module Window
– Contains the code that is to be executed
Indian Institute of Quantitative Finance
19
VBA Programming Basics
Constants
 Variables – memory allocation
 Literals
 Statements

– Separator – New line character
– Connector – UnderscoreAmpersand character
Key words
 Operators

Indian Institute of Quantitative Finance
VBA Programming Basics
Constant

A Constant is an item that cannot change its value during the
execution of the program

It is defined using the keyword Const

For instance, Const a = “D:\my folder”

VBA also has certain standard pre-defined constants. They
start with the prefix xl. These are used as specific options to
define object properties. We will see xlMaximised /
xlMinimised as definitions for WindowState Property for
Window Object
Indian Institute of Quantitative Finance
21
VBA Programming Basics
Variables

Variable is an item that could take various values

The values could dynamically change over code execution
22
Indian Institute of Quantitative Finance
VBA Programming Basics
Literals

Literals are similar to constants
23
Indian Institute of Quantitative Finance
VBA Programming Basics
Variable
Declaration
Variable Naming Convention
Variable Scope
Indian Institute of Quantitative Finance
VBA Variable Declaration
In VBA, one needs to declare the variables
before using them by assigning names and data
types. They are normally declared in the
general section of the codes' windows using
the Dim statement.
The syntax is as follows:
Dim <variable name> as [Data Type]
Indian Institute of Quantitative Finance
VBA Variable Declaration
Examples:
Dim Myname As String
Dim firstnum As Integer
Dim total As Long
Dim doDate As Date
Indian Institute of Quantitative Finance
VBA Variable Declaration
Variables

Explicitly declaring a variable is not compulsory

In case of explicit non-declaration, VBA would implicitly
declare one, the first time it is used

However, if we make a spell error, then it would assign the
mistaken spell as new variable

To prevent this, we could say ‘Option Explicit’

This would be a module-specific declaration

Once done, all variables will have to be explicitly declared
27
Indian Institute of Quantitative Finance
VBA Variable Declaration
Data types

If we need to declare multiple variables, do it individually

Let’s say we need to declare a and b as integers

We say:
Dim a as integer
Dim b as integer

Alternatively, we say Dim a as integer, b as integer

We cannot say Dim a, b as integer

It is good to declare variables in variable declaration area at
the top of each sub-routine / function
28
Indian Institute of Quantitative Finance
VBA Variable Naming
The following are the rules when naming the
variables in Visual Basic:
It must be less than 255 characters
No spacing is allowed
It must not begin with a number
Period is not permitted
Should not be VBA Reserve words like Sub,
End, Function, For, Do, Loop, and so on. If
you do, you will get error message ‘Expected
Identifier’
Indian Institute of Quantitative Finance
VBA Variable Naming
Valid Name
My_Car
ThisYear
Long_Name_Can_beUSE
Invalid Name
My.Car
1NewBoy
He&HisFather (*& is not acceptable)
Indian Institute of Quantitative Finance
VBA Data types
Data types

While declaring variables, specifying data type is optional

It defines characteristic of values the data type can take

If not explicitly specified, it assumes Variant data type that
can take any value
31
Indian Institute of Quantitative Finance
VBA Data types
VBA Data Types
 Fundamental Data Types
– Numeric data types
– Non-Numeric data types

Derived Data Types
– Enumeration
– Type
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Numeric data types are types of data that
consist of numbers, which can be computed
mathematically
with
various
standard
operators such as add, minus, multiply, divide
and more.
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Type
Byte
Integer
Long
Storage
1 byte
2 bytes
4 bytes
Single
4 bytes
Double
Currency
8 bytes
8 bytes
Decimal
12 bytes
Range of Values
0 to 255
-32,768 to 32,767
-2,147,483,648 to 2,147,483,648
-3.402823E+38 to -1.401298E-45 for negative values
1.401298E-45 to 3.402823E+38 for positive values.
-1.79769313486232e+308 to -4.94065645841247E-324 for
negative values
4.94065645841247E-324 to 1.79769313486232e+308 for
positive values.
-922,337,203,685,477.5808 to 922,337,203,685,477.5807
+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is
use
+/- 7.9228162514264337593543950335 (28 decimal places).
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Byte
Data Type (0 to 255)
Byte is VBA's smallest numeric data type and
holds a numeric value from 0 to 255. This data
type doesn't include any negative values. If
you attempt to assign one, VBA returns an
error.
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Integer
Data Type
This is probably the most common data type in
use, besides String. Use this data type to store
only whole numbers that range from –32,768
to 32,767.
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Long
Data Type
The Long data type is also an Integer data type
storing only whole numbers, but the range is
much larger than the traditional Integer data
type. Use Long to store values from –
2,147,483,648 to 2,147,486,647.
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Single
Data Type
The Single data type stores precision
numbers—numbers with decimal places or
fractional numbers. The data type is similar to
Double, but the range is smaller. Use this data
type to store values from –3402823E38 to –
1.401298E–45 or from 1.401298E–45 to
3.402823E38.
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Double
Data Type
Use the Double data type to store precision
floating point numbers from:
–1.79769313486232 E 308
to –4.94065645841247 E -324
OR
1.79769313486232 E 308
to 4.94065645841247 E -324.
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Currency
Data Type
Use the Currency numeric data type to store
monetary values from –922,337,203,477.5808
to 922,337,203,685,477.5807.
Indian Institute of Quantitative Finance
VBA Numeric Data Types
Decimal
Data Type
The Decimal data type is a subtype of Variant
and not a truly separate data type all its own,
accommodating
values
from
–
79,228,162,514,264,337,593,543,950,335 to
79,228,162,514,264,337,593,543,950,335
if
the value contains no decimal places.
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
Nonnumeric data types are data that cannot be
manipulated mathematically using standard
arithmetic operators. The non-numeric data
comprises text or string data types, the Date
data types, the Boolean data types that store
only two values (true or false), Object data
type and Variant data type .
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
Data Type
String(fixed length)
String(variable
length)
Date
Boolean
Object
Variant(numeric)
Variant(text)
Storage
Length of string
Range
1 to 65,400 characters
Length + 10 bytes
8 bytes
2 bytes
4 bytes
16 bytes
Length+22 bytes
0 to 2 billion characters
January 1, 100 to December 31, 9999
True or False
Any embedded object
Any value as large as Double
Same as variable-length string
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
Boolean
Data Type (–1 or 0)
Use the Boolean numeric data type to store
logical data that contains only two values: on
and off, true and false, yes and no, and so on.
The keywords True and False are predefined
constants and are interchangeable with the
values –1 and 0, respectively.
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
String
Data Type
String is another very common data type; it
stores values or numbers, but treats them as
text. There are two varieties: fixed and
variable. A fixed string can handle from 1 to
65,400 characters. To declare a fixed string,
use the Dim statement in the form
Dim variablename As String * stringlength
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
In contrast, the variable String data type grows
and shrinks as required to fit its stored value.
By default, all String variables are of this type.
To declare this type, use the Dim statement in
the form
Dim variablename As String
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
Date
Data Type
The Date data type stores a specially formatted
numeric value that represents both the date and
time. You don't have to store both the date and
time value. The Date data type accepts either
the date or the time, or both. Possible values
range from January 1, 100 to December 31,
9999.
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
Object
Data Type
An Object variable is actually a reference to an
Access object, such as a form, report, or
control. Or, the data type can reference an
ActiveX component, or a class object created
in a class module.
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
Variant
Data Type
The Variant data type stores numeric and nonnumeric values. This data type is the most
flexible of the bunch because it stores very
large values of almost any type (matches the
Double numeric data type). Use it only when
you're uncertain of the data's type or when
you're accommodating foreign data and you're
not sure of the data type's specifications.
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
The Variant data type is VBA's default, so the
following code interprets varValue as a
Variant:
Dim varValue
Indian Institute of Quantitative Finance
VBA Non-Numeric Data Types
Although the Variant data type is flexible,
VBA processes these data types a little slower
because it must determine the most accurate
data type for the assigned value. However,
most likely, you'll never notice the
performance hit.
The biggest disadvantage is the data type's lack
of readability. By that, we mean that you can't
easily determine the appropriate data type by
viewing the code, and that can be a problem.
Indian Institute of Quantitative Finance
VBA Data types
User-defined data types

Most powerful use of VBA is it allows for custom data types

This is done by syntax: Type <Type Name>
a as <data type>
b as <data type>
End Type

This is fed in the declaration section (above the macro code)

We declare it as Dim New as <Type Name>

We feed in data in the form New.a = <value>
52
Indian Institute of Quantitative Finance
VBA Data types
User-defined data types

For instance, let us say we collect 3 data points from an
investor: Name, Age and Date of Investment

We create a custom data type: Investor as
Type Investor
Nm as String
Age as Integer
DoI as Date
End Type
53
Indian Institute of Quantitative Finance
VBA Data types
User-defined data types

We could then use Investor as a data type now

For instance, Dim a(10) as Investor

We could then assign values as

Define a data type Fund with 3 parameters: Name, Category,
a(0).Nm=“Rahul”
a(0).Age = 25
a(0).DoI = “12-Sep-2011”
Performance

Define a 3 index member array as Fund and assign values

Display info of your choice
54
Indian Institute of Quantitative Finance
VBA Data types
Data types

Vartype() is a function which helps to test the kind of value a
variable is holding

Other variable data type related functions include:
IsNumeric(), IsEmpty(), IsNull(), IsDate()
55
Indian Institute of Quantitative Finance
VBA Data types
Data types

Consider the code
Sub macro1()
Dim a as variant
a=3
Msgbox vartype(a)
a=“Hi”
Msgbox vartype(a)
End Sub

Now, consider this example:
Sub macro1()
Dim a as integer
a=3
Msgbox isNumeric(a)
End Sub
56
Indian Institute of Quantitative Finance
VBA Data types
Data types

Now, let us extend the example:
Sub macro1()
a = “”
Msgbox isempty(a)
Msgbox isnull(a)
Msgbox isnumeric(a)
a=0
Msgbox isempty(a)
Msgbox isnull(a)
Msgbox isnumeric(a)
a = Null
Msgbox isempty(a)
Msgbox isnull(a)
Msgbox isnumeric(a)
End Sub
57
Indian Institute of Quantitative Finance
VBA Data types
Data types

In this example, you would note the following:
– “” is taken either as numeric and or regarded as empty
– “” Does not mean Null
– When we assign ‘0’ to the variable, it turns numeric but non-empty
and Non-Null
– “” or 0 does not mean null
– It has to be explicitly assigned as Null
– Null is used to denote missing or unknown values
58
Indian Institute of Quantitative Finance
VBA Key words
All words for data types
 All words for VB commands

Indian Institute of Quantitative Finance
VBA Operators

Arithmetic Operators
–
–
–
–

Addition
Subtraction
Multiplication
Exponentiation
String Operators
– Concatenation
Indian Institute of Quantitative Finance
Operators
Operators

Basic operators as used in Excel could also be used in VBA

Such operators include:
– Arithmetic: +, -, *, /, ^
– Comparison: <, >, =, <>, <=, >=
– Concatenation: &
– Special Operators: Is – Helps to compare two reference object
Like – Helps to compare two strings
– Example: Msgbox worksheets(1) Is Worksheets(2)
Msgbox “Richard” Like “richard”
61
Indian Institute of Quantitative Finance
Operators
Operators

To use the Like operator, we need to explicitly declare we are
comparing two text items.

This is done by stating: Option Compare Text

Some characters which could also be used for comparison:
– ? : one character; Msgbox “Richard” Like “ric?ard”
– *: several characters; Msgbox “Richard” Like “ric*”
– #: Numeral; Msgbox “Richard5” Like “richard#”
62
Indian Institute of Quantitative Finance
VBA Programming
Logic Structures
 Control Structures
 Sub routines
 Functions

Indian Institute of Quantitative Finance
VBA Logic Structures
Decision Making

Decision - checking on condition(s) and evaluate actions
accordingly
– If…Then
– Select…Case
64
Indian Institute of Quantitative Finance
VBA Logic Structures
Indian Institute of Quantitative Finance
VBA Logic Structures
If..Then..Else





Checks whether a condition is true or false
Works out an action according to the result
Syntax:
If <condition> Then
<true code>
Else
<false code>
End If
Multiple conditions with AND / OR operators
Syntax:
If <condition 1> and <condition 2> Then
Indian Institute of Quantitative Finance
66
VBA Logic Structures
Indian Institute of Quantitative Finance
VBA Logic Structures
If..Then..ElseIf






Like Excel, we could have Nested Ifs  If within If
End If will have to be used as many times too
We could also have more than 2 choices
In such a scenario, we use ElseIf…Then as many times as
we have choices
There would however be only one End If
Let’s extend the same exercise to cover 3 options: Absolute
value, Relative value or Both
68
Indian Institute of Quantitative Finance
VBA Logic Structures
Indian Institute of Quantitative Finance
VBA Logic Structures
Select Case



When we have multiple choices, then it would be tough to
write if statements for each choice
Rather we could use Select Case
Syntax:
Select Case <variable Name>
case <case value>
<action>
case <case value>
<action>
case Else
<action>
End Select
70
Indian Institute of Quantitative Finance
VBA Logic Structures
Select Case

Try the previous exercise with select case instead of Elseif
– Case 0 for Relative returns, Case 1 for Absolute returns and
Case 2 for both
– Case Else could be used to capture any other number entered
instead of 0, 1 or 2


In Select Case, we could also use To and Is in defining cases
Example: Case 1 to 5 or Case is >5
71
Indian Institute of Quantitative Finance
VBA Control Structures
Do-While Loop
Do While [condition]
[statements]
[Exit Do]
Loop

Indian Institute of Quantitative Finance
VBA Control Structures
Do…loops




Do based loops are executed until the condition is satisfied
We could present it as Do while or Do Until
Syntax:
Do While /Until <condition>
<action>
Loop
Consider the following code:
Sub test()
x=0
Do While x < 5
x=x+1
MsgBox x
Loop
Indian Institute of Quantitative Finance
73
VBA Control Structures
Do Loop
Do
[statements]
Exit Do
While [condition]

Indian Institute of Quantitative Finance
VBA Control Structures
Do…loops

Let’s consider the same exercise as For…Next loop

We would not move the counter from moving average row
number until count of last element

We would do the action (putting in a technical indicator)
until cell value turns empty

Isempty(activecell) = True would be the condition employed

Activecell.offset(<row>,<column>) would also be used
75
Indian Institute of Quantitative Finance
VBA Control Structures
Do…loops


We could work out the same with a Do..until loop
Consider the following code:
Sub test()
x=0
Do until x = 5
x=x+1
MsgBox x
Loop
76
Indian Institute of Quantitative Finance
VBA Control Structures
For-Next Loop
For [condition]
[statements]
Exit For
Next

Indian Institute of Quantitative Finance
VBA Control Structures
For..Next





One of the key benefits from macro comes from repeating a
task multiple times
This is facilitated by loops
For..Next runs a set of code until stated condition is satisfied
For <var. name> = <start value> to<end value> step <value>
<action>
Next <variable name>
Example: For i=3 to 18 step 3
Msgbox I
Next i
78
Indian Institute of Quantitative Finance
VBA Control Structures
For..Next

Let’s say we want a column of technical indicator:
– Ask user for fund and moving average (days)
– If selected Fund trades on a particular day above user defined
moving average, we show 1 else 0
– This is computed over the entire period, starting from ma+1
days
– In other words, if user wants 100day ma, we compute technical
indicator from 101th day to end
– Compute Proportion of days when the indicator was positive
79
Indian Institute of Quantitative Finance
VBA Control Structures
For..Next

We would execute this using a for next loop where
– We first get inputs on fund and moving average (days) from user
– Assuming starting from ma+1 to 1000 days, we compute a technical
symbol 1 or 0 using for next loop and formulaR1C1
– We count the number of entries and place it in a cell
– Based on this the for loop would move from ma+1 to the count
– We then count number of days we get a technical uptick (1) using
formula R1C1 and from there get the proportion
80
Indian Institute of Quantitative Finance
VBA Control Structures
For..Each

For..Each loop is similar to For..Next

However, they are generally used to define each element
within a collection

For instance, worksheet within worksheets, workbook within
workbooks, cell in a range

For each <element name> in <collection name>
<action>
Next <element name>
81
Indian Institute of Quantitative Finance
VBA Control Structures
For..Each

Let’s consider the following codes:

Sub Foreachcheck1()
Dim wsheet as worksheet
For each wsheet In worksheets
Msgbox wsheet.name
Next wsheet
End sub

Another common application is in referencing elements in a
data array
This is done by defining the array elements by a variant term

82
Indian Institute of Quantitative Finance
VBA Control Structures
For..Each

Sub Foreachcheck2()
Dim tick As Integer
Dim arr(4) As Single
Dim base As Single
Dim counter As Variant
For tick = 0 To 4
arr(tick) = InputBox("Enter the exchange rate")
Next tick
base = InputBox("Enter the earnings in foreign currency")
For Each counter In arr
MsgBox "Value in INR:" & base * counter
Next counter
End sub
83
Indian Institute of Quantitative Finance
VBA Programming
Arrays in VBA
 Array data types
 Single dimensional Arrays
 Multi dimensional Arrays
 Dynamic Arrays
 How we can access arrays’ elements with
control structures
Indian Institute of Quantitative Finance
VBA Variable Declaration
Index and Arrays

Sometimes, we might have to store multiple data points
associated with the same variable

Let’s say we have investment return of 10 fund managers

Instead of defining each manager’s return by a separate
variable, we could use an index where A1 relates to 1st
manager, A2 to 2nd and so on

In VBA, this is called variable indexing or array

It is defined as Dim a[9] as integer
85
Indian Institute of Quantitative Finance
VBA Variable Declaration
Index and Arrays

Note that for 10 managers, we have created an index for 9, as
in VBA index by default starts from 0

We have index numbers from 0 to 9 – thus, 10 cases

To refer to a particular index value, we refer it as a(i)

For instance, to assign values to 4th unit, we say a(3)=0.101

Let’s consider an example, where we create an index of 10,
assign random values to the same

Then, use the Msgbox function to display average returns
86
Indian Institute of Quantitative Finance
VBA Variable Declaration
Index and Arrays

We could change the default index start from zero to one

We say Option base 1, before the sub-function as it a module
level assumption

We could also start from any integer, including –ve values

For this, we say Dim a(1 to 10) as Long

Let us say, we want to enter last three year of trailing and
forward PE for a company. Create an array that moves from
index -3 to 3, feed in the value and display the 3-year trailing
and forward PEs
Indian Institute of Quantitative Finance
87
VBA Variable Declaration
Multi-dimensional arrays

We could also have multi-dimensional arrays

They are defined as: Dim a(x,y,z) as [data type]

Create data on 2 companies, with 1 year historic DY, current
and 2 year forward DY. Create a 2-dimensional array

Display the average DY for the current year

There are 2 functions linked to arrays: Lbound(), Ubound()

These function return lower and upper bound index numbers

To the example above, display its lower and upper bounds
88
Indian Institute of Quantitative Finance
VBA Programming
Arrays
and matrix algebra
How efficiently Multidimensional Array can
be used to manipulate matrices
Indian Institute of Quantitative Finance
Matrix Algebra
Matrix entry

We have already seen multi-dimensional array creation

It is defined as Dim X(<a range>,<b range>) as <data type>

Matrices are defined as two-dimensional arrays, with rows
and column requirement defining a and b ranges

For instance, Dim Ret(1 to 3, 1 to 4) could be viewed as 3*4
matrix

Assigning value to such matrices is generally done using two
For…Next loops
90
Indian Institute of Quantitative Finance
Matrix Algebra
Matrix entry

Consider the following code:
Sub Matrixentry()
Dim ret(1 To 2, 1 To 3) As Single
Dim i As Integer
Dim j As Integer
For i = 1 To 2
For j = 1 To 3
ret(i, j) = InputBox("Enter return matrix values")
cell(i, j).Value = ret(i, j)
Next j
Next i
End Sub
Indian Institute of Quantitative Finance
91
Matrix Algebra
Matrix Addition

Let’s add one more similar 2*3 matrix to the previous code

Repeat the same code as previous one to capture data for the
second matrix

Let’s then add the corresponding elements to form the matrix
addition matrix

Here, no matrix formula is applied as with Excel; Only
addition of corresponding elements in the matrix
92
Indian Institute of Quantitative Finance
Matrix Algebra
Matrix Multiplication

Let’s say we want to multiply two matrices

We know how to gather data points for the same in VBA

In Excel, we use the MMULT function

We initiate the same function in VBA through
Application.WorksheetFunction.MMULT (<matrix 1
name>,<matrix 2 name>) (<row ref>,<column ref>)

Let’s consider a 2*3 matrix and 3*2 matrix. Let’s get the
values from user, multiply them and display the result
93
Indian Institute of Quantitative Finance
Matrix Algebra
Matrix Transpose

Similar to matrix multiplication, we find transpose of a
matrix using Application.WorksheetFunction.Transpose
(<matrix 1 name>) (<row ref>,<column ref>)

Get 2 set of 2*3 matrix data from user, transpose the first
one, multiply with the second one and display the result
94
Indian Institute of Quantitative Finance
Matrix Algebra
Matrix Inverse

Similarly, we find inverse of a square matrix using
Application.WorksheetFunction.Inverse (<matrix 1 name>)
(<row reference>,<column reference>)

Matrix determinant is found as
Application.WorksheetFunction.MDeterm (<matrixname>)

Get a 2*2 matrix data from user, find inverse and display it

Compute determinant of original matrix and display

Check whether the multiplication of the original matrix with
inverse matrix leads to Unitary matrix
95
Indian Institute of Quantitative Finance
Matrix Algebra
Variance-Covariance Matrix

One key use of Arrays and Matrix in VBA is concerned with
setting a variance-covariance matrix

Steps to create a Variance-Covariance matrix:
– Fix ranges of individual elements. If it is 3*3 matrix, we have 3
elements and hence we need to fix ranges of 3 elements
– For..Next loop is used in fixing ranges
– Each relevant range is placed in columns in the data
– Hence, Range is fixed as:
Set <Rng Nm>(<emt rf>) = Range(“<Rng rf>”).columns(<emt rf>)
96
Indian Institute of Quantitative Finance
Matrix Algebra
Variance-Covariance Matrix

Steps to create a Variance-Covariance matrix (contd.):
– Once the ranges of individual elements are fixed, we create the matrix
– Matrix creation is similar to what we have seen so far
– We use two for…next loops to assign values to matrix elements
– Application.WorksheetFunction.covar(<range1 ref>,<range2 ref>)

In the variance covariance matrix exercise, compute variance
covariance and correlation matrices based on the 3 funds
97
Indian Institute of Quantitative Finance
Sub-routines and Functions
Modules and Procedures

All VBA program code is executed in a module

Module is an area specific to an application. They do not get
initiated automatically, but has to be called for execution

Modules contain procedures that carries the code

A workbook can have multiple modules

Procedures are of two types: Sub-routines and Functions

Sub-routine defined by keyword Sub and Function by
Function

By default, procedure is a sub-routine
Indian Institute of Quantitative Finance
98
Sub-routines and Functions
Sub-Routine

Sub-routine does not return any value as output. It simply
contains code to be executed

Sub-routine is defined by the syntax
Sub <Sub-routine name> ([input arguments])
End Sub

Let’s take a simple example
Sub trial_macro1()
Msgbox “Hello”
End Sub

This would display a message box with text ‘Hello’
Indian Institute of Quantitative Finance
99
Sub-routines and Functions
Sub-Routine

The program written within a sub-routine could be called
anywhere in the program using the Call keyword

Sub TrialSub(TrialText as String)
Msgbox TrialText
End Sub

Sub Macro1()
Call trialsub(“Welcome to sub-routines”)
End Sub
100
Indian Institute of Quantitative Finance
Sub-routines and Functions
Functions

Function is similar to sub-routines but can return a value that
can be used for further calculation

Functions are created with the syntax Function
Function called using a variable
Let’s consider the following:


Function Multiply (a as integer, b as integer)
Multiply = a*b
End Function

Now, let’s add the following to Macro1()
x = multiply(3,4)
Msgbox x
Indian Institute of Quantitative Finance
101
Sub-routines and Functions
Scope of procedures







Procedures could have a Public or Private Scope
By default sub-routines/functions are Public
Public scope would mean a procedure can be accessed across
modules of a workbook
Private - scope is limited to a particular module
Cannot be accessed from other modules within the same
workbook
Alternatively, the same variable used in one private
procedure can be defined again in other procedures
Private procedure are also not seen in Macro window
102
Indian Institute of Quantitative Finance
Sub-routines and Functions
Parameter definition








As we have seen, both sub-routines and functions can accept
parameters as input arguments
Parameters are defined like variables along with data types
Sub <Sub-routine name>(<param name> as <data type>)
Function <Function name >(<param name> as <data type>)
A sub-routine or function can have as many parameters
Some parameters may be compulsory and some optional
Optional ones defined after compulsory ones with Optional
Sub <Sub-routine name>(<param1 name> as <data type>,
Optional <param2 name> as <data type>)
103
Indian Institute of Quantitative Finance
Sub-routines and Functions
Comments, Indent and Referencing



We can add comments to our VBA code to facilitate easy
understanding for users
It is done by placing (‘) in front of the text
It’s an indication that it should not be executed

It is always a good practice to indent the code as it facilitates
easy reading

R1C1 is a style of referencing cells in Excel
For instance, cell A3 can also be referred as R3C1

104
Indian Institute of Quantitative Finance
Sub-routines and Functions
Writing a sub-routine and function






Let’s say we first write a function to convert a given rate
based on a given compounding frequency into an Effective
Annual yield (annual compounding)
Now write separately a sub-routine to capture FV of an
investment based on given PV, annual rate and n (years)
Now, call the function to convert into EAY a 10% rate
compounded semi-annually
Present the EAY through a message box
Now for PV of Rs.1000 and 5 years with EAY also as input,
call the sub-routine to capture FV and display it with msgbox
Hint: Declare all variables with suitable data types
105
Indian Institute of Quantitative Finance
User Defined Functions
User Defined Functions (UDF) can provide
great power and convenience and can be very
simple to write.
Indian Institute of Quantitative Finance
VBA UDF
Indian Institute of Quantitative Finance
UDF - Limitations
But there are some problem areas that may need
special attention in your UDF coding:






The UDF code must be in a General Module, not a
Sheet Module.
Action ignored: UDF "does nothing"
Not recalculated when needed or always
recalculating.
Unexpectedly returns #Value or other error.
Calculates more than once in a recalculation, the
Function Wizard or when entered.
Slow to calculate.
Indian Institute of Quantitative Finance
UDF - Limitations
Excel will not allow a UDF written in VBA to alter anything
except the value of the cell in which it is entered. You cannot
make a VBA UDF which directly:
Alters the value or formula or properties of another cell.
Alters the formatting of the cell in which it is entered.
Alters the environment of Excel. This includes the cursor.
Uses FIND, SpecialCells, CurrentRegion, CurrentArray,
GOTO, SELECT, PRECEDENTS etc : although you can use
Range.End.
Note you can use FIND in Excel 2002/2003/2007
Indian Institute of Quantitative Finance
User Interaction
Input Boxes

Before looking at Decision making and looping let’s look at
one tool for interacting with the user – Input Boxes

While a message box simply displays as output, input box
helps to gather input from the user

Input box is defined as:
<variable name>=inputbox (“<Question>”)

For instance, a = inputbox (“Enter term of investment:”)

The variable ‘a’ can then be used as input in a procedure
110
Indian Institute of Quantitative Finance
User Interaction
Input Boxes

Let’s modify the last example of finding FV of an investment

Let all inputs be provided by the user:
– Rate of interest and Compounding frequency
– PV and term of investment

Display appropriate output using message boxes
111
Indian Institute of Quantitative Finance
VBA Functions
String functions

We would look at some specific functions associated with
managing strings in VBA

Some of the functions could be similar to ones in Excel

Splitting Strings:
– Left(<Text>, <no. of characters>)
– Right(<Text>,<no. of characters>)
– Mid(<Text>,<start character>,<no. of characters>)

Try out the same with a dummy macro recording
112
Indian Institute of Quantitative Finance
VBA Functions
String functions

Changing String characteristics:
– Ucase(<Text>)
– Lcase(<Text>)
– Val(<Text>)  converts a numeral entered as a string into a number
For instance, try these codes
Msgbox right(“BN123”,3)*3
Msgbox Val(right(“BN123”,3))*3
– VBA recognises numbers automatically, however Excel may not
– Hence, it may be a good practice to explicitly convert it to a number
113
Indian Institute of Quantitative Finance
VBA Functions
String functions

Comparing functions:
– InStr([Start char],<base string>,<search string>,[compare type])
– Start char  Optional parameter to specify where to start search
– Compare type  Could be specified as VbTextCompare or
VbBinaryCompare
– VbBinaryCompare is the default setting. This will compare each
character’s binary value. Key result of this is that the comparison
would be case sensitive
– If we do not want comparison to be case sensitive, we need to specify
VbTextCompare
114
Indian Institute of Quantitative Finance
VBA Functions
String functions

Comparing functions:
– Let’s try the following example:
InStr( “Trial Statement here” , “statement”)
InStr(1,“Trial Statement here”, “statement”,VbTextCompare)
InStr(10,“Trial Statement here”, “statement”,VbTextCompare)
115
Indian Institute of Quantitative Finance
VBA Functions
Utility functions

Other common functions:
– Len(<Text>)
– Abs(<number>)
– Int(<number>)
– Format(<text/number>,<format type>) . Format Type could be Predefined or user-defined
Msgbox Format(“121.4565”,”#.##”)
Msgbox Format(“121.4565”,”Currency”)
Msgbox Format(“12/1/2011”,”dd-mmm-yy”)
116
Indian Institute of Quantitative Finance
VBA Functions
Utility functions

Pre-defined formats for Format type include
Format Type
Details
General Number
Displays number as it is
Currency
Displays along with set currency symbol
Fixed
Number with two decimal point accuracy
Standard
Number with 1000 separator and two decimal places
Scientific
Number displayed in standard scientific notation
Yes/No
Displays No if number is 0. Else, Yes
True/False
Displays False if number is 0. Else, True
On/Off
Displays Off if number is 0. Else, On
117
Indian Institute of Quantitative Finance
VBA Functions
Conversion functions

Conversion functions convert data type of one form to other

CStr(<value>)  Conversion to a string

CInt(<value>) Conversion to an Integer (rounded – off)

CLng(<value>)  Conversion to a Long value

CDbl(<value>)  Conversion to a Double value

Example:
Msgbox CStr(12)
Msgbox CInt(12.545)
118
Indian Institute of Quantitative Finance
VBA Functions
Date and Time functions

Date  Displays today’s system date

Now  Displays current date and time

DateAdd(<Interval type>, <number>,<base date>)  Adds
specified number to the given date
– Interval type  d, w (week days), ww (weeks),m, q, h, n (minute), s

DateDiff((<Interval type>, <date1>,<date2>) Shows the
difference between 2 dates, in specified interval type

DatePart(<Interval type>,<date>) Specific characteristic
of the given date
Indian Institute of Quantitative Finance
119
VBA Functions
Date and Time functions

DateSerial(<year>,<month>,<day>)  Converts a given
date into its corresponding serial number.
– However, we need to use CDbl along with the function so that
display is right. Else, Windows will show it by default only as date

Let’s try the following:
– Msgbox Date
– Msgbox DateAdd(“ww”,6,”12/09/2010”)
– Msgbox DateDiff(“m”,”12/01/2009”,”30/06/2010”)
– Msgbox DatePart(“q”,”12/05/2009”)
– Msgbox CDbl(DateSerial(2011,2,3))
120
Indian Institute of Quantitative Finance
VBA Functions
Initiating other applications

Two functions are available to initiate other applications

Variable name = Shell(<application cmd>,<window state>)

Once a document is initiated, we could activate using

AppActivate “<Application name>”

Consider the following example:
x = Shell("calc.exe", vbMinimizedNoFocus)
MsgBox "Calculator is open"
AppActivate "Calculator”
121
Indian Institute of Quantitative Finance
VBA Functions
Interacting with other applications

SendKeys application is used to pass info to applications

For numerals, we could specify it as they are

For other keys we place them within {}

For instance,
Sendkeys 3
Sendkeys “{+}”

Let’s say we activate calculator, then pass two numbers and
addition symbol to it to get the final output
122
Indian Institute of Quantitative Finance
VBA Functions
Interacting with other applications

Consider the following code:
x = Shell("calc.exe", vbMinimizedNoFocus)
MsgBox "Calculator is open"
AppActivate "Calculator"
SendKeys 1
SendKeys "{+}"
SendKeys 2
SendKeys "{=}”
123
Indian Institute of Quantitative Finance
Excel Object Model
Objects, Properties, Methods and Events

Excel carries a collection of Objects (also called Collections)

They are arranged in a Hierarchy starting from
Application  Workbook  Worksheet  Range and so on

Each object could have multiple sub-objects below it

Each object has its own set of Properties and Methods

Objects could also have associated Events - a pre-defined
action- which if it occurs we could execute certain code
Opening a workbook, Click of a mouse, Double click,
Activation of a cell are some instances of Events

124
Indian Institute of Quantitative Finance
Excel Object Model
Object Browser

In the Msgbox example, we executed a code when ‘Open’
event is initialised under ‘Workbook’ object

To get a sense of all objects and associated Properties and
Methods and events, we could check out the Object Browser
125
Indian Institute of Quantitative Finance
Excel Object Model
Object Browser
Class / Object
/ Collection
Event (Yellow lightning icon)
Method (Green icon)
Property (Grey icon with hand)
126
Indian Institute of Quantitative Finance
Excel Object Model
Object Browser
Save As Method of
Workbook Object
Methods are like Excel
functions. Parameters
associated with the method
are displayed below
127
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Window(s)
– Key Methods: Activate, AvtivateNext, ActivatePrevious, Close,
NewWindow
 Windows(“Book1”).Activate
 ActiveWindow.ActivateNext
 ActiveWindow.ActivatePrevious
 ActiveWindow.Close
 ActiveWindow.NewWindow
128
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Window(s)
– Key Properties: ActiveCell, ActiveSheet, Caption, GridLineColor,
DisplayGridlines,
 Windows(“Book1”).Activecell.value
 Windows(“Book1”).Activesheet.name
 ActiveWindow.Caption=“MyWindow”
 ActiveWindow.GridLineColor = RGB (100,0,0)
 ActiveWindow.DisplayGridlines = False
129
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Window(s)
– Key Properties: Zoom, WindowState, RangeSelection,
SelectedSheets, Split
 ActiveWindow.Zoom = 80
 ActiveWindow.WindowState = xlMinimised / xlMaximised
 Msgbox ActiveWindow.RangeSelection.Address
 Msgbox ActiveWindow.SelectedSheets.Count
 ActiveWindow.Split =True
130
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Workbook(s)
– Key Methods: Activate, PrintPreview, Save, SaveAs, Close, Protect,
 Workbooks(“Book1”).Activate
 Workbooks(“Book1”).PrintPreview
 ActiveWorkbook.Save
 ActiveWorkbook.SaveAs “D:\Trialfile.xls”
 ActiveWorkbook.Close
 ActiveWorkbook.Protect (“trialpwd”)
 ActiveWorkbook.Unprotect
131
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Workbook(s)
– Key Properties: ActiveSheet, ReadOnly, Saved, HasPassword, Sheets
 Msgbox ActiveWorkbook.ActiveSheet.Name
 Msgbox ActiveWorkbook.ReadOnly
 Msgbox ActiveWorkbook.Saved
 Msgbox ActiveWorkbook.HasPassword
 ActiveWorkbook.Sheets(“Sheet2”).Select
132
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Worksheet(s)
– Key Methods: Activate, Select, Calculate, Delete, PrintPreview,
CheckSpelling
 Worksheets(“Sheet1”).Activate
 Worksheets(“Sheet1”).Select
 Activesheet.Calculate
 Activesheet.Delete
 Worksheets("sheet2").PrintPreview
 Activesheet.Checkspelling
133
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Worksheet(s)
– Key Properties: Visible, Name, Range
 Worksheets(“Sheet3”).Visible = False
 Activesheet.Name = “Trialsheet”
 Activesheet.Range(“A1:B5”).Activate
134
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Range
– Key Methods: Activate, Select, Calculate, ClearContents, Copy,
PasteSpecial
 Worksheets(“Sheet3”).Range(“A1”).Activate
 Worksheets(“Sheet3”).Range(“C3”).Select
 Worksheets(“Sheet3”).Range(“C3:M15”).Calculate
 Activecell.ClearContents
 Worksheets("sheet2").Range(“F1:G5").Copy
 Worksheets("sheet2").Range(“A1").PasteSpecial
135
Indian Institute of Quantitative Finance
Excel Object Model
Key Objects and its Methods, Properties

Range
– Key Properties: Row, Column, Rows, Columns, ColumnWidth,
RowHeight, Value
 MsgBox Worksheets("sheet2").Range("b3:c5").Row
 MsgBox Worksheets("sheet2").Range("b3:c5").Column
 MsgBox Worksheets("sheet2").Range("b3:d7").Rows.Count
 MsgBox Worksheets("sheet2").Range("b3:d7").Columns.Count
 Worksheets("sheet2").Range("b3:c5").ColumnWidth = 5
 Worksheets("sheet2").Range("b3:c5").RowHeight = 12
 Worksheets("sheet2").Range("b3:c5").Value = 3
136
Indian Institute of Quantitative Finance
User Interaction
Interacting with Excel s/sheet

One could take inputs from Excel s/sheet and could feed data
into a s/sheet using macros

The relevant objects would be Cells and Range

Cells used for referencing a particular cell and Range for
defining a set of cells

Cells(<row index>,<column index>).select
cells(3,2).select

range(“<range>”).select
range(“A1:B3”).select
137
Indian Institute of Quantitative Finance
User Interaction
Interacting with Excel s/sheet

Consider the following: This macro would collect the row
and column number from user and present the product of two
numbers in the given row and column number
Sub Macro1()
Dim a as Integer,b As Integer
a = InputBox("what is the row number")
b = InputBox("What is the column number")
cells(a,b).value=a*b
End Sub
138
Indian Institute of Quantitative Finance
User Interaction
Interacting with Excel s/sheet






Cells(a,b).value helps in capturing the cell value
Now, try recording a macro: select a range, fill it with a
colour of choice and stop recoding the macro
Now, look at the macro in edit mode. The key code lines to
note would be: Range(“A1:B3”).select
Select method is used to define a range/cell
Once a cell or range is used, Selection object can be used to
perform operations with it
Selection.Copy, Selection.Paste, Selection.Font are some
examples
139
Indian Institute of Quantitative Finance
User Interaction
Interacting with Excel s/sheet






With selection.<method/property>
…
End with
is a property that is primarily used with formatting of cells
Copy or paste is a single activity  selection.copy
However, say within font, we look at colour, size and style
This can be collectively handled  with selection
Common application being Fill colour, font colour, borders,
alignment and the like
You need not know the syntax. It is best to get this done
using a recording of macro
Indian Institute of Quantitative Finance
140
User Interaction
Interacting with Excel s/sheet

Activate is a method that can be applied on a sheet/
workbook/ cells/ range

Practically, it is very similar to the Select method

For instance Cells(a,b).activate, Sheets(“Sheet2”).activate

Similar to Selection object that defines a selected range, an
activated object is defined by:

ActiveWindow, ActiveWorkbook, ActiveSheet, ActiveCell
141
Indian Institute of Quantitative Finance
User Interaction
Interacting with Excel s/sheet

Sub Macro1()
MsgBox ActiveWindow.Caption
ActiveWorkbook.SaveAs Filename:="Trial1.xls"
MsgBox ActiveWindow.Caption
Cells(4, 5).Value = "Trial value check"
MsgBox ActiveSheet.Name
MsgBox ActiveSheet.Cells(4, 5).Value
ActiveCell.Value = "Trial value checked"
MsgBox ActiveCell.Value
End Sub
142
Indian Institute of Quantitative Finance
User Interaction
Playing with rows and columns

A row or column is identified by using the object rows and
columns respectively
Example: Rows(3).select, Columns(5).select

Try recording a macro to insert a row/column

Now try refining it to make it user defined by way of an input
box

Try a similar exercise with insertion of worksheet
143
Indian Institute of Quantitative Finance
User Interaction
Feeding in Excel formulae

To put in an Excel formulae through a macro, we need to just
define it by:
cells(a,b).formula=“=Max(<range>)”
cells(11,1).formula=“=Max(“A1:A10”)”
cells(a,b).formulaR1C1 = “=Max(<range in R1C1>)”
cells(11,1).formulaR1C1=“=max(R[-11]C:R[-1]C)”

In both cases, we could alternatively select a cell and then
use Activecell property to place a formula
Cells(11,1).select
Activecell.formula=“=Max(A1:A10)”
144
Indian Institute of Quantitative Finance
User Interaction
Feeding in Excel formulae

Like the way we define number of rows and columns to work
on based on R1C1 referencing, we could make it dynamic

We could collect input from the user in terms of rows and
columns to move and we could compute formulae

In the previous example, try gathering Max data range using
inputs from the user

Note that ‘&’ is used to club elements of formulae and
reference

For instance,
activecell.formulaR1C1=“=Max(R[“ & -m & “]C:R[“& -n &”]C)”
145
Indian Institute of Quantitative Finance
User Interaction
Moving across the s/sheet

One useful property of Range/Cells object is
Offset(<rows>,<columns>)
Activecell.Offset(2,3)

It helps to move certain rows and columns from a given
starting reference cell

In the previous example, try getting the value in the data
array, based on user defined requirement
146
Indian Institute of Quantitative Finance
User Interaction
Complete Exercise

Before we take up a complete exercise using interaction with
Excel, note the following:
– No exercise is done fully by writing codes
– Part recording and Part writing is the best approach
– Do not be taken aback by the large volume of code that gets
generated when you record a macro
– Before recording, try and plan the record mentally
– This will ensure limited noise in the data and also the program would
move logically  easier to understand and debug
– For instance, the manner you move to a particular location and
selecting a range should be done according to requirements
147
Indian Institute of Quantitative Finance
User Interaction
Complete Exercise

Let’s look at an Exercise on User Input:
– Ask the user for product name and Quarter name
– Then provide information on sales enquiries and conversion for that
product/quarter
– Compute the conversion rate for each product and quarter and place it
via a formula in row 6
– Create a summary sheet with all the conversion rates
– Compute the average conversion rate for each qtr/pdt
– Present a message box with conversion rate of selected productquarter, as well as average for that pdt and qtr
148
Indian Institute of Quantitative Finance
User Interaction
Exercise – Breaking it up
– Ask the user for product name and Quarter name [Input Box]
– Then provide information on sales enquiries and conversion for that
product/quarter [ Coding, Message Box]
– Compute the conversion rate for each product and quarter and place it
via a formula in row 6 [Recording of macro]
– Create a summary sheet with all the conversion rates [Recording,
Tweaking the code]
– Compute the average conversion rate for each qtr/pdt [ Recording,
Tweaking the code]
– Present a message box with conversion rate of selected pdt-qtr, as
well as average for that pdt and qtr [ Coding, Message Box]
149
Indian Institute of Quantitative Finance
Excel Worksheet Functions
Worksheet functions

So far, to work out a Excel function through VBA, we have
used ActiveCell.Formula or FormulaR1C1

That places the formula in the Excel s/sheet

One other option is to use
Application.WorksheetFunction.<Function details>

Here, only the final value could be placed in a cell

For instance,
a=application.WorksheetFunction.vlookup(“A”, range(“A1:B20”),2,0)
150
Indian Institute of Quantitative Finance
Excel Worksheet Functions
Worksheet functions

However, note that all Excel functions may not be available
in VBA, although most of them do

In some cases, syntaxes could also be different

Also, usage of multiple functions like index…match
becomes difficult

Despite the limitations, it is worth with some complex
functions like Matrix functions

Let’s first understand basic usage using the looping example
151
Indian Institute of Quantitative Finance
Excel Worksheet Functions
Worksheet functions
Sub Wshtfn()
dtd = InputBox("What is the required day")
dtm = InputBox("What is the required month")
dty = InputBox("What is the required year")
fd = InputBox("Which fund")
Range("I6").Select
ActiveCell.FormulaR1C1 = "=date(" & dty & "," & dtm & "," & dtd & ")"
Cells(6, 10).Value =
Application.WorksheetFunction.VLookup(Range("I6"),
Range("A1:E2000"), fd + 2, False)
End sub
152
Indian Institute of Quantitative Finance
Debugging
Debugging your subroutine / user defined
function in VBA
Stepping
Add watch
Quick watch
Toggling
Clearing
Indian Institute of Quantitative Finance
Debugging
Stepping

One simple check we could do to debug errors is execute the
code bit-by-bit and see what happens to the variables

By pressing F8, the code gets executed line-by-line

Alternatively, we can introduce a break line in the code (by
selecting the required line of code)

This is also done by Debug  Toggle Breakpoint

This would execute the macro until that point

From thereon, we could execute line-by-line using F8
154
Indian Institute of Quantitative Finance
Debugging
Add Watch

One other way to debug a set of code is by adding a Watch

This helps us to track value of certain variables in a
procedure as the code gets executed

This helps to check whether the variable is assuming values
rightly as desired

A watch is added by Debug  Add Watch

We specify the variable name and the procedure we would
like to track

It places a small watch window below the Module Window
155
Indian Institute of Quantitative Finance
Debugging
Add Watch
Specify
variable name
Specify
procedure /
module name
Watch gets
created here
156
Indian Institute of Quantitative Finance
Debugging
Quick Watch

Instead of defining a Watch manually, we could also create
Quick Watch

Here, we place the cursor in variable of choice in the code

We then click Debug  Quick Watch

This places a watch for that variable in a Watch window at
the bottom of the Module Window

Deleting a watch is done by selecting the relevant variable in
the watch window and clicking delete
157
Indian Institute of Quantitative Finance
Debugging
Locals Window

Locals window displays a Watch-like window for all
variables in the specific module the cursor is placed

This is set-up using View  Locals Window

We generally combine it with a Toggle Breakpoint and single
stepping to see whether value flow into variables are fine

The Locals window is also placed below the module window
158
Indian Institute of Quantitative Finance
Debugging
Immediate Window

Immediate Window helps to execute some section of the
code in the program to see what is the result

This could be either to find value of a variable or running a
user-defined function within a larger program

This is initiated by View  Immediate Window

To execute the code, we use:
? <variable name / function name>

The answer gets displayed in the next line

Not applicable for sub-routine as they do not return any value
159
Indian Institute of Quantitative Finance
Debugging
Error capturing

As we execute codes, we might have seen several errors with
error codes

If we are aware of possible error codes that our macro could
generate, we could capture them and assign action

Err.Number = <error number> is used to capture them

We could also include in our code: On Error Resume Next

This would help continue to run the code without breaking or
skipping the relevant line that throws an error

However, it makes debugging difficult
160
Indian Institute of Quantitative Finance
User Forms
Creating an User Form

We have so far obtained info from the user using Input Box

This helps to get simple inputs

If we have the need for gathering multiple inputs or need for
user to pick from several options, we use User Forms

User Form represents a custom-interface using standard
Windows control items

The look and feel of a user-form would be similar to a
standard Windows form
161
Indian Institute of Quantitative Finance
User Forms
Creating an User Form

A user form is brought into the Project: Insert  UserForm
162
Indian Institute of Quantitative Finance
User Forms
Tool Box

User Form is like a dummy template where we could place
various control elements

When a User Form is created, a Tool Box is also placed

We drag and drop inputs like Text Box and Check Box from
the Tool Box and place it on the form
163
Indian Institute of Quantitative Finance
User Forms
User Form Object

Like various Excel and VBA objects we have seen so far,
UserForm is also an Object

It has its own set of Properties, Events and Methods
164
Indian Institute of Quantitative Finance
User Forms
User Form Events

Running the User Form is similar to running a module

A double click of the User Form leads to the code window

We could have actions taken for various events
165
Indian Institute of Quantitative Finance
User Forms
User Form Events

Let’s try putting a simple code:
Private Sub UserForm_Click()
MsgBox “You have clicked User Form"
End Sub

Now, run the UserForm

Note that, by default, User Forms are Private in nature.

However, they can be made Public by removing the Private
Keyword
166
Indian Institute of Quantitative Finance
User Forms
Key Controls

Label
– Used for display of Text
– Properties: Caption, BackColor, ForeColor, Font

TextBox
– Allows entry of input at run-time
– Properties: BackColor, ForeColor, Font, MaxLength, TextAlign
– ControlSource : Links label to a cell in Excel. It can be used to feed
data into a particular cell and do computation based on that
– Enabled: It is ready to be used. Else control is non-selectable
– Locked: Can be selected, but cannot be edited
167
Indian Institute of Quantitative Finance
User Forms
Key Controls

ComboBox
– Used for selecting an item from a drop down
– Properties: BackColor, ForeColor, Font, MaxLength, TextAlign
– ControlSource :

Links Combo Box to a cell in Excel. If the cell has a value, it
would appear as default value when User form is run

When a new item is chosen from the drop down, that chosen
value gets assigned to the cell
– MatchRequired: This ensures the user can only select values from a
list and cannot enter any other value. Default is False.
– RowSource: Refers to the list elements that are to be included. Could
refer to a set of cells in Excel
Indian Institute of Quantitative Finance
168
User Forms
Key Controls

ListBox
– Used for selecting an item from a permanent set of displayed items
– Properties: BackColor, ForeColor, Font
– Control Source, RowSource
– BoundColumn: On the list box, we could display data on more than
one column. Bound column indicates the column whose value is fed
into the Control Source
– ColumnCount: Based on RowSource, indicates how many columns
get displayed in the List Box
– ColumnHead: If set to True, the row above the Row Source would
be treated as Column Headers
169
Indian Institute of Quantitative Finance
User Forms
Key Controls

CheckBox
– Used for checking/ unchecking an option
– Caption : Provides Caption for the Check Box
– ControlSource: Links true / false value to a cell in Excel based on
whether the box is checked / unchecked
– Enabled and Locked property similar to Text Box

OptionButton
– Also called Radio Button.
– Allows selection of one item from a group
– Once an item is selected, all other items gets unselected
– Caption, ControlSource, Enabled, Locked
170
Indian Institute of Quantitative Finance
User Forms
Key Controls

ToggleButton
– Similar to Check Box, but presented like a button
– Check / Uncheck like rise / sinking of a button
– Caption, ControlSource, Enabled, Checked

Frame
– It simply provides a frame within which other controls are placed
– First the frame needs to be placed followed by controls inside
– Especially, useful with Option buttons, where Frames help to group
such buttons
171
Indian Institute of Quantitative Finance
User Forms
Key Controls

CommandButton
– Most commonly used item. Commonly linked to a macro
– Caption, Enabled, Locked
– Default:

Setting this as True would keep focus on the button when a
UserForm is run.

A click of enter would thus execute code associated with
command button click
172
Indian Institute of Quantitative Finance
User Forms
Key Controls

Tab Strip
– Helps to place a holder of various tabs (all belonging to same form)
– Useful when different set of information is to be displayed on the
same control
– For instance, let’s say we want to display value of share under various
valuation methods or price of a product in different currency
– The data selected by the user needs to be captured through a code
– TabStrip1.SelectedItem.Index helps to capture the index number of
the selected tab (starting from zero)
– TabStrip1.SelectedItem.Caption helps to capture the name of the
selected tab
173
Indian Institute of Quantitative Finance
User Forms
Key Controls

MultiPage Control
– Helps to place a holder of various forms in different tabs
– Each Tab referred to as Page
– We could thus place different controls in different pages
– For instance, the same example as the previous one, with computation
of value per share across models.
– In cases, where we not only just show the final value, but also more
details of valuation models, the kind of controls needed may be
different. Here, a MultiPage control is more meaningful
– MultiPage1.SelectedItem.Index and
MultiPage1.SelectedItem.Caption are used to capture the selection
174
Indian Institute of Quantitative Finance
User Forms
Key Controls

ScrollBar
– Places a vertical scroll bar
– ControlSource: Helps link value selected to a particular Excel cell
– Maximum, Minimum: Specifies the maximum and minimum
number between which the scroll would come
– SmallChange: Specifies how much should the scroll value change to
a click on the arrows at both end
– By default, SmallChange carry the value 1. Minimum is 0 and
Maximum is 32767 (could be set beyond that value)
– Here, ControlSource will display the change in value only when the
focus shifts to other controls from the Scroll Bar
– ScrollBar1.Value can be used to capture the current selected level
Indian Institute of Quantitative Finance
175
User Forms
Key Controls

SpinButton
– Places a Spinner. Linked to another control like a text box
– ControlSource: Helps link value selected to a particular Excel cell
– Here, ControlSource will display the change in value only when the
focus shifts to other controls from the SpinButton
– SmallChange, Enabled, Locked
– Orientation: Indicates whether the Spin Button is arranged
horizontally or vertically
– SpinButton1.Value can be used to capture the current selected level
176
Indian Institute of Quantitative Finance
User Forms
Key Controls

Image
– Places a Image Control which could hold a picture
– Picture: Used to add a picture to the holder

RefEdit
– Enables user to specify a range that could be used for further use
– The user could type the range (like Text box) or select it on the Excel
s/sheet by clicking on the button on the right
– RefEdit1.Value could also be used to capture this value using a code
177
Indian Institute of Quantitative Finance
User Forms
Referring to controls in user form

If we need to check the nature of control in user forms we
use the function: TypeName(<control name>)

For instance, TypeName(TextBox1) would return TextBox

To refer all controls in userform: <userform nm>.controls

To refer to individual control elements, with in an user form,
we use a For…each loop
Dim ctrl as control
For each ctrl in userform1.controls
…..
Next ctrl
178
Indian Institute of Quantitative Finance
User Forms
Exit a macro

If you are running a macro and would like to exit it half way,
subject to certain conditions not being met, we use
Exit Sub

This would stop execution of the code it is currently running
179
Indian Institute of Quantitative Finance
User Forms
Exercise 1

We want to create a simple financial calculator where by
giving 4 of the five inputs of PV, FV, NPER, PMT and Rate,
we find the fifth element

Step1:Create the following form elements:
– A combo box that displays the five elements
– 5 text boxes to input the five elements
– A Label for displaying output
– A couple of Command Buttons - Compute and Clear
– Appropriate Labels for various text boxes, output and combo box
– Change the names of control elements for easy reference
180
Indian Institute of Quantitative Finance
User Forms
Exercise 1

Step2: Form initiation:
– On form initiation, we would like only the combo box to be enabled.
All text boxes, output label and command buttons would be disabled

Step3: On selection / click of combo box:
– We would capture the selection made by the user
– Depending on the selection, we would enable the relevant input text
boxes. So, 4 of the 5 boxes would be enabled
– Command buttons would also be enabled
– To ensure, we repeatedly work on the screen without having to close
the form and initiate every time, we put in a check first to ensure all
relevant elements are disabled and then suitable ones enabled
181
Indian Institute of Quantitative Finance
User Forms
Exercise 1

Step4: Functions for various calculation
– Independently create five functions to compute PV, FV, NPER, PMT
and Rate.
– Each function would carry the other 4 as inputs
– Excel function for the same could be used here for computation

Step 5: Validation check before computation
– Before computing, when user clicks on ‘Compute’ Button, we check
all enabled text boxes to see if they are non-empty and carry only
numbers
– If not we display suitable error message and exit the command button
macro
182
Indian Institute of Quantitative Finance
User Forms
Exercise 1

Step6: Computing the output:
– Post the validation, we enable the output label and display the
appropriate output, by calling the appropriate function
– The displayed number is rounded to 2 decimal places

Step7: Clearing the input fields:
– In order to clear the input data set, so that we could try out new set of
inputs and output, we clear all existing data
– We also clear the output value and disable the output label

Step 8: Comments and Indents
– Ensure the codes are suitably commented and indented
183
Indian Institute of Quantitative Finance
User Forms
Inserting a chart

The best way to understand the coding behind creation of a
chart is by recording a macro

We could place the chart in User Form through Image
Control

This is done using the code:
<image name>.picture = loadpicture(<picture variable>)

However, this can consider only images like .gif files and not
a chart object in Excel

Therefore, if we would like to use an Excel chart we need to
convert it into a picture
184
Indian Institute of Quantitative Finance
User Forms
Inserting a chart

This is done using the Export Method of charts
<Picture Variable> = ThisWorkbook.Path & "\<file name>.gif"
ActiveChart.Export Filename:=<Picture Variable>, FilterName:=“GIF”

ThisWorkbook.Path provides the path name where the
current workbook is saved.

The picture is also saved in the same folder

Export method carries 2 parameters – Filename &
FilterName
185
Indian Institute of Quantitative Finance
User Forms
Exercise 2

We are going to set-up a simulation exercise

Given holding period (in days) and Number of trial runs, we
would like to find maximum and minimum possible returns
over the holding period

We would also chart the path of returns across various trials

Input:
– Distribution of returns as normal
– Annual Average and Standard Deviation of returns
– Randomised Daily returns based on the distribution
– Indexed daily returns starting at 100
186
Indian Institute of Quantitative Finance
User Forms
Exercise 2

We start with recording of macro to capture the following

Step1: Populating the number of holding period days
– For a start, let’s assume holding period days be 100
– Populate column A from 1 to 100. Equate it as previous period value
+1

Step2: Populating the returns over this period
– For the first day’s return put in the formula
– For the remaining cells copy paste the previous cell’s value

Step3: Pasting the HP range
– From cell I6, we paste the HP range from 0 to 100
187
Indian Institute of Quantitative Finance
User Forms
Exercise 2

Step4: Pasting instances of returns of each trial
– We copy and paste as values path of returns
– We would paste each trial’s next to each other starting from cell I7
– In row 6, starting from I6, we put in trial number

Step5: Holding Period Return
– Compute Holding Period return in row 1 starting from cell J1

Step6: Computing output return
– Compute Max and Min possible returns in cells H1 and H2

Step7: Checking the macro
– Set things back and see whether the macro works fine
188
Indian Institute of Quantitative Finance
User Forms
Exercise 2

Step8: Tweaking the macro
– Once satisfied macro is working fine, we tweak the macro to bring
the needed dynamism
– Looping is done for pasting of HP days, computation of returns as
well as pasting of return path of each trial next to each other and
computing holding period returns

Step9: Creating the User Form
– We put in a user form to capture HP days and number of trials
– We display maximum and minimum returns as output
– We also put in two command buttons – Compute and Clear
189
Indian Institute of Quantitative Finance
User Forms
Exercise 2

Step10: Linking user form to macro
– We would link input on HP days and trials appropriately in the macro
– We also call the macro from the Compute Button click event
– Clear button link is used to clear all existing input/output data

Step11: A macro to record creation of charts
– We would record a new macro to present line chart of return path
across various trials
– We then tweak the macro for suitable chart range

Step12: Exporting the chart
– We then export the chart to the same folder and save it as a GIF
image
190
Indian Institute of Quantitative Finance
User Forms
Exercise 2

Step13: Putting the chart in UserForm
– We would place a Image Control in the user form
– Here, we would load the exported picture
– Since, Picture variable is declared in a different module compared to
user form, we need to declare the variable Public instead of Dim

Step14: Chart Button
– We would place a command button called Chart, which we would
link to the chart macro we have recorded

Step15: Clearing
– Finally, we would include clearing of chart too as part of the Clearing
button and associated action
191
Indian Institute of Quantitative Finance
Thank You
192
Indian Institute of Quantitative Finance
Descargar

Introduction to Financial Statistics