Course Outline
Introduction to VBA
- Overview of the VBA environment
- The Visual Basic (VB) Editor
- Utilizing the Project Explorer
- The Toolbox
- Working with Modules
- Procedures and Functions
- Incorporating Comments
Programming Principles and Concepts
- Data types, variables, and constants
- Control flow logic
- If…Then…Else constructs
- Do…Loop, While, and Until loops
- For…Next statements
- Input/Output operations, message boxes, and input boxes
- Form code-behind logic and control manipulation
- Passing arguments and returning values
Debugging Techniques
- Run-time, design-time, and break modes
- Setting breakpoints and watches
- Using the Local Window
- Using the Immediate Window
Object-Oriented Programming in Access
- The Access object model
- Understanding Objects and Collections
- Handling Events
- Methods and properties
- The Data Access Object (DAO) Library
Access User Interface Design
- Implementing event procedures
- Creating dynamic combo boxes
- Managing user inputs
- Key considerations for interface design
- Customizing menus
Access SQL and Database Design
- Filtering data using various WHERE clause options
- Deriving data through calculated fields
- Inner joins
- Outer joins
- Subqueries for filtering, virtual tables, and columns
- Inserting records directly or via queries
- Updating and deleting records directly or via subqueries
- Creating and dropping tables
- Establishing relationships
- Using primary and foreign keys
Object-Oriented Programming in Excel
- The Excel object model
- Understanding Objects and Collections
- Handling Events
- Methods and properties
Excel Programming for Data Analysis
- Sorting, filtering, and distributing data across worksheets and workbooks
- Controlling charts with VBA
- Manipulating pivot tables via VBA
- Managing What-If analysis scenarios with VBA
- Importing data from external files and the internet
Error Handling
- Stepping through code execution
- Identifying syntax errors
- Utilizing Debug.Print
- Constructing error-trapping routines
Requirements
Participants should already be proficient in using Excel formulas, sorting and filtering data, creating charts, and working with pivot tables. Regarding Access, attendees 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.
