Course Outline
Introduction to VBA
- Overview of the VBA environment
- The VB Editor
- Navigating the Project Explorer
- The Toolbox
- Module structure
- Procedures and Functions
- Code commenting conventions
Programming fundamentals and core concepts
- Data types, variable usage, and constant definitions
- Control of program flow
- If...Then... Else logic
- Do...Loop, While, and Until constructs
- For...Next iteration statements
- Input/Output operations, Message boxes, and Input boxes
- Form-level code and Form control interaction
- Parameter passing and return value management
Debugging
- Run Time, Design Time, and Break Mode states
- Breakpoints and Watch windows
- The Local window
- The Immediate window
Access - Object-Oriented Programming
- The Access object model
- Objects and Collections
- Event handling
- Methods and properties
- Data Access Object (DAO) Library
Access User Interface Design
- Implementation of event procedures
- Dynamic combo box creation
- Managing user inputs
- Considerations for interface design
- Menu construction
Access SQL & Database Design
- Data filtering using various Where clause options
- Data derivation through calculated fields
- Inner table joins
- Outer table joins
- Sub-queries for filtering, virtual tables, and columns,
- Record creation by adding rows directly or via queries
- Updating and deleting records directly or via sub-queries
- Creating and dropping tables
- Establishing relationships
- Application of primary and foreign keys
Excel - Object-Oriented Programming
- The Excel object model
- Objects and Collections
- Event handling
- Methods and properties
Excel – Programming Analysis Routines
- Sorting, filtering, and distributing data across worksheets and workbooks
- Chart control via VBA
- Pivot table control via VBA
- What If scenario control via VBA
- Importing data from external files, the internet, and other sources
Error Handling
- Stepping through code execution
- Identifying syntax errors
- Utilizing Debug.Print
- Developing error-trapping routines
Requirements
Participants are expected to possess intermediate proficiency in using Excel, including proficiency with formulas, sorting and filtering, charts, and pivot tables. Regarding Access, participants should be capable of maintaining tables, queries, forms, and reports to ensure effective engagement with the material.
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.
