Overview
Excel VBA for Finance Course Overview
This course will teach you the fundamentals of Excel VBA and is perfect for finance professionals who are comfortable with Excel and want to expand their knowledge. The course starts with the basics by teaching you how to set up the VBA environment, when and why to use VBA, and how to build custom formulas. We will then use this foundation to progress to more advanced topics such as how to handle errors and exceptions, how to publish your functions and share them with your colleagues, set up events & automation, and work with arrays. VBA is a great tool to automate repetitive tasks on Excel and is also able to streamline long, complex calculations. This course uses a case study of a multinational company to create a custom function and explore various useful topics and concepts in VBA.
Excel VBA for Finance Learning Objectives
By the end of this course, you will be able to:- Know when and why to use VBA
- Create Custom Formulas in VBA to be used in Excel
- Identify coding errors, and manage errors in your data
- Publish your custom Functions and share them with your colleagues
- Create Events, and Automate Tasks
- Source large datasets
Who should take this course?
This Excel VBA for Finance course is perfect for professionals who have a solid understanding of excel and want to expand its usability. This course is designed to equip anyone who desires to begin a career in investment banking, sales, and trading, treasury, or other areas of finance with a fundamental knowledge of Excel. This course is also great for aspiring Business Analysts. Although VBA functions quite similarly in Mac and Windows, some features will perform differently, and we recommend students use Windows to complete the training.
Get Recognized With CFI
See All Certification ProgramsWhat you'll learn
Lesson
Multimedia
Exams
Files
Custom Formulas
Session Objectives
Free Preview
How to Open VBA
Free Preview
A Tour of VBA
Free Preview
Create a Module
Enable Macros Notification
A Formula in Excel
A Basic Function
Making a Variable Dynamic
Application.Volatile
Future Proofing Our Code
Comments
Making Our Output Currency Dynamic
Chapter Review
Custom Formulas Exercise 2
Custom Formulas Exercise 2 Review
Custom Formulas Exercise 3
Custom Formulas Exercise 1
Custom Formulas Exercise 1 Review
Custom Formulas Advanced Examples
Errors and Exceptions
Session Objectives
Using Breakpoints
Tracking Down an Error
Option Explicit & Types of Variables
Testing Option Explicit
Variable Types for Arguments
Code Order
Other Ways to Track Values
Trapping an Exception with On Error Resume Next
Exit Function & Colons
Chapter Review
Errors and Exceptions Exercise 1
Recap of New Techniques
Errors and Exceptions Exercise 1 Review
Errors and Exceptions Exercise 2 Review
Errors and Exceptions Exercise 2
Finalize and Publish Functions
Session Objectives
Currency Recap
Catching an Exception By Using an Indented IF
Setting Up Our Code to Handle Multiple Output Currencies
Output In Any Currency
Review the Final Function
Saving Our Function as an Add-in
Installing an Add-in
Preventing Add-in Code Conflicts
Download a Colleague's File
Events & Automations
Session Objectives
Why Use a Central Assumptions File
Create a Message Box Routine
Call the MSGBOX Each Time the Workbook Opens
Creating the Central Assumptions File
Setting Up A Code Framework
Pasting Data Back to the Workbook
Testing Our Code
How to Reference Another Workbook (Excel)
Creating a VBA Reference to Another Workbook
Using a VBA Reference to GET Value from Another File
Common Errors When Referencing Workbooks
Catching an Exception with an IF Statement
Ensuring the Update Happens Every Time the File Is Opened
For Each Loop
Adapting Our Loop
Chapter Review
Events and Automation Exercise 2
Events and Automation Exercise 2 Review
Events and Automation Exercise 1
Best Practice Tips
Events and Automation Exercise 1 Review
Working with Arrays
Sessions Objectives
Setting Up Our Project
Working With Multiple Workbooks
Testing with STOP
Selecting a Sheet in Another Workbook
Assigning Values to an Array
Testing an Array
Gathering Data and Returning to ThisWorkbook
Methods
Resize a Table
Populating a Table of Data
Loop Through Rows
Success or Failure Message
Using our Array of Data in our Model
COUNT - Code Quality Rating
Working with Arrays Exercise 1 Review
Working with Arrays Exercise 2
Working with Arrays Exercise 1
Working With Arrays Exercise 2 Review
Qualified Assessment
This Course is Part of the Following Programs
Why stop here? Expand your skills and show your expertise with the professional certifications, specializations, and CPE credits you’re already on your way to earning.
Financial Modeling & Valuation Analyst (FMVA)® Certification
Excel VBA for Finance is part of the Financial Modeling & Valuation Analyst (FMVA)® certification, which includes 52 courses.
- Skills Learned Financial modeling and valuation, sensitivity analysis, strategy
- Career Prep Investment banking and equity research, FP&A, corporate development
Data Analysis in Excel Specialization
- Skills Learned Data Modelling & Analysis, Data Transformation, Data Visualization
- Career Prep Data Analyst, Business Intelligence Specialist, Data Scientist, Finance Analyst
Learner Reviews
Excellent foundation to Excel VBA coding!
Excellent foundation to Excel VBA coding! The instructor was very clear in delivering the lessons.I even liked how interactive he was in the 'Handling Errors' section. I also appreciate the summaries.Loved absolutely everything about this course!
Excellent foundation to Excel VBA coding! The instructor was very clear in delivering the lessons.I even liked how interactive he was in the 'Handling Errors' section. I also appreciate the summaries.Loved absolutely everything about this course!
Kethlois Meikle,FMVA
Fascinating VBA course
Completing this course was way beyond my expectation. I particularly appreciated that the tutor provided high-level explanations and incredibly insightful comments all the time when guiding me through the core material.
Completing this course was way beyond my expectation. I particularly appreciated that the tutor provided high-level explanations and incredibly insightful comments all the time when guiding me through the core material.
Kornél Peszt
A bit complex but explains a lot
Good course, I would say it explains more than basics, which can be a bit complex for the beginners. Anyway, I would add downloadable text/transcripts/notes based on what instructions is saying, some exercises after each section as well as a full presentation.
Good course, I would say it explains more than basics, which can be a bit complex for the beginners. Anyway, I would add downloadable text/transcripts/notes based on what instructions is saying, some exercises after each section as well as a full presentation.
Erikas Daugirdas
Professional approach on VBA Finance
VBA for Finance course has a very professional approach on this topic, giving the clarity on the logic of the coding and helping you understand what is behind the scene when writing the code along with an excellent example
VBA for Finance course has a very professional approach on this topic, giving the clarity on the logic of the coding and helping you understand what is behind the scene when writing the code along with an excellent example
