Macros & User defined function
with VBA
Why Macros / Functions?
Automating a task performed frequently
Automating repetitive operations
Creating customized functions
Simplified front end
Help other users
Complicated sequence of calculations can be
replaced by function entries
Macros
Recording Macros
To start the macro recorder, choose
ViewMacroRecord New Macro. The Record
Macro dialog box presents several options:
Macro Name: The name of the macro.
Shortcut Key: You can specify a key combination
that executes the macro. The key combination
always uses the Ctrl key.
Store Macro In: The location for the macro.
Description: A description of the macro. By
default, Excel inserts the date and your name. You
can add more information if you like.
Recording a macro: Example 1
Record a macro that changes the formatting for the
current range selection. The macro makes the selected
range use Arial 16-point type, boldface, and the color
red.
Select the cell that contains the value or text
Select ViewMacroRecord New Macro
Enter a new name for the macro
Assign this macro to the shortcut key Ctrl+Shift+F by entering
Shift+F in the edit box labeled Shortcut Key
Click OK. This closes the Record Macro dialog box.
Select FormatCells and then click the Font tab. Choose Arial
font, Bold, and 16-point type, and make the color red. Click OK
to close the Format Cells dialog box.
The macro is finished, so click the Stop Recording button on
the Stop Recording toolbar (ViewMacroStop Recording).
Recording a macro: Example 2
Converting formulas into values is usually a
two-step process in Excel:
Copy the range to the Clipboard.
Choose Edit -> Paste Special (with the Values
option selected) to paste the values over the
formulas.
User defined Functions
Structure of a function
Where to define functions:
Alt + F11
Within the VBA editor, select Insert | Module from the
menu
Has three elements
Header line with name of function and list of parameters
Closing line
Program lines (instructions) between header and closing
line
Ex:
Function myFunction(p)
End Function
Ex: A simple function
Finding the square:
Function mySqr(p)
mySqr = p * p
End Function
Find difference between two numbers:
Function myDiff(p1,p2)
myDiff = p1 p2
End Function
Conditional Execution Simple IF
If then else
Structure
If <condition> Then <If True> Else <If False>
Ex: If t-stat is greater than 1.96 return 1 else
return 0
Function myTstat(p)
If p > 1.96 then myTstat = 1 else myTstat = 0
End Function
Conditional Execution - Block IF
If more than one statement is to be
conditionally executed
Structure
If <condition> Then <Instruction>
ElseIf <condition> Then <Instruction>
[Any number of ElseIfs ]
Else <Instruction>
End If
Ex:
Deposit
Interest Rate
< 10,000
5%
> 10,000 & < 50,000
5.5%
>50,000 & < 1,00,000
6%
> 1,00,000
6.5%
Conditional Execution Block IF
Function myRates(deposit)
If deposit < 10000 Then
myRates = 0.05
ElseIf (deposit >= 10000) And (deposit < 50000) Then
myRates = 0.055
ElseIf (deposit >= 50000) And (deposit < 100000) Then
myRates = 0.06
Else
myRates = 0.065
End If
End Function
Conditional Execution Select Case
Execute a statement from a set of statements based on value
of an expression
Structure
Function mySelect(p)
Select Case p
Case 1
<statement>
Case 2
<statement>
Case Else
<statement>
End Select
End Function
Conditional Select Select Case
Ex:
Function myselect(p)
Select Case p
Case Is < 10000
myselect = 0.05
Case Is < 50000
myselect = 0.055
Case Is < 100000
myselect = 0.06
Case Else
myselect = 0.065
End Select
End Function
Excel functions in VBA
Compute the price of a call option using Black-Scholes
options pricing formulae. The inputs to the model
Stock Price 100
Strike Price ATM
Volatility 20%
Risk Free rate 5%
Time to Maturity 6 Months
Function BS(s, k, v, r, t)
d1 = (Application.WorksheetFunction.Ln(s / k) + (r + (v ^ 2) / 2) * t) / (v *
Sqr(t))
d2 = d1 - (v * Sqr(t))
BS = (Application.WorksheetFunction.NormSDist(d1) * s) (Application.WorksheetFunction.NormSDist(d2) * k * Exp(-r * t))
End Function
Loops For Next
Repeats a set of statements for a defined
number of times
Compute Factorial
Function myFactorial(p)
j=1
For i = 1 To p Step 1
j=j*i
Next i
myFactorial = j
End Function
Calling User-defined functions
Compute factorial using recursive function call
Function myFactorial2(p)
If p = 0 Then
myFactorial2 = 1
Else
myFactorial2 = myFactorial2(p - 1) * p
End If
End Function
Variables
Variables are used to store and manipulate data
Defining a variable
Dim <variable name> As <variable type>
Ex: Dim Temp As Integer
Commonly used variable type
Variable
Type
Range
Boolean
True / False
Integer
-32,768 to 32,767
Long (Integer) -2,147,483,648 to 2,147,483,647
Double
(Floating)
-1.798 E 308 to -4.941 E -324 (-ve)
4.941 E -324 to 1.798 E 308 (+ve)
Date
Jan 1, 100 to December 31, 9999
String
Text
Arrays
Compute
PV of a set of cash flows.
Inputs: cash-flows and discount rate
Function pvarray(myCF As Variant, n As Double)
Dim rws As Integer, cls As Integer, i As Integer, j As Integer, temp As
Double
Dim tempCF As Variant
tempCF = myCF
rws = UBound(tempCF, 1)
cls = UBound(tempCF, 2)
temp = 0
For i = 1 To cls
For j = 1 To rws
temp = temp + (tempCF(j, i) / (1 + n) ^ j)
Next j
Next i
pvarray = temp
End Function
Backup
Conditional Execution Nested IF
Function myRates2(deposit)
If (deposit < 10000) Then
myRates2 = 0.05
Else
If (deposit < 50000) Then
myRates2 = 0.055
Else
If (deposit < 100000) Then
myRates2 = 0.06
Else
myRates2 = 0.065
End If
End If
End If
End Function
Loops Do While
Executes statement while the condition is true
Compute factorial
Function myFactorial2(p)
If p < 2 Then
myFactorial2 = 1
Else
i=1
j=1
Do While i <= p
j=j*i
i=i+1
Loop
myFactorial2 = j
End If
End Function
Loops Do Until
Executes statement until the condition is met
Compute factorial
Function myFactorial3(p)
If p < 2 Then
myFactorial2 = 1
Else
i=1
j=1
Do Until i > p
j=j*i
i=i+1
Loop
myFactorial2 = j
End If
End Function
Arrays
A group of variables sharing the same name & type and
referenced by an index
Declaring an array
Dim myArray(5)
Array index starts from 0
Declaring an array with index boundaries
Dim myArray(2 To 5)
Identifying the bounds of an array
LBound(myArray)
UBound(myArray)
Multi-dimension array
Dim myArray(5,5)
Dim myArray(1 To 5, 1 to 5)
