Course Outline
Introduction to VBA
- Overview of the VBA environment
- The VB Editor
- Utilizing the Project Explorer
- Working with the Toolbox
- Managing Modules
- Procedures and Functions
- Adding Comments
Core Programming Principles and Concepts
- Data types, variables, and constants
- Control flow structures
- If...Then...Else logic
- Do...Loop, While, and Until loops
- For...Next statements
- Input and Output operations, including Message boxes and Input boxes
- Writing code behind forms and manipulating Form controls
- Passing arguments and returning values
Debugging Techniques
- Run Time, Design Time, and Break Mode
- Setting Breakpoints and Watches
- Using the Local Window
- Utilizing the Immediate Window
Access - Object-Oriented Programming
- Understanding the Access object model
- Objects and Collections
- Handling Events
- Methods and Properties
- The Data Access Object Library
Access User Interface Design
- Developing event procedures
- Implementing dynamic combo boxes
- Managing user inputs
- Key considerations for interface design
- Working with Menus
Access SQL & Database Design
- Filtering techniques and various Where clause options
- Calculating data through derived fields
- Inner joins
- Outer joins
- Sub-queries for filtering, virtual tables, and columns
- Inserting data by adding rows directly or via queries
- Updating and deleting records, both directly and through sub-queries
- Creating and dropping tables
- Establishing relationships
- Effective use of primary and foreign keys
Excel - Object-Oriented Programming
- Understanding the Excel object model
- Objects and Collections
- Handling Events
- Methods and Properties
Excel - Programming Analysis Routines
- Sorting, filtering, and distributing data across worksheets and workbooks
- Controlling charts via VBA
- Managing pivot tables with VBA
- Handling What If scenarios using VBA
- Importing data from external files, the internet, and other sources
Error Handling
- Stepping through code execution
- Identifying syntax errors
- Using Debug.Print for diagnostics
- Developing robust error-trapping routines
Requirements
Participants should already be proficient in using Excel features such as formulas, sorting, filtering, charts, and pivot tables. Regarding Access, they should be capable of managing tables, queries, forms, and reports effectively.
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.
