Course Outline
Introduction to VBA
- Overview of the VBA environment
- The VB Editor
- Navigating the Project Explorer
- The Toolbox
- Working with Modules
- Defining Procedures and Functions
- Adding Comments
Core programming principles and concepts
- Data types, variables, and constants
- Controlling program flow
- If...Then... Else constructs
- Do...Loop, While, and Until structures
- For...Next statements
- Input/Output, message boxes, and input dialogs
- Form coding and manipulating form controls
- Passing arguments and returning values
Debugging
- Run-time, Design-time, and Break modes
- Setting breaks and watches
- Utilizing the Local Window
- Using the Immediate Window
Access - Object-Oriented Programming
- The Access object model
- Objects and Collections
- Handling Events
- Methods and properties
- Data Access Object (DAO) Library
Access User Interface Design
- Writing event-driven procedures
- Implementing dynamic combo boxes
- Managing user inputs
- Interface design best practices
- Customizing Menus
Access SQL & Database Design
- Filtering – various Where clause options
- Deriving data - calculated fields
- Inner table joins
- Outer table joins
- Sub-queries – filtering, virtual tables and columns,
- Creating records – adding rows directly or via queries
- Updating and Deleting - directly and via sub-queries
- Creating and Dropping tables
- Establishing relationships
- Utilization of primary and foreign keys
Excel - Object-Oriented Programming
- The Excel object model
- Objects and Collections
- Handling Events
- Methods and properties
Excel – Programming Analysis Routines
- Sorting, filtering, and moving data between worksheets and workbooks
- Controlling charts using VBA
- Managing pivot tables with VBA
- Controlling What If scenarios with VBA
- Importing data from external files, the internet, etc.
Error Handling
- Executing code step-by-step
- Identifying syntax errors
- Utilizing Debug.Print
- Constructing error-trapping routines
Requirements
Participants should be proficient in utilizing Excel formulas, sorting and filtering, charting, and pivot tables. In Access, they should be capable of managing 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.
