Visual Basic for Applications (VBA) - Excel 2000/2003

AUDIENCE: This course is designed for those who have knowledge of the workings of Excel and who wish to extend its capabilities to automate frequently used tasks.
PREREQUISITES: Good working knowledge of Excel. No previous programming experience is required.
DURATION: 3 days. Hands on.
OBJECTIVES: On completion of the course, delegates will understand:
- How to create and edit code procedures and Functions.
- How to use Events.
- How to provide and control user access.
- How to create and protect applications.
- How to use Excel objects to achieve results.
- How to create and use Custom Forms and dialogue boxes.
- How to access and manipulate external data.
COURSE CONTENT:
Visual Basic for Applications
Introduction
Definitions
Objectives

Programming in Excel
Procedures
Relative and Absolute References
Availability of Procedures
Personal Macro Workbook

Editing Recorded Procedures
Viewing Code
The Visual Basic Editor
Reading Code
Using Visual Basic Procedures
Interactive Procedures
Control Structures
Using Help
Customising Visual Basic

User Defined Functions
Creating and using User Defined Functions

Visual Basic Procedures
Using Procedures Together
Calling Procedures
Optional Arguments
Passing Arguments
Project Organisation
Protecting Procedures
Protecting Modules

Visual Basic Objects
Using Objects
Referencing and Using Properties
Common Properties
Using Methods
Collections
Object Browser

Visual Basic Code
Declaring Variables and Arguments
Data Types
Function Data Types
Assigning Objects to Variables
Scope and Life of Variables
Arrays

Control Structures
Conditional Structures
Repetition
Nesting structures

Testing and Debugging
Debugging Tools
Calls Stack
Watch Expressions
Immediate Pane

Error Handling
Error Handler Design
Error-Handling Hierarchy
Testing Error Handling

Object Models
Object Model Overview
Using Objects to Automate Tasks

Application Objects
Properties
Methods
Events

Data Access Objects
DAO Objects
Accessing ODBC Data
DAO with ODBCDirect
Using ODBCDirect

ActiveX Controls and Dialogue Boxes
Custom Dialogue Boxes
Controls

Optimising for Size and Speed
Optimisation Strategies

User Access
Menus
Toolbars
Command Buttons

Configuration Settings
Writing to the Registry
Reading from the Registry

JS07/01