Course Outline
Introduction to VBA
- Overview of the VBA development environment
- Navigating the VB Editor
- Utilizing the Project Explorer
- Exploring the Toolbox
- Understanding Modules
- Defining Procedures and Functions
- Using Comments
Programming Principles and Concepts
- Data types, variables, and constants
- Managing program flow
- If…Then…Else structures
- Do…Loop, While, and Until iterations
- For…Next loop statements
- Input/Output operations, Message boxes, and Input boxes
- Code-behind forms and manipulating form controls
- Argument passing and value returning
Debugging Techniques
- Run Time, Design Time, and Break modes
- Setting Breaks and Watches
- Using the Local Window
- Utilizing the Immediate Window
Access: Object-Oriented Programming
- The Access object model
- Objects and Collections
- Event handling
- Methods and properties
- The Data Access Object Library
Access User Interface Design
- Programming event procedures
- Implementing dynamic combo boxes
- Capturing user inputs
- Interface design best practices
- Menu configuration
Access SQL and Database Design
- Filtering using various Where clause options
- Deriving data via calculated fields
- Inner table joins
- Outer table joins
- Sub-queries – filtering, virtual tables, and columns
- Creating records – direct insertion or via queries
- Updating and deleting records directly or via sub-queries
- Creating and dropping tables
- Establishing relationships
- Implementing 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
- Controlling charts with VBA
- Managing pivot tables via VBA
- Controlling What If scenarios with VBA
- Importing data from external files, the internet, and other sources
Error Handling
- Stepping through code execution
- Identifying syntax errors
- Utilizing Debug.Print
- Constructing error-trapping routines
Requirements
Participants are expected to have proficiency in utilizing Excel formulas, sorting and filtering mechanisms, charts, and pivot tables. Additionally, familiarity with Access is required, including the ability to manage 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.
