Course Outline
Introduction to VBA
- Overview of the VBA environment
- Visual Basic Editor
- Utilizing the Project Explorer
- Toolbox components
- Working with Modules
- Procedures and Functions
- Adding Comments
Programming Principles and Concepts
- Data types, variables, and constants
- Control flow in programs
- If...Then...Else logic
- Do...Loop, While, and Until structures
- For...Next loops
- Input and Output operations, Message boxes, Input boxes
- Code behind forms and manipulation of Form controls
- Passing arguments and returning values
Debugging Techniques
- Run Time, Design Time, and Break Mode
- Breakpoints and Watches
- Local Window
- Immediate Window
Access - Object-Oriented Programming
- Access object model
- Objects and Collections
- Events
- Methods and properties
- Data Access Object Library
Access User Interface Design
- Writing event procedures
- Dynamic combo boxes
- Managing user inputs
- User interface design considerations
- Menus
Access SQL and Database Design
- Filtering – various Where clause options
- Data derivation – calculated fields
- Inner table joins
- Outer table joins
- Sub-queries – filtering, virtual tables, and columns
- Creating – adding rows directly or via queries
- Updating and Deleting – directly and via sub-queries
- Creating and Dropping tables
- Creating relationships
- Use of primary and foreign keys
Excel - Object-Oriented Programming
- Excel object model
- Objects and Collections
- Events
- Methods and properties
Excel – Programming Analysis Routines
- Sorting, filtering, and distributing data between worksheets and workbooks
- Controlling charts using VBA
- Controlling pivot tables using VBA
- Controlling What If scenarios using VBA
- Importing data from external files, the internet, etc.
Error Handling
- Stepping through code
- Identifying syntax errors
- Using Debug.Print
- Developing error-trapping routines
Requirements
Participants should be proficient in using Excel features such as formulas, sorting, filtering, charts, pivot tables, and others. 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.
