VBA Training: VBA for Office XP and 2003

Course Number: VBA-100
GSA/Previous Course Number: 875
Duration: 4 days
view class outline

VBA Training Overview

Accelebrate's VBA for Office XP and 2003 training teaches attendees how to build interactive Microsoft Office solutions using Visual Basic for Applications and Microsoft Office XP or 2003.

Location and Pricing

Most Accelebrate courses are taught on-site at our clients' locations worldwide for groups of 3 or more attendees and are customized to their specific needs. Please visit our client list to see organizations for whom we have recently delivered training. These courses can also be delivered as live, private online classes for groups that are geographically dispersed or wish to save on the instructor's or students' travel expenses. To receive a customized proposal and price quote private training at your site or online, please contact us.

In addition, some courses are available as live, online classes for individuals. To see a schedule of online courses, please visit http://www.accelebrate.com/online_training/?action=category&page=vba.

VBA Training Prerequisites

No prior experience is presumed.

Hands-on/Lecture Ratio

This VBA training class is 70% hands-on, 30% lecture, with the longest lecture segments lasting for 15 minutes. Students "learn by doing," with immediate opportunities to apply the material they learn to real-world problems.

VBA Training Materials

All attendees receive more than 600 pages of courseware and a related reference book.

Software Needed on Each Student PC

A full installation of Microsoft Office XP or 2003, including Visual Basic support and Visual Basic Help
If you have purchased this class, please contact us for additional setup details

VBA Training Objectives

All attendees will learn to:

Understand the Visual Basic Editor included in all of the Microsoft Office products.
Write VBA procedures that do complex calculations.
Use a VBA procedure in an Access query or an Excel spreadsheet formula.
Create custom toolbars and buttons to run your VBA code.
Design forms that act as dialog boxes for your VBA solutions.
Use events in forms and in the Office products to customize the way each product works.
Understand the object models and how they are organized.
Understand the Object Browser and online Help - the tools you'll need to figure out any product's object model.
Determine what objects the user is working with when your code runs.
Understand the techniques you'll need to create solutions that use the features of more than one product.
Open a table in a database to read and change its records.
Use different techniques to add data to an Excel workbook.
Create charts from code using information in Excel or the result of a database query.
Get information from Word documents to create new documents or PowerPoint presentations.
Automatically send an Outlook e-mail message while you're working in Excel or Access.

VBA Training Outline

Getting Started with VBA

What Is VBA?
What Products Host VBA?
What Does a VBA Application Look Like?
Working with VBA in a Host Product
How Do You Make VBA Code Happen?
Starting with VBA by Recording Macros

Writing VBA Procedures

Overview of the Visual Basic Editor
Working with the Project Explorer
Creating Simple Procedures
Understanding the Code Window
Customizing the Visual Basic Editor

Working with Variables and Constants

What Is a Variable?
Data Types
Working with Arrays
Using Constants

Visual Basic Editor Techniques

Writing Code Efficiently
Taking Advantage of IntelliSense
Optional and Named Arguments

Mastering VBA Control Structures

Branching Structures
Looping Structures

Investigating Built-In VBA Functions

VBA's Built-In Functions
Using the Object Browser to Find VBA Functions
Testing Functions in the Immediate Window
Interacting with the Computer
VBA Functions You'll Probably Need

Creating User Forms

Working with User Forms
Adding Controls to a User Form
Formatting Controls
Setting Properties at Design Time
Introducing Events
Setting Properties at Run Time
Additional ActiveX Controls
Working with Methods
Working with List Boxes and Combo Boxes

Understanding Object Models

Defining the Terms
Using the Object Browser
Working with Object Models in VBA
Object Variables
Special VBA Control Structures for Objects
Working with the Office Object Models

Handling Errors

Recognizing a Run-Time Error
The Error-Handling Standard
Exiting the Error Handler
Inline Error Handling
Combining Types of Error Handling

Debugging Your VBA Code

Debugging Your Code
Setting a Breakpoint
Stepping Through Code
Checking Values at Run Time
Watch Expressions
The Call Stack
Working with Error Handling
The Debugging Process

Understanding Events

Events and Event Procedures
Which Objects Have Events?
User Form Events
Control Events
Product-Specific Events
Is There More?

Putting It All Together

Assembling the Pieces
Building a Solution in Excel
Using the Solution in Word
Where Are You Going from Here?

Exploring the Object Models of the Office Products

Figuring Out Object Models
Working with the Object Browser: Access
Knowing the Interactive Product: Word
Mining the Help File: Excel
Using the Macro Recorder: PowerPoint

Working with Active Objects

Active Properties in Office
Excel's Active and Selection Properties
Word's Active and Selection Properties
The Access Screen Object

Using Another Product's Features with Automation

Expanding Your Horizons Using Automation
Getting to the Object Model
Working With an Existing Instance

Working with Data Using ADO

Introducing ActiveX Data Objects
Working with Data in Recordsets
Moving Through a Recordset
Creating a Recordset Based on a SQL String
Adding a Recordset to a Worksheet
How and When to Use DAO

Techniques with VBA Variables

Scope, Lifetime, and Precedence
Passing by Value and by Reference
Optional Arguments

Adding Data to Excel, Access, and Outlook

Using VBA to Work with Data
Adding Values to an Excel Worksheet
Adding ADO Data to Excel
Adding a Database Record with ADO
Working with the ADO Command Object
Adding an Item to Outlook

Adding Content to Word and PowerPoint

Adding Text to a Word Document
Adding Content to PowerPoint

Charting Your Data

Excel's Chart Object
Creating a Chart
Creating an Excel Chart from Access
Pasting Excel Charts Into Other Documents

Giving Your Solutions to Others

Sharing the Wealth
Using Custom Toolbars
Office Templates and Add-Ins
Securing Your Code
Using the Developer Edition to Deploy Solutions

Conclusion