VBA FOR FINANCIAL
MODELLING
Study Guide
AMARDEEP SINGH
[email protected]
VBA Study Guide
Contents
Introduction to VBA Macros ........................................................................................ 2
Recording Macros and Understanding the Code .......................................................... 2
Modules and Macro Security ...................................................................................... 4
VBA Procedures......................................................................................................... 4
Running Sub Procedure.............................................................................................. 5
Variable Declaration .................................................................................................. 6
MsgBox and InputBox ................................................................................................. 6
Format Function ........................................................................................................ 7
UCase and LCase ...................................................................................................... 7
Application.WorksheetFunction ................................................................................. 7
Loops Introduction .................................................................................................... 7
For Loop ................................................................................................................... 8
Do Until Loop ............................................................................................................ 8
Conditional Statements ............................................................................................. 8
Error Handling ........................................................................................................... 9
Conclusion and Practice Tips ................................................................................... 10
1
VBA Study Guide
Introduction to VBA Macros
What is VBA?
VBA (Visual Basic for Applications) is a programming language integrated into MS Excel that
allows automation of repetitive tasks and creation of custom functions, user-defined tools,
and interactive applications.
Opening the VBA Editor
Press Alt + F11 or go to the Developer tab > Visual Basic.
Why use VBA in Financial Modelling?
VBA is especially useful in financial modelling where repetitive tasks, data analysis, and
dynamic calculations are common. VBA helps to:
• Automate repetitive tasks (e.g., formatting, data importing)
• Build custom calculations or reports
• Improve speed and reduce manual errors
Recording Macros and Understanding the Code
Macro Recorder Overview
Excel’s Macro Recorder helps generate VBA code based on your actions. It shows how
tasks are translated into code.
2
VBA Study Guide
How to Record a Macro?
1. Go to Developer Tab → Record Macro
2. Give a name, shortcut key (optional), and store location.
3. Perform your actions.
4. Click Stop Recording
Rules for Macro Names
- Mix of alphabets and numbers is allowed but number cannot be the first character
- Cannot contain spaces
- Cannot contain special characters except underscore
- Must be unique (cannot repeat)
Storage Location
- This Workbook: Available only in the current file.
- New Workbook: Opens a new Excel file with macro.
- Personal Macro Workbook: A hidden workbook that loads each time Excel opens (ideal
for macros you want to use everywhere).
Understanding Recorded Code
Sub FormatHeaders()
Range("A1:C1").Font.Bold = True
Range("A1:C1").Interior.Color = RGB(200, 200, 255)
End Sub
• Sub begins a macro.
• Actions are translated into VBA commands.
• Recorded macros may be inefficient; always refine them.
3
VBA Study Guide
Modules and Macro Security
What is Module?
A Module in VBA is where your procedures (macros or functions) are written.
To insert one: Open the VBA Editor, then Insert > Module.
Macro Security Settings
Excel disables macros by default.
To enable: Go to Developer → Macro Security → Macro Settings
Recommended option: 'Disable all macros with notification' to manually choose which
macros to trust.
VBA Procedures
4
VBA Study Guide
Sub Procedure
A Sub Procedure is a block of code that performs an action like formatting cells, copying
data, etc.
Sub GreetUser()
MsgBox "Welcome to VBA Modelling!"
End Sub
Function Procedure
A Function Procedure is similar but it returns a value and can be used directly in Excel like
other built-in functions.
Function AddTwoNumbers(a As Double, b As Double) As Double
AddTwoNumbers = a + b
End Function
Running Sub Procedure
How to run?
There are multiple ways to execute code written in sub procedure:
- Run code from VB Editor
- Run code from Developer tab Macros option
- Using shortcut key assigned to macro
- Assign macro to a shape
Recommended option: While there are many methods to run a sub procedure, assigning
macro to a shape is the recommended option from ease-of-use perspective.
5
VBA Study Guide
Variable Declaration
Variables store information for use in your code.
Syntax
Dim variableName As DataType
Common Data Types:
Data Type Used for
Integer Whole numbers (-32,768 to 32,767)
Long Large integers
Double Decimal numbers
String Text values
Boolean True/False
Date Date values
For example,
Dim age As Integer
Dim salary as Long
Dim totalAmount As Double, discountPercent As Double
Dim userName As String
Dim isActive As Boolean
Dim QuarterEndDate As Date
MsgBox and InputBox
Use MsgBox to display messages, and InputBox to receive input from users.
Dim userInput As String
userInput = InputBox("Enter your name:")
MsgBox "Hello " & userInput
6
VBA Study Guide
Format Function
Format() helps display numbers, dates, text, etc. in a user-friendly way.
Dim formattedDate As String
formattedDate = Format(Date, "dd-mmm-yyyy")
MsgBox formattedDate
Format(12345.678, "#,##0.00") 'Output: 12,345.68
UCase and LCase
These functions convert text to uppercase or lowercase.
Dim txt As String
txt = "finance"
MsgBox UCase(txt) 'Output: FINANCE
MsgBox LCase("MODEL") 'Output: model
Application.WorksheetFunction
This allows the use of Excel functions in VBA.
Dim EmpName As String, result As String
EmpName = InputBox("Enter name of employee") 'e.g. AMAR DEEP
result = Application.WorksheetFunction.Proper(EmpName)
MsgBox result 'Output: Amar Deep
Loops Introduction
Loops repeat tasks automatically, saving time and reducing errors. It involves writing the
logic to repeat a command or a series of commands multiple times or based on a
condition.
7
VBA Study Guide
For Loop
Used for repeating actions a specific number of times.
Syntax
For i = 1 To Number_of_Times
' your code
Next i
Dim i As Integer
For i = 1 To 5
Cells(i, 1).Value = "Row " & i
Next i
Do Until Loop
Executes as long as the condition is FALSE.
Syntax
Do Until condition
' your code
Loop
Conditional Statements
If – Then – ElseIf – Else
Use to make decisions based on conditions.
Syntax
If condition Then
'action
ElseIf anotherCondition Then
'alternative
Else
'fallback
End If
8
VBA Study Guide
Dim marks As Integer
marks = InputBox("Enter your score")
If marks >= 90 Then
MsgBox "Grade A"
ElseIf marks >= 75 Then
MsgBox "Grade B"
Else
MsgBox "Needs Improvement"
End If
You may also write conditional statement in one single line as below:
Sub EvenOrOdd()
Dim num As Integer
num = InputBox("Enter a number")
If num Mod 2 = 0 Then MsgBox "Even" Else MsgBox "Odd"
End Sub
Error Handling
To prevent crashes and handle exceptions gracefully.
Sub SafeDivide()
On Error GoTo ErrHandler
Dim a As Double, b As Double
a = InputBox("Enter numerator")
b = InputBox("Enter denominator")
MsgBox a / b
Exit Sub
ErrHandler:
MsgBox "Incorrect input"
End Sub
Use On Error Resume Next to skip errors but use with caution.
9
VBA Study Guide
Conclusion and Practice Tips
VBA is a powerful tool for automating Excel tasks, and with regular practice, you can create
dynamic and efficient models. As you start applying these concepts, here are a few tips
and summary points to keep in mind:
Practice Tips
• Always plan what your macro should do before writing the code.
• Start with simple macros and build your confidence gradually.
• Use the Macro Recorder to understand syntax but clean up unnecessary lines
afterward.
• Indent your code properly for better readability.
• Name your macros and variables meaningfully.
Key Summary Points
• VBA allows automation, custom logic, and flexibility within Excel.
• Macros can be recorded or written from scratch in the VBA Editor.
• Sub procedures perform tasks, while Function procedures return values.
• Declaring variables helps manage data effectively and improve code efficiency.
• InputBox and MsgBox are useful for interacting with users.
• Loops allow repetitive tasks to be handled with minimal code.
• Conditional logic and error handling make your macros robust.
• Application.WorksheetFunction expands your VBA capabilities using Excel’s built-in
formulas.
• Modules help organize and store your VBA code.
Continue exploring and modifying VBA code to suit your modelling needs – experience is
the best teacher!
10
