Excel Advanced VBA Macros
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
- Review The Visual Basic Editor
- Review VBA Naming Conventions and Coding Best Practices
- Understand Variable Declarations
- Organize Code for Easier Editing and Review
- 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
- 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
- Review Common Loop Structures
- Use Loops to Repeat Procedures
- Create Single, Double, And Nested Loops
- Automate Repetitive VBA Tasks
- 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
- 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
- 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
- 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
- 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