Course Outline
VBA Fundamentals
- Overview of the VBA environment
- The VB Editor
- Navigating the Project Explorer
- Utilizing the Toolbox
- Working with Modules
- Procedures and Functions
- Adding Comments
Core Programming Principles
- Data types, variables, and constants
- Controlling program flow
- If…Then… Else constructs
- Do…Loop, While, and Until loops
- For…Next loops
- Input/Output operations, message boxes, and input dialogs
- Form event procedures and control manipulation
- Passing arguments and returning values
Debugging Techniques
- Runtime, Design Time, and Break Mode
- Setting Breakpoints and Watches
- Exploring 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 Objects (DAO) library
Access Interface Design
- Writing event-driven code
- Implementing dynamic combo boxes
- Capturing user input
- Best practices in interface design
- Managing menus
Access: SQL & Database Architecture
- Filtering data using various WHERE clause options
- Deriving data through calculated fields
- Performing inner joins
- Executing outer joins
- Writing sub-queries for filtering and creating virtual tables/columns
- Inserting data directly or via queries
- Updating and deleting records directly or through sub-queries
- Creating and dropping tables
- Defining relationships
- Implementing primary and foreign keys
Excel: Object-Oriented Programming
- The Excel object model
- Objects and Collections
- Event handling
- Methods and Properties
Excel: Programming Analytical Routines
- Sorting, filtering, and distributing data across sheets and workbooks
- Controlling charts with VBA
- Manipulating pivot tables via VBA
- Managing What-If scenarios using VBA
- Importing data from external sources and the internet
Error Management
- Step-by-step code execution
- Identifying syntax issues
- Applying Debug.Print
- Developing robust error-trapping routines
Requirements
Participants should possess familiarity with Excel features such as formulas, data sorting and filtering, charting, and pivot tables. Additionally, regarding 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.
