Access and Excel VBA Training: VBA for Excel 2003 and Access 2003

Course Number: VBA-110
Duration: 3 days
view class outline

Access and Excel VBA Training Overview

Accelebrate's Access and Excel VBA training: VBA for Excel 2003 and Access 2003 training teaches attendees how to automate Microsoft Excel and Microsoft Access using VBA 2003.

Important Note: This course presumes that little or no time will be spent on your actual VBA projects. If you would like to include a day of time working on your actual VBA projects, please request the extended version of this course.

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.

Access and Excel VBA Training Prerequisites

No prior experience is presumed.

Hands-on/Lecture Ratio

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

Access and Excel VBA Training Materials

All attendees receive more than 300 pages of courseware and a copy of Pro Excel 2003 VBA.

Software Needed on Each Student PC

A full installation of Microsoft Office 2003 (or at least Excel and Access), including Visual Basic support and Visual Basic Help. Tying in Excel and Access 2003 with Outlook and Word is included in the outline below but could be omitted upon request.
If you have purchased this class, please contact us for additional setup details

Access and Excel 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.
Automatically send an Outlook e-mail message while you're working in Excel or Access.

Access and Excel 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
Mining the Help File: Excel

Working with Active Objects

Active Properties in Office
Excel'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

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

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

VB.NET, C#, ASP, & ASP.NET Training

Java Training

SQL Server Training
SharePoint Training

Adobe Training

“Accelebrate,” “Accelerated Learning Celebrated Results,” and the Accelebrate logo are registered trademarks of Accelebrate, Inc. All other product names and trademarks are property of their respective owners. Course outline content may mirror or be derived from the book that each student receives with the course. Otherwise, all content is copyright © 2003- by Accelebrate, Inc. of Atlanta, Georgia, USA. To contact us, please use the contact form, email info@accelebrate.com us, or call 1 877 849 1850 in the US and Canada, 0808 234 2132 in the UK, 1 800 550 311 in Ireland, or +1 678 648 3113 from all other locations.