Course Level: Advanced
Description:
This advanced course builds upon the foundational skills introduced in Excel Macros and VBA – Level I and takes users deeper into the logic and power of Visual Basic for Applications (VBA). Participants will strengthen their understanding of variables, loops, and conditional structures while learning how to work efficiently with arrays and user-defined functions.
Through hands-on exercises, participants will design custom dialog forms and incorporate a variety of ActiveX controls to create interactive, user-friendly Excel applications. In addition, this course explores structured error handling, debugging techniques, and best practices for writing reliable, maintainable VBA code.
By the end of the course, attendees will be able to build customized, professional-grade automation tools that enhance productivity and deliver consistent results across workbooks and teams.
This one-day advanced Excel VBA training runs from 8:30 a.m. to 4:15 p.m. with an instructor and picks up where Level 1 ends. You work with array variables, variable scope and data types, then control program logic with For Each…Next and nested loops, If…Then and Select Case. The class shows you how to build user-defined functions that you can use directly in Excel formulas, including returning values and handling parameters.
You also practice debugging in Break Mode with the Immediate, Locals and Watch windows, and write structured error-handling routines with On Error GoTo and Resume. In the user interface module you create custom UserForms, add ActiveX controls such as buttons, list boxes and combo boxes, and write event-driven procedures that respond to users. This VBA course suits people who have completed Excel Macros and VBA Level 1 or already write basic VBA procedures.
The course fee is $345 for one day of instructor-led group training. Organizations can also book these Excel VBA classes as private training for a team, and the form on this page lets you ask about upcoming dates or a group session.
Write more sophisticated VBA procedures using variables, arrays, and logic structures.
Create custom Visual Basic functions for use in both spreadsheet formulas and subroutines.
Develop user-friendly dialog boxes with form controls and ActiveX objects.
Implement structured error handling and debugging strategies to ensure program stability.
Apply professional coding practices for efficient, maintainable macro solutions.
Highlights:
Review of variables and constants
Declaring and managing array variables for data storage and manipulation
Understanding scope and lifetime of variables (local, module, and global)
Working with data types for efficiency and clarity
Outcome: Gain confidence in designing procedures that manage complex data efficiently.
Advanced looping commands (For Each…Next, nested loops)
Conditional branching structures (If…Then, Select Case)
Combining loops and conditions to automate decision-making
Outcome: Write flexible code that adapts to changing data and user inputs.
Understanding the difference between Sub procedures and Functions
Building custom VBA functions for use directly in Excel formulas
Returning values and handling parameters
Best practices for naming and documentation
Outcome: Extend Excel’s capabilities by creating your own formulas and reusable code modules.
Entering Break Mode and controlling program execution
Using the Immediate Window for testing and troubleshooting
Monitoring code behavior with the Locals Window and Watch Window
Interpreting error messages and refining procedures
Outcome: Identify and correct logic errors quickly using professional debugging tools.
Designing structured error-handling routines (On Error GoTo, Resume, etc.)
Preventing runtime errors through validation
Logging and communicating errors to users
Writing resilient code for real-world environments
Outcome: Create robust programs that handle unexpected conditions gracefully.
Creating custom dialog forms (UserForms) in VBA
Adding and configuring ActiveX controls (buttons, list boxes, combo boxes, etc.)
Writing event-driven procedures for responsive interaction
Designing intuitive, user-friendly automation tools
Outcome: Develop polished, interactive solutions that simplify complex workflows.
Combining multiple procedures into larger workflows
Organizing projects with multiple modules and forms
Best practices for maintaining, documenting, and distributing macro solutions
Outcome: Deliver professional-grade Excel applications that enhance team productivity and consistency.
Cancellation Policy: 5 working days for full refund. Cancellations after that time are charged full tuition for the course.
For more information regarding refund, concerns, and/or program cancellation policies please contact our offices at 919-878-7100 ext. 22
