Excel 2007: Introduction to Programming in VBA

CLASS DATE(s):
11/8/2010 - 11/10/2010

COURSE LENGTH: 3 Days

COURSE COST: $1,195.00*

COURSE TIMES: 8:30am - 4:00pm


print
register

COURSE OVERVIEW

The skills and knowledge acquired in this course are sufficient to be able to create VBA applications within Excel 2007.

At the completion of this course, you will be able to:

• create recorded macros in Excel
• use the macro recorder to create a variety of macros
• understand the Excel object model and VBA concepts
• work with the three main components of the VBA Editor window
• create command procedures
• create and use variables
• create and work with user-defined functions
• write code to manipulate Excel objects
• use a range of common programming techniques
• create a custom form complete with controls and event procedures
• create code to drive a user form
• create procedures that start automatically
• write a variety of error handling routines

This course is designed for users who wish to learn how to use the inbuilt programming language in Excel to enhance their worksheets and automate processes.

This curriculum assumes the student has some prior knowledge and familiary working with Excel 2007. You must also have a general understanding of personal computers and the Windows operating system environment.

*Course Cost listed above does not include the cost of books. Please contact VTEC (sales@vtec.org or 207-775-0244) for information on the book(s) available for this course.

COURSE TOPICS:

Understanding Excel Macros

Setting Macro Security

Saving A Document As Macro Enabled

Recording A Simple Macro

Running A Recorded Macro

Relative Cell References

Running A Macro With Relative References

Viewing and Editing a Macro

Assigning A Macro To The Toolbar

Assigning A Keyboard Shortcut To A Macro

Deleting or Copying a Macro

Tips For Developing Macros

Preparing Data For An Application

Recording A Summation Macro

Recording Consolidations

Recording Divisional Macros

Testing Macros

Creating Objects To Run Macros

Assigning A Macro To An Object

VBA Terminology

About Objects

Accessing The Excel Object Model

Using The Immediate Window

Working With Object Collections

Setting Property Values

Working With Worksheets

Using The Object Browser

Programming With The Object Browser

The VBA Editor

Opening And Closing The Editor

Working With The Project Explorer

Working With The Properties Window

Working With The Work Area

Working With A Code Module

Setting Breakpoints In The Editor

Stepping Through A Procedure

About Procedures

Creating A Command Procedure

Making Sense of IntelliSense

Using The Edit Toolbar

Commenting Statements

Indenting Code

Bookmarking In Procedures

Understanding Variables

Creating And Using Variables

Implicit And Explicit Declarations

The Scope Of Variables

Procedure Level Scoping

Module Level Scoping

Passing Variables By Reference

Passing Variables By Value

Data Types

Declaring Data Types

Using Arrays

About Functions

Creating Simple User-Defined Functions

Using User-Defined Functions

Using Multiple Arguments

Modifying A Function

Creating A Function Library

Application Methods And Properties

Workbook Methods And Properties

Viewing Excel And The Editor Together

Using Workbook Objects

Worksheets Methods And Properties

Using Worksheet Objects

Range Methods And Properties

Using Range Objects

Using Objects In A Procedure

The MsgBox Function

Sending Messages To The User

InputBox Techniques

Using The InputBox Function

Using The InputBox Method

The If Statement

Using If For Single Conditions and Multiple Conditions

The Select Case Statement

Using The Select Case Statement for Loops

Looping With Known Or Specified Iterations

The Do...Loop Statement

Looping With Unknown Iterations

About Custom Forms

Creating A Custom Form

Adding Text Boxes To A Form

Changing Text Box Control Properties

Adding Label Controls To A Form

Adding A Combo Box Control

Adding Option Buttons

Adding Command Buttons

Running A Custom Form

Handling Form Events

Initialising A Form

Closing A Form

Project Transferring Data From A Form

Running Form Procedures

Creating Error Checking Procedures

Running A Form From A Procedure

Creating Quick Access Toolbar Buttons

Programming Automatic Procedures

Running Automatic Procedures

Automatically Starting A Workbook

Error Types

The On Error Statement

Creating A Simple Error Handler

Using The Resume Statement

Using Decision Structures In Error Handlers

Using The Err Object

Error Handling In Event Procedures

Defining Custom Errors

Concluding Remarks