Excel Advanced VBA Macros

AdvancedFull Day1 DayVersion 365Official CPE Provider

Take your Excel automation to a professional level. Program with the Excel object model, add loops, decision logic, and error handling, build custom functions, and design UserForms that turn spreadsheets into real applications.

Course Objective

This is the follow-up class to Introduction to VBA Macros. In this class we continue using VBA to automate Excel tasks. Explore more fundamentals of Excel VBA including effective decision making, using worksheet events, debugging, and error handling.

Prerequisites

Due to the technical content, Introduction to VBA Macros is required prior to enrolling in this course. Before taking this course, you should be comfortable recording macros, finding and editing macros as well as writing basic VBA code. You should also be aware that our VBA classes are somewhat technical in nature since they involve programming. If you have any questions, feel free to contact us to discuss if this course is right for you.

Course Outline

9 lessons · 37 topics

1More Fundamentals of Excel VBA

Review The Visual Basic Editor
Review VBA Naming Conventions and Coding Best Practices
Understand Variable Declarations
Organize Code for Easier Editing and Review

2Excel Objects

Review The Excel Object Hierarchy
Use Application, Workbook, Worksheet, And Range Objects
Work With Object Methods, Events, And Properties
Reference Workbook and Worksheet Objects Efficiently

3Variables And Constants

Review Common VBA Data Types Used in Variables
Understand Variable Usage and Declaration Sections
Use Variables and Constants to Make Procedures Easier to Maintain
Apply Constants for Fixed Values Used Throughout Code

4Loops

Review Common Loop Structures
Use Loops to Repeat Procedures
Create Single, Double, And Nested Loops
Automate Repetitive VBA Tasks

5Effective Decision Making

Use IF Conditional Logic to Control Macro Procedures
Use Input Validation to Control User Responses
Use Worksheet Functions in VBA To Test Existing Data
Apply Select Case for Multiple Conditions or Value Ranges

6Events

Introduce Event-Driven VBA Procedures
Review How Event Handlers Respond to User Actions
Use Form Events to Initialize Controls and Default Values
Use Control Events to Validate User Input

7Debugging And Error Handling

Identify Syntax, Compile, Logic, And Run-Time Errors
Review How VBA Displays and Handles Errors
Use Debugging Tools to Locate Problems in Code
Build Basic Error Handlers for Safer Procedures
Control How Procedures Respond When Errors Occur

8Excel Worksheet Functions In VBA

Use Excel Worksheet Functions Inside VBA Procedures
Create User-Defined Functions In VBA
Use The LAMBDA Function to Create User-Defined Functions Without Code
Reuse Custom Functions Inside Worksheets and Macros

9User Forms

Introduce UserForms as Custom Data-Entry Interfaces
Add And Configure Form Controls
Set Form and Control Properties
Use Form Events to Initialize and Validate User Input

Excel Solutions Team
Kirkwood Tower, 11757 Katy Fwy, Suite 1300, Houston, TX 77079
(713) 589-2655 · contactinfo@excelsolutionsteam.com · excelsolutionsteam.com

Official CPE Provider (TX State Board of Public Accountancy)
BBB Accredited since 2006 · 20+ years training Houston professionals