Course Outline
VBA Fundamentals
- Overview of the VBA environment
- The VB Editor interface
- Navigating the Project Explorer
- Using the Toolbox
- Understanding Modules
- Defining Procedures and Functions
- Adding Comments to code
Core Programming Principles
- Data types, variables, and constants
- Controlling program flow
- Conditional logic: If...Then... Else
- Looping structures: Do...Loop, While, and Until
- Iterative processes: For...Next statements
- Input/Output methods, Message boxes, and Input boxes
- Implementing code behind forms and manipulating Form controls
- Argument passing and returning values
Debugging Techniques
- Managing Run Time, Design Time, and Break Mode
- Setting Breakpoints and Watches
- Utilising the Local Window
- Interacting with the Immediate Window
Access: Object-Oriented Programming
- The Access object model
- Working with Objects and Collections
- Handling Events
- Applying Methods and properties
- The Data Access Object Library
Access User Interface Development
- Creating event-driven procedures
- Configuring Dynamic combo boxes
- Managing User inputs
- Key Interface design considerations
- Creating Menus
Access SQL & Database Design
- Data filtering using various Where clause options
- Deriving data through calculated fields
- Implementing Inner table joins
- Implementing Outer table joins
- Writing Sub-queries for filtering, virtual tables, and columns
- Inserting data: adding rows directly or via queries
- Modifying and removing data: directly and via sub-queries
- Creating and Dropping tables
- Establishing relationships
- Applying primary and foreign keys
Excel: Object-Oriented Programming
- 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 via VBA
- Manipulating pivot tables with VBA
- Managing What If scenarios using VBA
- Importing data from external files, the internet, and other sources
Error Handling
- Stepping through code execution
- Identifying syntax errors
- Utilising Debug.Print
- Constructing error-trapping routines
Requirements
Participants should have a solid working knowledge of Excel, including proficiency with formulas, sorting, filtering, charts, and pivot tables. In relation to Access, delegates are expected to 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.
