Teach Yourself Visual Basic 5 for Applications in 21 Days
http://www.empowermentzone.com/vba21day.zip
Table of Contents
Part 1--At A Glance
What's Ahead?
Chapter 1--Getting Started
Macros and Programming Languages
What Is a Macro?
A Brief History of Visual Basic
for Applications
Why Learn Visual Basic for Applications?
Recording New Macros
Setting Up a Macro's Starting
Conditions
Starting the Macro Recorder and
Naming the Macro
Recording a Word 97 Macro
Setting Up the Starting
Conditions
Naming the Macro and
Selecting a Storage
Location
Selecting Toolbar and
Keyboard Options
Recording Your Actions
Stopping the Macro
Recorder
Recording an Excel 97 Macro
Setting Up the Starting
Conditions
Naming the Macro and
Selecting a Storage
Location
Recording Your Actions
Stopping the Macro
Recorder
Macro Source Code
Running Macros
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 2--Writing and Editing Simple Macros
Understanding the Visual Basic for
Applications Environment
Understanding Modules
Word 97 Modules
Excel 97 Modules
Examining the Visual Basic
Editor
Starting the VB Editor
Understanding the VB
Editor's Windows
The Project Explorer
Window
The Code Window
Introducing the VB
Editor's Menus
The File Menu
The Edit Menu
The View Menu
The Insert Menu
The Format Menu
The Debug Menu
The Run Menu
The Tools Menu
Other Menus
Introducing the VB
Editor's Toolbar
The Standard
Toolbar
The Edit Toolbar
Editing Macros
Displaying a Module
Finding Recorded Macros
Using the Object
Browser's Search Feature
Listing Project Modules
with the Object Browser
Parts of a Recorded Macro
Editing Macro Text
Moving or Copying a Macro from
One Module to Another
Saving and Importing Modules as
Text Files
Exporting Modules
Importing Modules
Removing Modules from a Project
Writing New Macros and Procedures
Inserting and Renaming a Module
Selecting an Existing Module
Writing the Procedure Text
Running a Procedure While Editing
Displaying Messages to a Procedure's
User
Understanding Error Messages While
Writing, Editing, or Running a Procedure
Syntax Errors
Runtime Errors
Printing Your Source Code
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 3--Understanding Data Types, Variables,
and Constants
Examining Visual Basic Data Types
Dates
Numbers
Byte, Integer, and Long
Integer Data
Floating-Point Numbers
The Currency Data Type
Text Strings
Logical Values
Variant Data
Understanding Variables
What Is a Variable?
Choosing Variable Names
Creating Variables
Scope: Determining Which
Variables Are Available
Procedure-Level Scope
Module-Level Scope
Using Variables with the
Same Name at Different
Scope Levels
Persistence: Determining How
Long Variables Retain Their
Value
Requiring Explicit Variable
Declaration
Specifying the Data Type of a
Variable
Using Dim to Declare
Typed Variables
Using Type-definition
Characters to Declare
Typed Variables
Using Dim to Declare
Fixed-Length String
Variables
Understanding Constants
Creating Named Constants
Constant Scope
Writing Literal Constants
String Constants
Numeric Constants
Date Constants
Boolean Constants
Specifying the Data Type of a
Constant
Understanding Intrinsic
Constants
Using the Object Browser
to Find Available
Intrinsic Constants
Getting Data from Your
Procedure's User
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 4--Operators and Expressions
Understanding Operators and Expressions
Data Type Compatibility
Visual Basic's Automatic Data
Conversions
Numeric Type Conversions
String and Number Conversions
Boolean Conversions
Date Conversions
The Assignment Operator (=)
Arithmetic Operators
Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
Integer Division (\)
Modulo Division (Mod)
Exponentiation (^)
Comparison Operators
String Comparisons
Binary and Text String
Comparison
Choosing the String
Comparison Technique
The Like Operator
The Is Operator
Logical Operators
Understanding Truth Tables
And
Or
Not
Xor
Eqv
Imp
String Concatenation Operators
Using String Concatenation
String Concatenation Operators
The Preferred
Concatenation Operator:
(&)
The Addition Operator
Used for String
Concatenation: (+)
Understanding Operator Precedence and
Complex Expression Evaluation
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 5--Using Visual Basic and Host
Application Functions
Understanding Functions
Using Functions in Assignments
and Expressions
Understanding Function Arguments
and Function Results
Ignoring a Function's Result
Using a Function's Named
Arguments
Using Visual Basic for Applications'
Functions
Mathematical Functions
Data Conversion Functions
Date and Time Functions
User Interaction Functions
InputBox
MsgBox
String Functions
Disk, Directory Information, and
Other Functions
Using the Host Application's Functions
Using the Object Browser to Insert
Function Calls
Viewing and Inserting Visual
Basic's Functions
Viewing and Inserting a Host
Application's Functions
Using Functions to Manipulate Strings
Removing Extraneous Space
Characters
Determining the Length of a
String
Comparing and Searching Strings
Using the StrComp
Function
Using the InStr Function
Breaking a String into Smaller
Parts
The Left Function
The Right Function
The Mid Function
Using String Characters You
Cannot Type at the Keyboard
Formatting Data Values
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 6--Creating and Using Function
Procedures and User-Defined Functions
Understanding Function Procedures and
User-Defined Functions
Creating Function Procedures
Writing a Function Procedure
Creating User-Defined Functions
for Excel
Declaring a Data Type for a
Function's Result
Declaring Data Types for a
Function's Arguments
Creating Optional Arguments
Specifying an Optional
Argument's Data Type
Specifying a Default
Value for Optional
Arguments
Understanding and Controlling
How VBA Passes Arguments
Understanding Arguments
Passed by Reference and
by Value
Specifying Whether to
Pass an Argument by
Reference or by Value
Using Function Procedures in Visual
Basic for Applications
Using the Object Browser to Find
Your Function Procedures
Entering a Function Procedure
Description with the Object
Browser
Using User-Defined Functions in Excel
Worksheets
Designing Function Procedures and
User-Defined Functions
Designing Functions for Excel
Understanding Recursion
Analyzing a Recursive Function's
Operation
Avoiding Accidental Recursion
and Other Recursion Problems
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 7--Understanding Objects and Collections
Understanding Objects
Object Properties
Object Methods
Object Classes
Using Objects
Using Object Properties
Using Object Methods
Declaring Object Variables
Using Objects in Expressions and
Assignments
Referring to Objects by Using
With...End With
Working with Object Collections and
Object Containers
Adding to Collections
Referring to Specific Objects in
a Collection or Container
Using the Object Browser with Objects,
Methods, and Properties
Summary
Q&A
Workshop
Quiz
Exercises
Part 1--In Review
Review for Excel 97
Review for Word 97
Part 2--At A Glance
What's Ahead?
Chapter 8--Making Decisions in Visual Basic for
Applications
Understanding Visual Basic for
Applications' Decision-Making Commands
Making Simple Choices
Choosing a Single Branch Using
If...Then
Choosing Between Branches Using
If...Then...Else
Making Complex Choices
Nested If...Then Statements
Using If...Then...ElseIf
Using the Select...Case
Statement
Unconditional Branching
Ending Procedures, Functions, and Entire
Programs Early
Using the Exit Statement
Using the End Statement
Using MsgBox to Let Users Make Choices
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 9--Repeating Actions in Visual Basic:
Loops
Understanding Looping Commands
Fixed Repetition: The For Loops
Using the For...Next Loop
Using For...Next with a
Count That Goes Up
Using For...Next with a
Count That Goes Down
Putting the For...Next
Loop to Work
Using the For Each...Next Loop
Flexible Repetition: The Do Loops
Understanding How Visual Basic
for Applications Tests the Loop
Determinant
Using Loops That Test Conditions
Before Execution
Building Loops with Do
While
Building Loops with Do
Until
Using Loops That Test Conditions
After Execution
Building Loops with
Do...Loop While
Building Loops with
Do...Loop Until
Ending Loops Early
Nesting Loops
Nesting For Loops
Nesting Do Loops
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 10--Data Types and Variables: Advanced
Topics
Getting Information about Variables and
Expressions
Using Visual Basic for
Applications' Data Information
Functions
Determining the Specific Data
Type of a Variable or Expression
Understanding the
TypeName Function's
Results
Understanding the
VarType Function's
Results
Understanding the Special Empty
and Null Values
The Empty Value
The Null Value
Defensive Programming: Preventing Errors
Before They Happen
Checking Arguments and Other
Internal Values
Validating User Input
Validating Other Input
Preserving Variables Between Function
and Procedure Calls
Creating Your Own Data Types
Defining a User-Defined Data
Type
Declaring Variables That Have a
User-Defined Type
Using Variables with
User-Defined Types
Creating Your Own Program Objects
Understanding Class Modules
Designing an Object Class
Creating a User-Defined Class
Inserting and Renaming a
Class Module insert and
rename a class module,
follow these steps:
Writing the Class
Property and Method
Definitions
Using a User-Defined Class
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 11--Creating Libraries and Whole
Programs: Modular Programming Techniques
Using Modules More Effectively
Creating Procedure and Function
Libraries
Creating an Excel
Library Workbook
Creating a Word Library
Template
Making Library Procedures and
Functions Available
Using the Startup Folder
to Load a Library
Using References to
Access Library
Procedures and Functions
Advanced Scope Rules for Cross-Module
Programming
Understanding Private and Public
Scope
Overriding Visual Basic for
Applications' Scope Rules
Making Entire Modules
Private to a Single
Project
Using the Private
Keyword to Limit Scope
to a Single Module
Using the Public Keyword
to Increase Scope to All
Modules
Understanding and Avoiding
Circular References
Understanding and Using Module
Qualifiers
Understanding Structured Programming
Techniques
Understanding Procedures That
Call Other Procedures
Top-Down Design and Step-Wise
Refinement
Using Modules to Organize Your
Programming Projects
Using Procedure Arguments to Communicate
Data Between Procedures
Understanding When and Why You
Should Use Argument Lists with
Your Procedures
Specifying a Procedure's
Argument List
Using Procedures That Have
Arguments
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 12--Managing Files with Visual Basic for
Applications
Understanding File Management
What Is File Management?
Reviewing Visual Basic for
Applications' File Management
Capabilities
Working with File Attributes
Understanding File Attributes
Getting a File's Attributes
Changing a File's Attributes
Getting or Finding Filenames
Using the Dir Function to Find
Files
Using Excel's Built-In Dialogs
to Get Filenames
Using the
GetOpenFilename Method
Using the
GetSaveAsFilename Method
Using Word's Built-In Dialogs to
Get Filenames
Using Word's File Open
Dialog Box
Using Word's File Save
As Dialog Box
Working with Disk Drives and Folders
Getting the Current Folder Path
and Drive Letter
Changing the Current Folder
Changing the Current Disk Drive
Creating Disk Folders
Removing Disk Folders
Copying and Deleting Files
Copying a File
Deleting a File
Renaming or Moving Files
Getting Information about Files
Getting a File's Time and Date
Stamp
Getting the Length of a File
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 13--Arrays
Understanding Arrays
Understanding Single-Dimensional
Arrays
Understanding Multi-Dimensional
Arrays
Static and Dynamic Arrays
The Option Base Statement
Declaring Arrays
Using Arrays
Using ReDim with Dynamic Arrays
The LBound and UBound Functions
Using Erase to Clear or Remove Arrays
Using Arrays as Arguments to Procedures
and Functions
Sorting Arrays
Searching Arrays
Using Linear Searches
Using Binary Searches
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 14--Debugging and Testing VBA Code
Basic Types of Program Bugs
Using the Break Mode
Entering Break Mode from an
Error Dialog
Setting and Using Breakpoints
Using the Stop Statement
Entering Break Mode Using the
Step Into Command
Entering Break Mode by
Interrupting Code Execution
Ending Break Mode
Using the Step Into Command
Using the Step Over Command
Using the Step Out and Run To Cursor
Commands
Understanding and Using Watched
Variables
Adding a Watch Expression
Editing a Watch Expression
Deleting a Watch Expression
Using Quick Watch
Using Data Tips
Using the Locals Window
Tracing Procedure Calls
Using the Immediate Window
Using the Debug.Print Statement
Summary
Q&A
Workshop
Quiz
Exercises
Part 2--In Review
Review for Excel 97
Review for Word 97
Part 3--At A Glance
What's Ahead?
Chapter 15--Creating Custom Dialog Boxes
Understanding User Forms
Understanding Controls
Creating Custom Dialog Boxes
Adding a New User Form Object
Using the Toolbox Toolbar
Adding Controls to a Form
Editing Form Controls
Selecting Controls
Moving a Control
Changing a Control's
Size
Copying, Pasting, and
Deleting Controls
Editing or Formatting a
Control's Caption
Controlling the Tab Order
Setting Form and Control
Properties In Design Mode
Displaying User Forms with VBA
Using VBA with Controls on a Form
Writing Event Procedures and
Other Form Code
Putting It Together: A
Self-Contained Dialog
Putting It Together: A Dialog
That Uses Code in a Standard
Module
Using List Controls
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 16--Menus and Toolbars
Understanding Command Bars
Command Bar Types
The Parts of a Command Bar
Command Bars as Menu
Bars
Command Bars as Toolbars
Types of Command Bar Controls
Command Bar Object Methods and
Properties
The CommandBars Collection
Object
The CommandBar Object
Understanding Built-In and Custom
Command Bars
Custom Command Bars
Storing Custom Command
Bars in Excel
Storing Custom Command
Bars in Word
Built-In Command Bars
Listing Available Built-In
Command Bars
Command Bar Control Objects, Methods,
and Properties
The CommandBarControls
Collection
The CommandBarControl Object
The CommandBarButton
Control Object
The CommandBarPopup
Control Object
The CommandBarComboBox
Control Object
Built-In Command Bar Controls
Command Bar Control Faces
Managing Custom and Built-In Command
Bars
Adding a New Command Bar
Hiding, Displaying, and
Positioning a Command Bar
Deleting a Command Bar
Resetting a Built-In Command Bar
Managing Command Bar Controls
Adding Controls to a Command Bar
Naming or Renaming a Command Bar
Control
Specifying a Control's Event
Procedure
Deleting Command Bar Controls
Enabling or Disabling a Command
Bar Control
Controlling the State of a
Command Bar Control
Finding Specific Controls
Putting It Together: A Menu-type Command
Bar
Putting It Together: A Toolbar-type
Command Bar
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 17--Error Handling
Strategies for Error Handling
The On Error GoTo Statement
The Resume Statement
Finding the Runtime Error's Type,
Message, and Location
Determining the Runtime Error
Type: Using the Err Function
Getting the Runtime Error
Message Text: Using the Error
Function
Determining the Runtime Error
Location: Using the Erl Function
Forcing Runtime Errors and Creating
User-Defined Error Codes: The Error
Statement
Using the Err Object
Putting It Together: Examples of Error
Handling
Handling Fatal Errors
Resolving Runtime Errors without
Halting
Retrying the Error-Causing
Statement
Resuming Execution at a
Specified Point
Forcing a Runtime Error
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 18--Controlling the VBA Host
Applications
Working with Excel 97
Working with Workbook Objects
Returning a Workbook
Object
Opening a Workbook
Creating a New Workbook
Activating a Workbook
Saving a Workbook
Closing a Workbook
Working with Worksheet Objects
Returning a Worksheet
Object
Activating a Worksheet
Creating a New Worksheet
Renaming a Worksheet
Copying and Moving a
Worksheet
Deleting a Worksheet
Methods That Return Range
Objects
Using the Range Method
Using the Cells Method
Using the Offset Method
Other Methods and
Properties That Return
Ranges
Working with Cells and Ranges
Selecting a Cell or
Range
Working with Values and
Formulas
Defining a Range Name
Cutting, Copying, and
Clearing Data
Working with Word 97
Working with Document Objects
Returning a Document
Object
Opening a Document
Creating a New Document
Activating a Document
Saving a Document
Closing a Document
Working with Template Objects
Creating Templates
Loading and Unloading
Global Templates
Attaching a Template
Understanding the Document
Object's Components
Specifying a Range
Obtaining a Range
Reference
Redefining a Range
Working with the Selection
Object
Positioning or Collapsing
Selection and Range Objects
Inserting Text
Cutting, Copying, Pasting, and
Deleting Text
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 19--Working with Other Applications: OLE
and Automation
What Is OLE?
A Brief History of OLE
How Does Visual Basic for
Applications Fit In?
Looking Up an Object's Class Type
Using the Windows 95 Registry
Starting the Registry
Editor
Finding Object Classes
in the Registry Database
Using the System Information
Applet
Adding Linked and Embedded Objects
Understanding the Shape and
OLEFormat Objects
Using the AddOLEObject Method of
the Shapes Collection
Inserting a New Embedded Object
Inserting an Existing File as an
Embedded Object
Inserting an Existing File as a
Linked Object
Excel 97's OLEObjects Collection
Word 97's InLineShapes
Collection
Working with Linked and Embedded Objects
Accessing OLE Objects
Using OLE Object Properties
Using OLE Object Methods
The Update Method
The Verb and DoVerb
Methods
Using Automation
Accessing Automation Objects
Accessing Objects Directly
Creating a New Automation Object
Using the CreateObject
Function
Using Referenced Object
Libraries
Accessing an Existing Automation
Object
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 20--Working with Other Applications:
DDE, DLLs, and Sending Keystrokes
Starting Another Application
Activating a Running Application
Using Dynamic Data Exchange
DDE: The Basics
Initiating and Terminating a
Link to the DDE Server
Controlling the Server
Application
Exchanging Data with the DDE
Server Application
Receiving Data from the
DDE Server
Sending Data to the DDE
Server
Sending Keystrokes to Another
Application
Accessing DLLs from Visual Basic for
Applications
Declaring DLL Procedures
Some DLL Examples
Beeping the Speaker
Determining the Windows
Folder
Summary
Q&A
Workshop
Quiz
Exercises
Chapter 21--Using Event Procedures and Add-Ins
What Are Events and Event Procedures?
Understanding Where Event
Procedures Are Stored
Event Procedure Names and
Declarations
Event Procedure Arguments
Working with Application Object Events
Working with Excel Object Events
The Open Event
The BeforeClose Event
The Activate Event
The Deactivate Event
The BeforeDoubleClick Event
The Change Event
The Calculate Event
Other Events
Working with Excel's Event-Related
Properties and Methods
Excel's OnWindow Property
Excel's OnKey Method
Excel's OnTime Method
Working with Word Object Events
The Open Event
The Close Event
The New Event
The DocumentChange Event
The Quit Event
Working with Word's Event-Related
Collections and Methods
The KeyBindings Collection
Word's OnTime Method
Working with Add-In Applications
Creating an Add-In Application
Controlling Add-Ins with Visual
Basic
Summary
Q&A
Workshop
Quiz
Exercises
Part 3--In Review
Excel VBA Review
Word VBA Review
Appendix A
Index
VICUG-L is the Visually Impaired Computer User Group List.
To join or leave the list, send a message to
[log in to unmask] In the body of the message, simply type
"subscribe vicug-l" or "unsubscribe vicug-l" without the quotations.
VICUG-L is archived on the World Wide Web at
http://maelstrom.stjohns.edu/archives/vicug-l.html
|
