Special Edition Using Visual Basic for Applications 5

Special Edition Using Visual Basic for Applications 5
by Paul Sanna
Que, Macmillan Computer Publishing
ISBN: 0789709597 Pub Date: 01/17/97

Introduction

About the Authors

PART I—Getting to Know Visual Basic for Applications

CHAPTER 1—Introducing Visual Basic for Applications

What Is VBA?

Understanding What’s Possible with VBA
Understanding the Elements of a VBA Project

Learning What’s New in VBA
Comparing VBA and Visual Basic
Comparing VBA and Visual Basic Script
Comparing VBA and Java
Understanding When to Use VBA

Must Be a VBA Host Application
Extend Host Application Functionality
Integrate Different Types of Data

CHAPTER 2—Understanding How to Build Software Applications

Introducing Software Development
Phases of a Software Development Project

Teams and Roles
Project Milestones

From Definition to Delivery

Defining the Problem/Designing the Solution
Developing the Product
Packaging and Delivering the Product

How VBA Projects Compare to Traditional Software Development

CHAPTER 3—Working in the VBA Editor

Choosing the VBA Host Application
Creating the VBA Project

Record a Macro and Then Edit It
Create a Macro
Create the Project in the Editor

Opening an Existing Project
Reviewing What You See in the VBA Editor
Using the Project Explorer

Moving and Displaying the Project Explorer
Opening and Closing Branches
Selecting Objects in the Project Explorer

Using the Properties Window

Displaying and Moving the Properties Window
Choosing Objects for the Properties Window
Setting Properties
Adding a Module to Your Project
Adding a Class Module to Your Project

Using the Code Window

Displaying the Code Window
Changing What You See in the Code Window
Writing Code
Creating Procedures
Navigating Through Code in a Module
Coding Assistance

Building Forms

Adding Forms to Your Project
Display the Toolbox
Knowing the Tools in the Toolbox
Adding Additional Controls to the Toolbox
Placing Controls on the Form
Viewing a Form You’ve Been Working on
Formatting Your Forms with Aligning and Sizing Controls
Setting Tab Order for Controls on Your Form
Writing Code for Controls on the Form

Importing and Exporting Files

Importing Files
Exporting Files

Switching to the Host Application
Running the Application

Running Forms, Subs, and Macros

Saving Your Work
Setting VBA Properties

Editor Options
Editor Formatting Options
General Options
Docking Options

Setting Project Properties

General Options
Protection Options

PART II—Learning to Program with Visual Basic for Applications

CHAPTER 4—Programming in VBA

Exploring the History of VBA Programming
Selecting a Host Application
Storing Your Code in Modules and Projects
Naming Conventions
Creating VBA Procedures

Understanding Sub and Function Procedures
Recording a Procedure as a Macro
Running a Recorded Macro
Editing a Recorded Macro
Creating a Procedure in the Visual Basic Editor
Understanding the Structure of a Procedure
Learning Guidelines for Entering Code
Understanding Statements, Functions, and Expressions—The Materials of Programming
Getting a Brief Preview of Object Programming

Using Program Control Statements

Performing Decision Making with the If…Then…Else Statement
Making Choices with the Select Case Statement
Looping with the Do…Loop Statement
Preventing the Endless Loop
Exiting a Loop Early
Looping with the While…Wend Statement
Looping with the For…Next Statement
Looping with the For Each…Next Statement
Using With…End With to Work with Objects
Jumping Out with the Exit Statements
Using Unconditional Branching with the GoTo Statement
Calling Subroutines and Functions
Calling a Function Procedure

Using Good Programming Practices

CHAPTER 5—Understanding Variables, Data Types, and Constants

Defining Variables
Examining Different Data Types for Different Purposes

Numeric Data Types
Variables for Storing String Data Types
Other Data Types
The Variant Data Types
Controlling Variant Subtypes

Declaring and Using Variables

Declaring Variables with the Dim Statement
Declaring Array Variables
Using Array Variables
Multidimensional Array Variables
Declaring Your Own Types

Understanding the Scope and Persistence of Variables

Where Does the Dim Statement Go?
How Long Does a Variable Hold Its Value?
An Example of Scope and Persistence

Using Option Explicit to Require Variable Declaration
Using Constants in VBA

Declaring and Using Constants in VBA
Using Built-In Constants in VBA

CHAPTER 6—Understanding and Working with Objects

Learning About Objects

Understanding What Objects Are
Understanding Objects in Programming
Objects in Visual Programming
Understanding Abstraction
Understanding Classes
Understanding Instances

Understanding Properties

Setting the Value of Properties
Retrieving the Value of a Property

Understanding Methods

Methods Requiring Instructions
Methods Returning Information
Using Methods with Properties
Understanding Superclasses and Subclasses

Understanding How Objects Are Used in VBA

Object Use in VBA
OLE Technology

Understanding Object Syntax in VBA

Property Syntax
Method Syntax

Programming Application Objects

Understanding Your Requirements
Understanding and Inserting References
Understanding the Object Model
Working with the Object Browser
Writing the Code

Creating Objects and Class Modules

Designing Your Object and Other Objects
Creating Properties
Creating Properties Automatically
Creating Methods
Creating Your First Object

CHAPTER 7—Learning How to Use Controls in VBA Applications

Using the TextBox Control

Adding a Textbox Control to a Form
Setting and Retrieving Text in the TextBox Control
Disabling the TextBox Control
Retrieving Selected Text in the TextBox Control
Setting Selected Text in the TextBox Control
Automatically Sizing the TextBox Control

Using the Label Control

Adding a Label Control to a Form
Labeling the Label Control

Using the CommandButton Control

Adding a CommandButton Control to a Form
Formatting the Button
Making a Button the Default Button
Responding to a Button Click
Changing the State of a Command Button

Using the ListBox Control

Adding a ListBox Control to a Form
Setting the Style of the List Box
Adding Items to the List
Determining the Selection in the List
Determining Selection in Multiple-Selection Lists
Removing Items from a List

Using the ComboBox Control

Adding a ComboBox Control to a Form
Understanding ComboBox Styles
Populating a ComboBox List
Working with the Value of the ComboBox
Setting and Retrieving the Value of the Control

Using the CheckBox Control

Adding a CheckBox Control to a Form
Setting the CheckBox Caption
Determining the CheckBox Value

Using the OptionButton Control

Adding an OptionButton Control to a Form
Grouping OptionButton Controls
Determining the Selected Option

Using the Frame Control

Adding a Frame Control to a Form
Programming the Frame Control

Using the SpinButton Control

Adding a SpinButton Control to a Form
Formatting the SpinButton Control
Programming the SpinButton Control
Specifying Other SpinButton Properties

Using the ToggleButton Control

Adding a ToggleButton Control to a Form
Programming the ToggleButton Control

Working with Other Controls

Understanding the TabStrip Control
Understanding the ScrollBar Control

CHAPTER 8—Working with Additional Control

Understanding and Using Additional Controls

Adding Controls to the Toolbox
Removing a Control from the Toolbox

Programming the Calendar Control

Adding the Calendar Control
Adding the Calendar Control to the User Form
Using the Calendar Control’s Properties
Using the Calendar Control’s Methods
Examining a Couple of Simple Code Examples

CHAPTER 9—Debugging and Error Handling

Debugging and Error Handling as Part of the Development Process

Types of Errors in VBA Programs
Examples of VBA Errors

Using Option Explicit to Reduce Variable Errors
Stepping Through Your Code

Working in Break Mode
Examining Variables and Properties

Using the Immediate Window
Creating and Using Breakpoints

Toggling a Breakpoint
Clearing All Breakpoints
Working with Breakpoints: An Example
Using the Stop Statement

Handling Runtime Errors

Understanding Trappable Errors
Setting a Trap for Errors
An Outline for the Error Trap
The Err Object and the Error Handler

PART III—Programming Applications with VBA

CHAPTER 10—Programming Excel with VBA

Understanding the Excel Object Model
Working with the Application Object

Using the Application Object’s Properties
Using the Application Object’s Methods
Using the Application Object’s Events

Working with the Workbooks Collection and the Workbook Object

Using the Workbooks Collection Properties and Methods
Using the Workbook Object’s Properties
The Workbook Object’s Methods
Using the Workbook Object’s Events

Working with the Worksheets Collection

Using the Worksheets Collection’s Count Property
Using the Worksheets Collection’s Add Method

Working with the Worksheet Object, Properties, and Methods

Name Property
Visible Property
Calculate Method
Copy and Move Methods
Delete Method

Working with the Range Object

Using the Range Object’s Properties
Using the Range Object’s Methods
Examining Additional Range Examples

Building and Using Custom User Forms

Using the User Form
Placing Controls on the Dialog Sheet
Initializing Values on the Dialog Sheet
Displaying Dialog Sheets
Validating User Form Values and Writing Values from a User Form

Creating User-Defined Functions

Definition of a User-Defined Function
Writing a User-Defined Function

CHAPTER 11—Programming Word with VBA

Understanding the Word Object Model

The Parts of the Object Model
Manipulating Objects

Working with Templates and VBA

Background: Word Templates
Why Use VBA to Create Macros?
Adding a VBA Module to a Template
Which Files Are Attached to the Template?
Resolving Conflicts

Automating Word Tasks

Displaying the Open Dialog Box on Startup
Highlighting Words for Index Entries
Choosing a Printer

Building an Outline Conversion Tool
Porting Old WordBasic Macros to VBA

How Conversion Works
Not All Macros Will Port to Visual Basic

CHAPTER 12—Programming Outlook with VBA

An Overview of the Outlook Programming Environment
Differences Between VBA and Visual Basic Scripting Edition
Designing an Outlook Form

Designing the Form
Saving the Form

Programming Outlook

Working with Script Events
Reviewing an Event Procedure
Frequently Used Outlook Objects

CHAPTER 13—Programming Access with VBA

Understanding the Access Object Model

The Access Application Object Model
The Data Access Object Model

Working with Collections
Working with VBA and the Access Application Object Model

Understanding the Database Wizard
Creating a User Interface
Comparing VBA with Access Macros

Controlling Access Application Object Behavior

Opening Access Forms and Reports
Adding Functionality to Command Bars
Creating Shortcut Keys

Working with Access Application Objects

Checking for Records
Using VBA to Change Form and Report Properties
Assisting with Data Entry
Using Access Application Objects from Other Applications

Working with the Data Access Object Model

Using Recordsets
Using Queries

Automating Common Tasks

Creating a Report Selection System
Creating a Dynamic Selection System
Controlling Import and Export Operations

CHAPTER 14—Programming PowerPoint with VBA

Understanding the PowerPoint Object Model
Maneuvering Around the Object Model
Getting to Know PowerPoint

Adding Graphics to PowerPoint with VBA
Setting Animation Controls
Setting Slide Transitions
Using the Control Toolbox in PowerPoint
Working with Bullets
Working with Combo Boxes

Reviewing Important Methods, Objects, and Properties in PowerPoint

TwoColorGradient Method
Words Method
TextRange Object
ThreeDFormat Object
ColorFormat Object
ChartUnitEffects Property
PresetTextured Property

Running VBA in Slide Show or Edit Mode
Using VBA or Using PowerPoint’s Internal Code
Automatically Building a PowerPoint Status Report
Controlling Excel from PowerPoint

CHAPTER 15—Programming Project with VBA

Understanding the Project Object Model

The Application Object
The Project Object
The List Object
The Task Object
The Resource Object
The Calendar Object

Working with VBA Within Project

Evaluating the Manual Process
Creating a Custom Table
Creating a Custom Filter
Creating a Custom View
Creating a Macro
Making It Available to More than One Project

Automating Tasks in Project

Automating Reporting
Creating a Custom Menu
Using the Auto-Executing Procedures
Creating a Task Posting System
Creating a Project Posting System

Using Project with Other Applications

Running Project Macros Remotely
Accessing Project Reports
Creating Special Reports

CHAPTER 16—Programming the Microsoft Office Binder

Why Use Binder?

Grouping Documents
Early and Late Binding
Exchanging Documents

Binder Templates

Using Templates
Creating Templates

Working in Binder
What You Can’t Do in Binder

Status Bars
Shared Workbooks
Recording Macros

Sharing Binders
Printing Binders
Working with Binder Using VBA

The Binder Object Model
Example Applications

CHAPTER 17—Programming Command Bars

Introducing Command Bars
Introducing Command Bars and CommandBarControls Objects
Working with the CommandBar Objects

Understanding the CommandBars Collection Object
Creating CommandBar Objects
Working with an Existing Command Bar

Working with the CommandBarControls Collection Object

Adding Controls to a Command Bar

Working with CommandBarControl Objects

Creating a CommandBarButton Control
Creating a Menu on a Command Bar
Creating a Drop-Down List

Building and Integrating Shortcut Menus

Creating the Shortcut Menu
Adding and Programming Controls
Controlling When the Shortcut Menu Appears

Building and Integrating a Toolbar

CHAPTER 18—Programming the Office Assistant

Introducing the Office Assistant
Programming the Office Assistant

Creating an Assistant Object
Specifying the Assistant Character
Animating the Assistant
Specifying How the Assistant Works

Displaying Balloon Help

Working with the Balloon Object
The Show Method

Programming Assistant Content

Customizing the Content of a Balloon
Using the Heading Property
Using the Text Property
Using the Label Property
Using the BalloonType Property
Knowing What Balloon Button the User Clicks
Using the CheckBox Property

PART IV—Using VBA with the World Wide Web

CHAPTER 19—Automated Web Publishing with VBA

Exploring Reasons to Use Macros to Publish
Using Web Publishing Strategies

Dynamic Updates
Extracting Data from Files
Don’t Forget Formatting
Keep a Backup
File Location
Module Design Preliminaries
Building Pages from Word and Excel Components

Using Modules to Edit and Format Web Pages

Finding Files That Contain Text Strings
Editing Selected Files

Publishing Pages

PART V—Advanced VBA Topics

CHAPTER 20—Working with Databases

Understanding When to Use External Data

MS Query
Data Access Objects
Database Types

Recording External Data Access from Excel and Word: MS Query

Discussion of MS Query
Accessing MS Query from Word
Accessing MS Query from Excel
Using Microsoft Query
Returning the Data to Word
Returning the Data to Excel
Reviewing the Recorded Macro

Working with Microsoft Jet
Working with ODBC
Developing a Solution with the Data Access Objects

Steps for Using DAO
Creating the Workspace
Opening a Database
Creating a Recordset
Accessing the Data
Navigating the Recordset
Searching a Recordset

CHAPTER 21—Using the Win32 API with VBA

Understanding the Win32 API

Understanding APIs
Understanding Dynamic Link Libraries
Knowing When to Use the Win32 API

Programming the Win32 API from VBA

Declaring a Routine
Using WIN32API.TXT
Understanding Parts of the Declaration
Understanding Functions and Subroutines
Specifying the Library Name
Understanding Aliases in Declarations
Understanding By Reference and By Value in Arguments
Specifying Strings as Parameters in Declarations
Testing Your Declaration
Calling a Win32 API Routine
Passing Strings as Return Parameters
Understanding Return Values from the Win32 API
Working with Handles

Working with the Registry

Understanding the Registry Organization
Understanding Registry Keys
Using VBA with the Win32 API and the Registry
Registry Declarations, Constants, and Data Types
Opening a Key
Retrieving a Key Value
Creating a Subkey
Setting a Subkey Value

CHAPTER 22—Securing Your VBA Projects

Exploring Available Security Options
Creating Module-Level Password Protection
Understanding Access Security Requirements

User-Level versus Share-Level Security
Storing Security Data

Removing VBA Code from Databases
Securing a Database
Using the Workgroup Administrator

Configuring the Security Database

Creating Permissions
Fine-Tuning Permissions

Providing Open Access
Protecting Individual Modules and Other Objects
Creating Modules for Administration

CHAPTER 23—Building VBA Help

Using Tip Text and What’s This? Help

Control ScreenTips
What’s This Help

Reviewing Help Features

Jumps
Searches
Pop-Up Definitions
Using Pictures

Building Help for Your Applications

Constructing Help
Finishing Touches
Testing and Building
Reviewing Help Authoring Tools

Integrating Help into Your Applications
Accessing the Host Application Help

PART VI—Techniques from the Pros

CHAPTER 24—Internet and Intranet Strategies

Understanding the Microsoft Internet Strategy

Current Internet Server Tools
Future Internet Server Tools

Exploring Web Development Tools

Internet Assistants and Office 97
Netscape Navigator Gold
Other Web Development Tools

Designing Internet Solutions

Planning a Web Site
Choosing Web Database Engines
Securing Your Web Site

Using Office Documents on an Intranet

Planning an Intranet Web Site
Choosing Appropriate Intranet Materials

CHAPTER 25—Implementing Internet and Intranet Security

Understanding Security Requirements

Why Is Security Important?
Information Integrity
System Availability
Information Confidentiality
User Identification
Recovering from a Security Breach

Developing Firewalls and Implementing Internet Security

Why Do I Need a Firewall?
The Firewall Security Concept
Firewall Security for Typical Internet Communications
Breaking into Your Own Intranet with SATAN

Where Can I Get More Information?

Concepts
FAQs
Newsgroups
Mailing Lists
UNIX Security Tools
Vendors
Security Bulletins
Security Policy
Operating System Patches
Firewalls

CHAPTER 26—The Role of VBA in Enterprise Development

VBA and Application Development
Understanding Enterprise Solutions

Using VBA to Leverage Your Enterprise Costs
Specialty System Deployment
Evolutionary System Deployment

Online Transaction Processing Systems and Decision Support Systems

Online Transaction Processing Systems
Decision Support Systems

Using VBA in OLTP Systems
VBA as a Data Exchange Medium
Using VBA in Decision Support Systems

MS Office as a Reporting Backbone
MS Project as a Reporting System
Schedule+ for Group Coordination

Integrating VBA with Other Languages

OLE Automation
Visual Basic
C and C++

Distributed Objects in an Enterprise System

The Automation Manager
The Remote Automation Connection Manager
The Component Manager

Multi-Tier Systems

Three-Tier systems
N-Tier Systems

Groupware

Business Processes
Routing Slips
Messaging Application Programming Interface (MAPI)
Outlook Forms Designer

Distributed System Management
The Role of VBA in Enterprise Computing

CHAPTER 27—Enterprise Solutions with BackOffice

Understanding BackOffice
The Components of BackOffice

Windows NT Server
SQL Server
Exchange Server
SNA Server
Systems Management Server

Integrating the Desktop with BackOffice

MAPI Interface to the Exchange Server
ODBC Access to SQL Server
SQL Distributed Management Objects (SQL-DMO) Connectivity to SQL Server

Moving Mission-Critical Databases to BackOffice

Determining What Information to Move from a Mainframe to a Client/Server System
File Server-Based Database
Access Upsizing Tools

Developing Scalable Applications to Meet Future Growth Needs

Multiuser Database Issues
Performance Monitoring

Planning Future Development with BackOffice

OLE Object Technology
Internet Services
BackOffice Telephony