Course Outline
Introduction to VBA
- Overview of the VBA environment
- Introduction to the VB Editor
- Navigating with Project Explorer
- Utilizing the Toolbox
- Working with Modules
- Understanding Procedures and Functions
- Incorporating Comments
Programming Fundamentals and Concepts
- Exploring data types, variables, and constants
- Managing program flow control
- Implementing If…Then…Else structures
- Utilizing Do…Loop, While, and Until constructs
- Applying For…Next statements
- Handling Input/Output operations, Message boxes, and Input boxes
- Writing code behind forms and manipulating Form controls
- Passing arguments and returning values
Debugging Techniques
- Understanding Run Time, Design Time, and Break Mode
- Setting Breakpoints and Watches
- Using the Local Window
- Utilizing the Immediate Window
Access - Object-Oriented Programming
- Exploring the Access object model
- Working with Objects and Collections
- Handling Events
- Applying Methods and properties
- Using the Data Access Object Library
Access User Interface Design
- Developing event procedures
- Creating dynamic combo boxes
- Managing user inputs
- Best practices for interface design
- Designing Menus
Access SQL & Database Design
- Filtering data with various Where clause options
- Deriving data through calculated fields
- Performing Inner table joins
- Performing Outer table joins
- Utilizing Sub-queries for filtering, virtual tables, and columns
- Inserting rows directly or via queries
- Updating and Deleting records directly and via sub-queries
- Creating and Dropping tables
- Establishing relationships
- Applying primary and foreign keys
Excel - Object-Oriented Programming
- Exploring the Excel object model
- Working with Objects and Collections
- Handling Events
- Applying Methods and properties
Excel – Programming Analysis Routines
- Sorting, filtering, and distributing data across worksheets and workbooks
- Controlling charts using VBA
- Managing pivot tables with VBA
- Handling What If scenarios via VBA
- Importing data from external files, the internet, and other sources
Error Handling
- Stepping through code for analysis
- Identifying syntax errors
- Employing Debug.Print
- Developing error-trapping routines
Requirements
Participants are expected to have a solid working knowledge of Excel, including proficiency with formulas, data sorting and filtering, chart creation, and pivot tables. Additionally, they should be capable of managing Access components such as 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.
