Course Outline
Introduction to VBA
- Familiarization with the VBA environment
- Overview of the VB Editor
- Navigating the Project Explorer
- Utilizing the Toolbox
- Understanding Modules
- Defining Procedures and Functions
- Writing Code Comments
Foundations of Programming Concepts
- Managing Data Types, Variables, and Constants
- Controlling Program Flow
- Implementing If…Then… Else logic
- Using Do…Loop, While, and Until constructs
- Applying For…Next statements
- Handling Input / Output, Message Boxes, and Input Boxes
- Managing Code Behind Forms and Form Controls
- Passing Arguments and Returning Values
Debugging Techniques
- Distinguishing between Run Time, Design Time, and Break Mode
- Setting Breaks and Watches
- Utilizing the Local Window
- Working with the Immediate Window
Access: Object-Oriented Programming
- Exploring the Access Object Model
- Working with Objects and Collections
- Handling Events
- Implementing Methods and Properties
- Utilizing the Data Access Object Library
Access User Interface Design
- Programming Event Procedures
- Implementing Dynamic Combo Boxes
- Managing User Inputs
- Considering Interface Design Best Practices
- Configuring Menus
Access SQL & Database Design
- Filtering Data – Various Where Clause Options
- Deriving Data – Calculated Fields
- Performing Inner Table Joins
- Performing Outer Table Joins
- Constructing Sub-queries – Filtering, Virtual Tables, and Columns
- Creating Records – Adding Rows Directly or via Queries
- Updating and Deleting Records – Directly and via Sub-queries
- Creating and Dropping Tables
- Establishing Relationships
- Applying Primary and Foreign Keys
Excel: Object-Oriented Programming
- Examining the Excel Object Model
- Interacting with Objects and Collections
- Managing Events
- Applying Methods and Properties
Excel: Programming Analysis Routines
- Sorting, Filtering, and Distributing Data Between Worksheets and Workbooks
- Controlling Charts via VBA
- Managing Pivot Tables with VBA
- Controlling What If Scenarios using VBA
- Importing Data from External Files, the Internet, and Other Sources
Error Handling
- Stepping Through Code Execution
- Identifying Syntax Errors
- Using Debug.Print for Diagnostics
- Building Robust Error-Trapping Routines
Requirements
Participants are expected to be proficient in utilizing Excel formulas, applying sorting and filtering, creating charts, and managing pivot tables. Additionally, candidates should be capable of maintaining tables, executing queries, and managing forms and reports within Microsoft Access.
Testimonials (4)
Well structured. Good teaching techniques. Course well documented!
Sorin
Course - VBA For Access & Excel
Tamil was exceptionally patient and very helpful in figuring out solutions to real needs. He was also very honest about if he didn't know something from the top of his head, which enabled us to quickly jump on in the training and we didn't lose time.
Christine Weiler
Course - VBA For Access & Excel
The explanation way and including tips on the best practices in VBA/Access, encouraging via exercise to think more by ourselves on how to solve the problem rather than giving ready solutions.
Daria Rudin
Course - VBA For Access & Excel
I liked the examples and the way he explained.
