Sign In

A Member of the CFA Institute Global Network of Societies

​​​​

 Introduction to Visual Basic for Applications in Excel

Course Goals & Overview:

Excel is well-known as a very powerful spreadsheet application, but its full potential can arguably only be unlocked using VBA, or the hidden code running behind the scenes. Start with an overview of the basics, such as Excel's object model, data types and variables. Write your own functions and subroutines while learning when to use one or the other, complete with five cumulative case study examples simulating actual use-cases in data validation and automation. While no background in other programming languages is required, it can definitely help during this intensive VBA primer. Those starting from scratch may even end up building the foundation for further coding skills both in Excel (or other Microsoft Office software) and in other applications, such as data structures, loops, and other best practices which are universal.

Learning Objectives:

•   Learn the fundamentals of VBA in Excel, including the object model, data structures, methods, and properties
•   Begin using the Macro Recorder, before progressing to the VBA Editor and getting familiar with its layout/functions
•   Explore the various elements of the VBA language, such as variables, procedures, VBA functions, and operators
•   Prepare macros with proper error handling and diligent debugging components to accommodate user error
•   Build visually pleasing user forms, following best practices in user interface design to add/manipulate data seamlessly

Course Sections – Part I:

•   Overview of the Excel object model, including the definitions of its various elements
•   Use proper syntax to reference objects, call their methods, and access its properties​
•   Forgo the Macro Recorder for the full-featured VBA Editor, becoming acquainted with its various panels/windows
•   Study VBA's wide variety of supported data types (e.g., strings, doubles, Booleans) and how to select the correct one
•   Declare, set, and reset  variable, arrays, collections, and more
•   Use Option Explicit to ensure strict control over variable declaration and sloppy execution
•   Annotate code with comments for increased program legibility and to help other contributing developers
•   Write functions and subroutines and learn the differences between the two as well as the best situations for both
•   Utilize Excel's built-in functions in VBA code using the Application keyword to avoid reinventing the wheel
•   Take advantage of the overlaps between VBA functions and Excel functions, especially for text manipulation
•   Understand VBA control statements, such as conditionals and the assortment of loop types
•   Create nested loops to iterate through two-dimensional arrays or ranges of rows/columns
•   Establish variable scope to ensure proper program flow and permission logic

Course Sections – Part II:

•   Identify the various ways a program can yield compile (syntax) errors
•   Handle run-time errors (exceptions) and learn to correct them beforehand based on the accompanying error code
•   Implement error handling techniques with On Error, GoTo, and Resume statements
•   Debug proactively throughout the development process via message boxes and breakpoints
•   Take a more passive approach to debugging during development via the Locals and Immediate views
•   Use Step Into, Step Over, and Step Out to execute specific lines or procedures precisely to narrow down bugs
•   Design message boxes with or without buttons, each complete with their respective subroutines
•   Prompt the user for information using input boxes that accept either inputs (e.g.,  text, numbers) or a cell range
•   Construct user forms by selecting the appropriate toolbox controls (e.g., TextBox, Label, Frame, buttons)
•   Manage control properties in the VBA Editor to handle both appearance (size/color) and functionality
•   Develop best practices underlying effective forms, utilizing event-specific and control-specific code prudently
 

**IMPORTANT - PLEASE NOTE**

To maximize the educational value of these programs, we strongly recommend that you have an intermediate understanding of Excel. Lack of basic Excel skills will impede your ability to effectively acquire and implement the techniques and shortcuts that are presented in this program.  Our courses are extremely interactive, hands-on with intensive focus on Excel shortcuts and efficiency.

Bring a PC laptop with Microsoft Excel installed, and a working USB port (in case our email containing in-class materials gets lost in your junk/spam folder, we can distribute them via flash drive). If you can only bring a Mac, please avoid Office 2008 and ideally set up a Windows environment via Boot Camp, Parallels, or VMware.

 

 Registration Fees

​CFAW Members: $899.00

Non-Members: $999.00

Currency: U.S. Dollar ($)

 Dates and Location

Registration coming soon.

 Discounts & Refunds

Group Discount: Organizations are eligible for a 10% discount when registering three or more people for an event. All registrations must be received at the same time. Email all discount registrations together to events@cfawashington.org

 
Cancellations, Substitution, and Refund Requests

 
  • Substitutions are welcome at any time.
  • Cancelations must be made in writing and emailed to events@cfawashington.org   on or before 5 business days prior to the event.
  • No refunds will be processed after 5 pm ET, 5 business days prior to the event. 
  • A $25 administrative fee will be applied to all cancellations.​
  • Refunds will not be issued for no shows