Make sure this course is right for you.
Get unbiased reviews and personalized recommendations.
About the course
This course is for anyone who wants to learn Visual Basic for Applications (VBA). Typically, participants have advanced knowledge of Microsoft Excel to be ready for programming in VBA. VBA is often used when a series of actions is repeatedly performed. By coding your sequence of actions in VBA, you can automate this process, saving lots of time. The course does not require any prior programming experience. We begin by using Microsoft Excel’s macro recorder and then learn to interpret and edit the underlying code. Participants will then learn how to work within modules to build macros (subprocedures) and user-defined functions. Programming topics that will be covered include: declaring variables, looping, debugging, creating input and message boxes, using logic in your code, working with controls and building userforms. This course is hands-on and participants are expected to follow along with the instruction. Financial applications will be used throughout the course to demonstrate the programming concepts.
By the end of the course, the participants will be able to:
- Record and edit VBA macros (subprocedures)
- Create user-defined functions (UDFs)
- Declare and work with different types of variables
- Incorporate existing Excel functions inside the VBA code
- Understand how to debug your code
- Use looping to efficiently repeat certain actions
- Incorporate logic in your code
- Extract and user inputs through input and message boxes
- Work with controls and build userforms
Session #1 (3 hours) Introduction to VBA: Recording Macros, Working with the Visual Basic Editor, Creating User-Defined Functions, and Creating Loops
Introduction to VBA Gain an introduction to VBA and learn how to record macros. Familiarize yourself with the VBA Editor.
- Get an overview of the Visual Basic Editor and macro settings
- Understand the VBA IDE environment
- Learn how the Visual Basic Editor displays reserved words, comments, and uses Intellisense
- Record a macro
- Record macros using relative references
- Understand how the recorder works and how it converts actions to code
- Run macros by assigning them to a button
- Build your own custom keyboard shortcuts through VBA
Procedures, User-Defined Functions, and Looping Learn how to work with modules and write your own code. Build your own functions. Understand the need for variables and learn how to create powerful loops.
- Work with modules
- Create, edit and refine code
- Add comments to your code and learn best practices
- Build user-defined functions
- Declare variables and understand variable types
- Use Excel functions inside your VBA
- Write loops to repeat actions
- Learn to debug your code
Session #2 (3 hours) Introduction to VBA: Practical Uses of VBA for Large Datasets, Building Logic, Working with Input from the user, and Creating Userforms Practical uses of VBA
Put your VBA knowledge to practical use by building code that will perform analytics on many worksheets quickly using loops.
- Combine recorded macros with looping to analyze large datasets quickly
- Look at the control-of-flow structures
- Use the for each … next structure to work with collections of objects
- Gather and output information via input and message boxes
Building Logic into your Code Make your code more robust by debugging and learning programming best practices. Build logic into your code.
- Prepare for and build workarounds in your code
- Build Boolean expressions and use If … Elseif … End If decision structures
- Learn the Case … Select Case … End Select method
Working with Forms and Controls Build interfaces to interact with users in an efficient, yet complex manner.
- Build userforms in VBA
- Work with controls
- Master the toolbox
- Make your subprocedures private or public
- Set userform properties, events, and methods
Cara Marshall, Ph.D. is the Director of the Risk Management Graduate Program at Queens College, within the City University of New York. She has been a professor of finance and risk management at Queens College since 2004. Dr. Marshall is also a highly regarded consultant who has trained employee...
Videos and materials
Because of COVID-19, many providers are cancelling or postponing in-person programs or providing online participation options.
We are happy to help you find a suitable online alternative.