VBA For Access & Excel Training Course

Introduction to VBA

• Introducing the VBA environment
• VB Editor
• Using Project Explorer
• Toolbox
• Modules
• Procedures and Functions
• Comments

Principles and concepts of programming

• Data types, variables and constants
• Program flow control
• If…Then… Else
• Do…Loop, While and Until
• For…Next statements 
• Input / Output, Message boxes, Input boxes
• Code behind forms, Form controls manipulation
• Passing arguments, Returning Values

Debugging

• Run Time, Design Time, and Break Mode
• Breaks and Watches
• Local Window
• Immediate Window

Access - Object-Oriented Programming

• Access object model
• Objects, Collections
• Events
• Methods and properties
• Data Access Object Library

Access User Interface Design

• Programming event procedures
• Dynamic combo boxes
• User inputs
• Interface design considerations
• Menus

Access SQL & Database Design

• Filtering – various Where clause options
• Deriving data - 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, Collections
• Events
• Methods and properties

Excel – Programming Analysis Routines

• Sorting, filtering and distributing data between worksheets and workbooks
• Using VBA to control charts
• Using VBA to control pivot tables
• Using VBA to control What If scenarios
• Importing from external files, internet et cetera

Error Handling

• Stepping through code
• Finding syntax errors
• Using Debug.Print
• Building error-trapping routines