Course Outline
Introduction to VBA
- Familiarising yourself with the VBA environment
- The VB Editor interface
- Navigating the Project Explorer
- Utilising the Toolbox
- Understanding Modules
- Defining Procedures and Functions
- The role of Comments in code
Principles and concepts of programming
- Working with Data types, variables, and constants
- Controlling program flow
- Conditional logic: If…Then… Else
- Loop structures: Do…Loop, While, and Until
- Iteration using For…Next statements
- Handling Input / Output, including Message and Input boxes
- Manipulating Form controls via code behind forms
- Managing arguments and return values
Debugging
- Understanding Run Time, Design Time, and Break Mode
- Setting Breaks and monitoring with Watches
- Utilising the Local Window
- Interacting with the Immediate Window
Access - Object-Oriented Programming
- The Access object model
- Working with Objects and Collections
- Understanding Events
- Applying Methods and properties
- Exploring the Data Access Object Library
Access User Interface Design
- Implementing event-driven procedures
- Creating Dynamic combo boxes
- Managing User inputs
- Key Interface design considerations
- Customising Menus
Access SQL & Database Design
- Data filtering and various Where clause options
- Deriving data through calculated fields
- Implementing Inner table joins
- Implementing Outer table joins
- Sub-queries for filtering, virtual tables, and columns,
- Creating records via direct insertion or queries
- Updating and Deleting data directly or using sub-queries
- Creating and Dropping tables
- Establishing relationships
- Applying primary and foreign keys
Excel - Object-Oriented Programming
- The Excel object model
- Working with Objects and Collections
- Managing Events
- Applying Methods and properties
Excel – Programming Analysis Routines
- Sorting, filtering, and distributing data across worksheets and workbooks
- Controlling charts with VBA
- Managing pivot tables using VBA
- Implementing What If scenarios via VBA
- Importing data from external files, the internet, etc.
Error Handling
- Stepping through code for inspection
- Identifying syntax errors
- Utilising Debug.Print
- Developing robust error-trapping routines
Requirements
Participants are expected to have a solid grasp of core Excel functionalities, including formulas, data sorting and filtering, charting, and pivot tables. Additionally, familiarity with Access is required, specifically the ability to manage tables, execute queries, and maintain 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.
