Course Outline
Introduction to VBA
- Overview of the VBA development environment
- The VB Editor interface
- Navigating the Project Explorer
- Utilizing the Toolbox
- Understanding Modules
- Defining Procedures and Functions
- Incorporating Comments
Core Programming Principles and Concepts
- Data types, variables, and constants
- Controlling program flow
- If…Then… Else logic
- Looping structures: Do…Loop, While, and Until
- Iterative processes using For…Next statements
- Managing Input and Output, message boxes, and input prompts
- Working with code behind forms and manipulating form controls
- Handling arguments and returning values
Debugging Techniques
- Distinguishing between Run Time, Design Time, and Break Mode
- Setting Breakpoints and using Watch windows
- Inspecting variables in the Local Window
- Using the Immediate Window for direct command execution
Access: Object-Oriented Programming
- The Access object model
- Managing Objects and Collections
- Handling Events
- Working with Methods and Properties
- Utilizing the Data Access Object Library
Access User Interface Design
- Writing event-driven code
- Implementing dynamic combo boxes
- Capturing user inputs
- Best practices in interface design
- Configuring Menus
Access SQL & Database Design
- Data filtering using various Where clause options
- Derived data through calculated fields
- Performing Inner table joins
- Executing Outer table joins
- Utilizing Sub-queries for filtering, virtual tables, and columns
- Data creation: adding rows directly or via queries
- Modifying and removing records directly and through sub-queries
- Table creation and deletion
- Establishing relationships
- Application of primary and foreign keys
Excel: Object-Oriented Programming
- The Excel object model
- Managing Objects and Collections
- Responding to Events
- Utilizing Methods and Properties
Excel – Programming Analysis Routines
- Sorting, filtering, and redistributing data across worksheets and workbooks
- Controlling charts via VBA
- Managing pivot tables with VBA
- Automating What If analysis scenarios
- Importing data from external sources, the internet, and other platforms
Error Handling
- Step-by-step code execution
- Identifying syntax errors
- Utilizing Debug.Print for diagnostics
- Constructing robust error-trapping routines
Requirements
Participants should possess a solid grasp of essential Excel functions, including formulas, sorting, filtering, charting, and pivot tables. Additionally, they should be proficient in 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.
