VBA For Access & Excel

Introduction to VBA

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

• 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

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

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

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

• 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 model
• Objects, Collections
• Events
• Methods and properties

• 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

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