Introduction to VBA – Office 2007

This course is designed to give proficiency in the Visual Basic Editor (VBE), predominantly making use of Excel objects, basic VBA programming, event handling, control structures, and debugging tools.

Delegates will learn how to
Record and edit a Macro recorded within Excel/Word
Use different techniques in order to run any macros available such as keyboard commands, the use of the Quick Access Toolbars or Buttons
Use VBA and examine the Visual Basic Editor; change the properties of an object; add a module to a project; write the code for a procedure and then run it; and use the Object Browser to search procedures
Manipulate data by declaring variables of different data types; combine data by using expressions; use functions to accept input and display output; and declare variables and procedures with the appropriate scope
Use decision structures to create procedures that make decisions; and use loop structures to perform repetitive tasks
Create an error handling routine in case things go wrong with VBA code

This course is designed to give proficiency in the Visual Basic Editor (VBE), predominantly making use of Excel objects, basic VBA programming, event handling, control structures, and debugging tools.

Course Outline

Lesson 1: Getting started

Introducing Visual Basic for Applications
Recording a Macro
Running a Macro
Absolute or Relative Cell Referencing
Saving and Opening Files with Macros
Adding Macros to Keyboard Shortcuts, Quick Access Toolbar, and Buttons
Editing a Macro in the Visual Basic Editor
Understanding the Development Environment
Tips for General Typing in VBA
Using Visual Basic Help

Lesson 2: Working with Procedures

Program Design
Understanding Modules
Creating a Module
Understanding Procedures
Programming Macro Concepts
Creating a Subroutine
Creating a Function
Understanding Arguments
Exiting Procedures
Calling Procedures

Lesson 3: Understanding Objects, Properties, Methods, and Events

Understanding Objects, Properties, Methods, and Events
Navigating the Object Hierarchy
Understanding Collections
Using the Object Browser
Using the With Statement
Working with Properties
Working with Methods
Creating an Event Procedure

Lesson 4: Using Expressions, Variables and Intrinsic Functions

Naming Rules
Understanding Expressions and Statements
Declaring Variables
Understanding Data Types
Determining the Value of Variables
Working with Variable Scope
Using Built-in VBA Functions
Understanding Constants
Using Built-in VBA Constants
Using Message Boxes
Controlling the Answer to a Message Box
Using Input Boxes
Declaring and Using Object Variables

Lesson 5: Controlling Program Execution

Understanding Control-of-Flow Structures
Using the If...End If Decision Structures
Using the Select Case …End Select Structure
Using the Do …Loop Structure
Using the For …Next Structure
Using the For Each …Next Structure
Guidelines for Use of Control-of-Flow Structures

Lesson 6: Working with Forms and Controls

Understanding UserForms
Using the Toolbox
Working with a Form's Properties, Methods, and Events
Working with Form and Control Properties, Methods and Event
Working with Form Controls
Setting the Tab Order
Populating a Control
Adding Code to Controls

Lesson 7: Debugging and Handling Errors

Understanding Errors
Using Debugging Tools
Setting Breakpoints and Using Break Mode
Stepping Through Code
Trapping Errors with the On Error Statement
Understanding the Err Object
Working with Inline Error Handling
Writing an Error-Handling Routine