Excel Visual Basic (VBA)
Introduction to VBA
- Overview of Visual Basic Editor
- Recording Macros
- Recording with Relative References
- Writing and Editing simple code
- Assigning your code to buttons
- Creating Keyboard Shortcuts
- Using the VBA Object Browser
Financial Application: Calculate asset returns quickly by recording and running a macro.
Procedures and Functions
- Create a User-Defined Function (UDF)
- Working with Modules
- Writing and Editing Functions and Subroutines
- Declaring Variables
- Variable Types
- Utilizing Excel functions inside VBA code
- Debugging VBA
Financial Application: Create a function to solve for Required Return using CAPM.
- Repeat a series of actions by using loops such as For…Each…Next
- Recursive syntax within code
- Looping through Collections of Objects
Financial Application: Build a Payback Period Calculator using loops.
Working with Large Datasets
Financial Application: Use looping to perform a series of calculations quickly on a collection of worksheets in an Excel file. Example: given closing prices for 30 stocks, determine the daily beta for each stock over a period of several years. Later, summarize your findings by determining the portfolio’s daily beta given varying weights on each stock.
- Boolean Expressions
- IF/ELSEIF syntax
- CASE/SELECT CASE syntax
- Discrete vs. continuous boundaries within logic
- Preventing potential errors and building more robust code
Financial Application: Given fundamental ratios, use logic in VBA to assess and output an associated credit rating.
Input Boxes and Messages Boxes
- Obtain information from the user within a subprocedure
- Incorporate multiple input boxes and assign inputs to variables
- Output information to users through message boxes
Financial Application: Use input and message boxes to calculate the monthly payment on a loan in the amount of the user’s choosing. Later, practice breaking our earlier code for calculating monthly payments. Build logical conditions to develop workarounds to prevent these errors from occurring.
Userforms and Controls
- Design a userform interface
- Work with the toolbox
- Add controls to the form
- Use logic to run appropriate subprocedures in different cases
Financial Application: Give users a choice of different datasets to import, parse, and scrub. Use forms, check boxes, buttons, subprocedures and logic to design and build a working stand-alone user-form.
Querying data and using SQL in VBA
- Efficiently work with large datasets to quickly isolate the relevant data
- Use SQL code inside VBA to perform queries and output specific datasets
Financial Application: Given a large dataset consisting of fundamental ratios for a variety of companies across industries, we will use SQL inside VBA to build queries to isolate a subset of the fundamental ratios for a particular industry. We can also use VBA to compile this data onto one worksheet per industry and calculate the high, low and average values for these fundamental ratios within each industry.
More Investment Banking VBA Applications
These examples and others may be included throughout the program for additional practice:
- Build an amortization table quickly in a message box or worksheet
- Develop a UDF designed to forecast EPS accretion/dilution in a merger
- Create football field charts quickly using VBA (to display valuation ranges)