Introduction to VBA

• Why use VBA if I can record macros in Excel?
• Recoding a Simple macro
• Reviewing the code
• Familiarisation with the VBA environment
• Running Code from the VB Editor window
• Getting help on code
• Stepping through a procedure
• Using a Break point
• Communicating with the User

VBA Terminology

• Modules and procedures
• Components of your code
• Objects, Collections, Properties and methods
• Using the Excel Object model

Data Types, Variables and Operators

• Working with variables and constants
• Using Data types
• Working with operators and expressions
• Implicit and Explicit Declarations
• Variable Scope
• Passing variables by Value
• Passing variables by Reference
• Using Arrays

Workbooks and Worksheets

• Workbook objects and methods
• Creating a new workbook
• Adding and Renaming Sheets
• Saving and Closing a Workbook

Functions

• Mathematical functions
• Date and time functions
• String functions
• Using Excel functions in VBA code

User Defined Function Procedures

• Creating User Defined functions
• Using user-defined functions in a worksheet
• Declaring Multiple arguments in a function
• Creating a Function Library

Loops

• Do Until and Do While loops
• Looping at least once
• For Next Loop
• For Each Loops with collections

Decision Structures

• Using IF to make decisions
• Testing for multiple conditions
• Establishing Flow control
• Branching
• Call other procedures

Working with Names

• Adding Names
• Deleting Names
• Creating Hidden Names
• Checking for the Existence of a Name

Manipulating data

• Working with the Ranges and Selections
• Using the Cells Property to Select a Range
• Using the Offset Property to Refer to a Range
• Using the Columns and Rows Properties
• Using the Union Method to Join Multiple Ranges
• Using the IsEmpty Function
• Using the CurrentRegion Property
• Using the Areas Collection

File management techniques in VBA

• Open and Save files
• Copy, move and delete files
• Changing folders
• Using Excel Open and SaveAs dialog boxes in code

Custom Forms / Dialog boxes

• Creating a user form
• Labels, text boxes, combo boxes and list boxes
• Setting properties for the form and controls
• Assigning data to combo boxes and list boxes
• Option buttons, Groups, checkboxes and buttons
• Creating the event code for controls
• Initialising the form
• Closing the form
• Using RefEdit to allow user interaction

Managing Information with VBA

• Linking Excel to an Access database
• Adding a Record to the Database from Excel
• Retrieving Records from the Database
• Updating an Existing Record