Course Outline
Introduction to VBA
- Overview of the VBA environment
- The VB Editor
- Navigating the Project Explorer
- The Toolbox
- Working with Modules
- Procedures and Functions
- Adding Comments
Programming Fundamentals and Concepts
- Data types, variables, and constants
- Controlling program flow
- If…Then… Else statements
- Do…Loop, While, and Until structures
- For…Next loops
- Input and Output, Message Boxes, and Input Boxes
- Form controls and backend code
- Passing arguments and return values
Debugging Techniques
- Run Time, Design Time, and Break Mode
- Setting Breaks and Watches
- The Local Window
- The Immediate Window
Access - Object-Oriented Programming
- The Access object model
- Objects and Collections
- Events
- Methods and properties
- Data Access Object Library
Designing the Access User Interface
- Creating event-driven procedures
- Implementing dynamic combo boxes
- Handling user input
- Key UI design considerations
- Menus
Access SQL & Database Architecture
- Filtering with various Where clause options
- Derived data via calculated fields
- Inner table joins
- Outer table joins
- Sub-queries for filtering, virtual tables, and columns
- Creating records – direct entry or via queries
- Modifying and deleting data directly or through sub-queries
- Table creation and removal
- Establishing relationships
- Utilizing primary and foreign keys
Excel - Object-Oriented Programming
- The Excel object model
- Objects and Collections
- Events
- Methods and properties
Excel – Developing Analysis Routines
- Sorting, filtering, and distributing data across sheets and workbooks
- Controlling charts with VBA
- Managing pivot tables using VBA
- Handling What If scenarios with VBA
- Importing data from external files and the internet
Error Handling
- Step-through code execution
- Identifying syntax errors
- Leveraging Debug.Print
- Developing robust error-trapping routines
Requirements
Participants should possess a strong working knowledge of Excel features such as formulas, sorting, filtering, charts, and pivot tables. Additionally, they should be familiar with managing tables, queries, forms, and reports within 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.
