Course Outline
Introduction to VBA
- Overview of the VBA development environment
- Introduction to the VB Editor
- Navigation and usage of the Project Explorer
- Utilizing the Toolbox
- Understanding Modules
- Defining Procedures and Functions
- Adding comments to code
Programming fundamentals and concepts
- Managing data types, variables, and constants
- Controlling program execution flow
- Conditional logic: If...Then... Else structures
- Looping mechanisms: Do...Loop, While, and Until
- Iteration using For...Next statements
- Handling Input/Output, including message and input boxes
- Implementing code logic behind forms and manipulating form controls
- Handling arguments and returning values from functions
Debugging techniques
- Understanding Run Time, Design Time, and Break Mode
- Setting breaks and monitoring with Watches
- Using the Local Window
- Utilizing the Immediate Window
Access: Object-Oriented Programming
- Exploring the Access object model
- Working with Objects and Collections
- Handling Events
- Applying Methods and Properties
- Using the Data Access Object Library
Access User Interface Design
- Developing event-driven procedures
- Configuring dynamic combo boxes
- Processing user inputs
- Best practices for interface design
- Designing and managing Menus
Access SQL and Database Design
- Data filtering using various Where clause options
- Deriving data through calculated fields
- Implementing Inner table joins
- Implementing Outer table joins
- Working with Sub-queries, including filtering, virtual tables, and columns
- Creating records by adding rows directly or via queries
- Performing Updates and Deletions, both directly and via sub-queries
- Creating and dropping tables
- Establishing relationships between tables
- Utilizing primary and foreign keys
Excel: Object-Oriented Programming
- Exploring the Excel object model
- Working with Objects and Collections
- Handling Events
- Applying Methods and Properties
Excel: Programming Analysis Routines
- Sorting, filtering, and distributing data across worksheets and workbooks
- Controlling charts using VBA
- Manipulating pivot tables with VBA
- Managing What If scenarios via VBA
- Importing data from external files, the internet, and other sources
Error Handling
- Stepping through code execution
- Identifying syntax errors
- Using Debug.Print for diagnostic output
- Developing robust error-trapping routines
Requirements
Participants are expected to have a solid grasp of core Excel features, including formulas, sorting and filtering, charting, and pivot tables. Additionally, experience with Access is required, specifically the ability to manage tables, queries, forms, and reports.
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.
