Course Outline
Introduction to VBA
- Overview of the VBA environment
- VB Editor
- Navigating the Project Explorer
- Utilizing the Toolbox
- Working with Modules
- Procedures and Functions
- Incorporating Comments
Programming Principles and Concepts
- Data types, variables, and constants
- Control flow in programs
- Conditional logic: If…Then…Else
- Loops: Do…Loop, While, and Until
- For…Next loops
- Input/Output operations, Message boxes, and Input boxes
- Event-driven code in forms and manipulating form controls
- Passing arguments and returning values
Debugging Techniques
- Run-time, design-time, and break mode operations
- Setting breakpoints and watching variables
- Using the Local Window
- Utilizing the Immediate Window
Access – Object-Oriented Programming
- Understanding the Access object model
- Working with Objects and Collections
- Handling Events
- Methods and properties
- Utilizing the Data Access Object Library
Access User Interface Design
- Writing event procedures
- Implementing dynamic combo boxes
- Managing user inputs
- Key interface design principles
- Creating and customizing Menus
Access SQL & Database Design
- Data filtering – various Where clause options
- Data derivation – calculated fields
- Inner joins between tables
- Outer joins between tables
- Sub-queries – filtering, virtual tables, and columns
- Inserting data – adding rows directly or through queries
- Updating and deleting records – directly and via sub-queries
- Creating and dropping tables
- Establishing relationships
- Employing primary and foreign keys
Excel – Object-Oriented Programming
- Understanding the Excel object model
- Working with Objects and Collections
- Handling Events
- 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
- Handling What If scenarios using VBA
- Importing data from external files, the internet, and more
Error Handling
- Stepping through code execution
- Identifying syntax errors
- Utilizing Debug.Print
- Developing error-trapping routines
Requirements
Participants should be proficient in using Excel formulas, sorting and filtering data, creating charts, and working with pivot tables, among other skills. Regarding Access, they must 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.
