VBA For Dummies

Not all applications instantly give developers what they need out of the box. VBA For Dummies, 3rd Edition makes it easy for programmers to customize software from Microsoft and others with Visual Basic for Applications.

b>Contents

Introduction

What the Book Covers
I Shouldn't Make Any Assumptions . . .
Divide and Conquer
Part I: VBA Boot Camp
Part II: VBA Programming Essentials
Part III: Stretching Out
Part IV: The Part of Tens
Bonus Part: Extras
How to Use the Examples
What's on the CD-ROM
Icons Used in This Book
The VBA For Dummies Web Site(s)

Chapter 1: Don't Sweat the Technical Stuff -- and It's All Technical Stuff
Why Bother with VBA?
VBA Is the Steering Wheel, but You Still Need the Car
Non-Microsoft VBA applications
The range of VBA applications
Programming with VBA
What's so visual about Visual Basic for Applications?
The main events
The programming cycle
More about What You Can Do with VBA
Customizing the user interface
Adding new features
Creating advanced custom programs
Not Your Mama's BASIC
The VBA integrated development environment
Macros and VBA
Controls -- ActiveX and otherwise
Object-oriented programming with VBA
VBA is a standard -- sort of
VBA 5 versus VBA 6
Other VBA dialects
VBA versus Visual Basic

Chapter 2: Don't Program When You Can Record a Macro
Understanding How Macros Work
When to record macros
Macros feel good, but they lead to the hard stuff
Planning a Macro
Recording Macros
Starting the macro recorder
Naming the baby
Making other choices in the Record Macro dialog box
Button, button, who's got the button?
Starting the macro recorder
Recording commands
Stopping and pausing the macro recorder
Running macros
Modifying Macros -- the Easy Way to Write Code
Editing macro code in the Visual Basic Editor
Making easy macro modifications

Chapter 3: Basic VBA Programming
Starting the Visual Basic Editor
One-click access to the Visual Basic Editor
A peek into the Visual Basic Editor
I Need Help!
411 for VBA -- or is it 911?
Getting Help for your VBA application
Searching the Help haystack
Context-sensitive Help
Understanding what's special about VBA Help topics
Installing all the Help files . . .
Creating a VBA Program
Step 1: Designing the sample program
Step 2: Implementing the design
Step 3: Testing your program
Step 4: Debugging
Running a Form from Other VBA Code
Returning to Your VBA Application

Chapter 4: Running VBA Programs
The Name Is Everything
Running from the Macros Dialog Box
Displaying the Macros dialog box
Running a macro
Finding your macro in the Macros dialog box
Launching Programs Quickly
Push button programs
Keyboard shortcuts in Word, Excel, and Access
Macros in Visio
Running VBA Programs Automatically

Chapter 5: The Visual Basic Editor at Your Command
Working with the Visual Basic Editor User Interface
Out to lunch with Editor menus
Tuning the toolbars
Customizing your toolbars and menus
Keyboard shortcuts
Managing the Windows
Some windows are loners; some run in crowds
Viewing and hiding windows
Docking windows, or not
Saving the screen layout
Managing Your Projects with Project Explorer
Opening the Project Explorer window
Exploring the Explorer
Setting project properties
Using the Object Browser
Starting the Object Browser
Browsing objects
Searching for members
Using browsed items in your code
Coding Secrets
Opening Code windows
Creating new Code windows
Typing code
More Code window creature comforts
Using the Properties Window
Invoking the Properties window
Renaming a project or module in the Properties window
A First Look at the Debugging Windows

Chapter 6: Anatomy of a Great VBA Program
Program Building Blocks
Programs defined
A sample program
The VBA hierarchy
Where Do Projects Come from, Mommy?
Totally Modular
Planning your modules
Adding a new module to a VBA project
What goes where in a new module
Standard modules and modules with class
Writing Procedures
Procedure skeletons
Creating a new procedure
Sub procedures in detail
Function procedures in detail
Winning arguments
Organizing your procedures
Scoping Out the Scope
Specifying a procedure's scope
Using private procedures
Making Statements
Declarations
Assignment statements
Executable statements
Compiler options
Naming Your Babies
VBA naming conventions
Making Your Code Pretty
Judicious indenting helps organize your code
Space-iousness is good
Don't scroll if you don't have to!
Remarks About Comments
How to make comments
When to use comments
Long-winded comments

Chapter 7: Storing and Modifying Information
Working with Variables
Declaring variables: The overview
Where to declare variables
When to declare variables
Choosing and using data types
Specifying a variable's scope
Declaring multiple variables on the same line
Giving variables their values
Expression-ism
Working with Values Constant-ly
Declaring constants
Reaping the benefits of a constant approach
Using constants to represent attributes
Hello, Operators
Taking precedence
Performing calculations with arithmetic operators
Comparing values
Stringing text together
Details on Data Types
Converting between data types
Understanding variants
Choosing a numeric data type
When to use Boolean variables
Working with currency values
Working with dates
Stringing you along

Chapter 8: Controlling the Flow
Controlling Wild Programs with Control Structures
Control structure anatomy
Nested control structures
Use indents!
The Road Taken: Using Condition Expressions
How condition expressions work
Condition expressions without comparison operators
Using logical operators in conditions
Using If...Then Statements
The basic form: If...Then
One-liners with If...Then
If...Then...Else statements
If...Then complexities: The introduction
Using Select Case Statements
Testing conditions in Select Case statements
A sample Select Case statement
The Case Else clause
More about Case clause tests . . . a lot more
Repeating Yourself with Loops
Do-ing loops
Repeating on count with For...Next loops
Interrupting the Flow with GoTo
A GoTo example
GoTo caveats

Chapter 9: Unbreakable Code: Debugging and Error Trapping
What Can Go Wrong Does Go Wrong
Fixing Syntax Errors
Entomology for VBA Programmers
Test, test, test
Keyboard shortcuts for debugging
Take a break
Steppin' through code
Major manipulations during debugging
Seeing Data Tips
Immediate Gratification
Keeping an Eye on the Locals (Window)
Local mechanics
Why edit variable values?
How to edit variable values
The Watch Window: Key Debugging Tool
Adding watch expressions
Working with the Add Watch window
Editing watch expressions
Using watch expressions to define breakpoints
Trapping Wild Bugs with On Error and the Error Object
Where run-time errors come from
How error-handling code works
How to write error-handling code

Chapter 10: Creating Interactive VBA Forms
Designing Forms
Running forms
Forms and controls are objects -- and they must be programmed
Planning forms for your program
Printing forms during the design process
Laying out a new form
Working with form and control properties
Working with key form and control properties
Formatting controls
Controls, Behave Yourself!
Using the Enabled and Locked properties
Setting the tab order for controls
Assigning accelerator keys
Sending messages with label controls
Collecting information with text boxes
Getting things done with Command buttons
I've been framed!
Picking one item from a group with option buttons
Turning options on or off with check boxes and toggle buttons
Selecting options with list and combo boxes
Form Programming
If you've got it, flaunt it!
Loading and showing forms
The main events
Click here . . .
Writing code for the Change event
Handling common form programming tasks
Validating Entries

Chapter 11: Built-in VBA Commands
Finding Built-In Commands
Formatting Data
Working with the Format function
Using prefab formats with the Format function
Specifying custom formats
Using the IIf function
Working with other formatting functions
Converting Data
Working with hex and octal values
Converting between numbers and strings
Con Text
Removing characters at the end of a string with Len and Left
Extracting characters from within a string
Using character codes in string expressions
Working with Dates and Times
A date with time
Calculating with dates
Simple Interactions with the World
Displaying message boxes
Obtaining user input
Working with Boolean values
Fun with Math and Money
Math functions
Financial functions
Miscellaneous Built-In Commands

Chapter 12: Object-Oriented Programming
So What's an Object?
Objects as components of VBA applications
Objects conceptualized
A practical definition of objects
Object classes versus specific objects
Collection objects
What's an Object Model?
Understanding why the object model is important
Extending the object model
VBA Forms Are Objects, Too
Using Objects in Code: An Introduction
The basic rule about object programming
Using object models
Getting and Changing Object Properties
What you can't do with some properties
Property settings are data
Retrieving a property's current setting
Changing a property's setting
Default properties
Objects as properties
Climbing the family tree
Method Acting
Comprehending Events
Identifying the Object You Want to Work With
Understanding object expressions
Creating object variables
Creating new objects
What the definition of Is is, really
Efficient Object Coding
Using With statements
Using For Each...Next structures

Chapter 13: Baskets for Data Storage: Arrays and Collections
Seeing A-rray of Hope
Referring to the items in an array
Understanding array data
Using arrays of multiple dimensions
Declaring arrays
Addressing elements in an array
Assigning data to an array . . . in bulk
Copying one array to another
Managing Sets of Data with Collection Objects
Weighing the trade-offs in using collections
Creating Collection objects
Adding data to a collection
Removing items
Count-ing your collection
Accessing items in a collection
Using collections for database-type data
Defining Your Own Data Types
Understanding user-defined data types
Declaring a user-defined data type
Declaring variables of a user-defined type
Working with information in a user-defined type
Working with variables of a user-defined type as a whole

Chapter 14: Ten (Minus Three) Really Cool Things You Can Do with VBA

Chapter 15: Ten (Times Three) VBA Resources Appendix: About the CD Bonus Part: Extras (On the CD-ROM) CD-1 Bonus Chapter A: VBA Programming in Office CD-3
Taking Control of Toolbars and Menus CD-3
Displaying and positioning toolbars CD-4
Customizing toolbar buttons CD-5
Programming the Office Assistant CD-8
Pulling the Assistant's strings CD-8
Programming the Assistant's balloon CD-9
Storing Document Variables CD-13
Storing and retrieving variable data in Excel, PowerPoint, and Project CD-13
Other solutions for storing variable data CD-15

Bonus Chapter B: VBA Programming in Word CD-17
Understanding the Application Object CD-17
Accessing Word Documents in VBA CD-18
Working with the active document CD-18
Specifying a particular document CD-18
Creating, opening, activating, and closing documents CD-19
Working with document sections CD-20
Opening Windows with VBA CD-20
Specifying windows in code CD-20
Working with window panes CD-21
Changing window appearance CD-21
Using the View object CD-22
Zooming in code CD-22
Working with the Selection Object CD-23
Understanding Range Objects CD-25
Using the Range property CD-25
Defining your own ranges by using the Range method CD-26
Working with Text in Word VBA CD-27
Selecting ranges, and creating ranges from selections CD-28
Redefining ranges and selections CD-28
Deleting, copying, and pasting text CD-30
Inserting new text CD-31
Formatting text CD-32
Finding and Replacing in Word VBA CD-33
Working with found text CD-33
Replacing text or formatting CD-34
Finding and replacing formatting CD-34
Using Document Variables CD-35

Bonus Chapter C: VBA Programming in Excel CD-37
Navigating the Excel Object Model CD-37
Working with Cells in Code Using Range Objects CD-38
Specifying a Range object CD-39
Using the Cells property to define a range CD-40
Acting on cells en masse CD-41
Acting on individual cells in a range CD-42
Working with selections CD-42
Programming Custom Worksheet Functions CD-45
Writing custom worksheet functions CD-45
Running custom functionsCD-46
Testing your custom functions CD-47
Fancier functions CD-48
Using Built-In Functions in Your Code CD-48
Programming for Excel Events CD-49
Choosing the right object CD-49
Starting an event procedure CD-49
Reacting to worksheet changes CD-50
Programming dynamic charts CD-52

Bonus Chapter D: Database Programming CD-53
Backgrounder on VBA Database Programming Technology CD-53
About database engines CD-53
SQL and VBA CD-54
All about database objects CD-55
Some related database technologies CD-56
Database programming choices CD-56
Database development options CD-57
Database programming in Access CD-58
Writing Database Code with ADO CD-58
Error handling CD-58
Creating a reference to ADO in your project CD-59
Establishing the connection CD-59
Working with Recordset objects CD-60
Using the Command object CD-67
Working with SQL CD-69
Avoiding SQL CD-69
Understanding SQL dialects CD-70
Inserting SQL statements in VBA code CD-70
Writing SELECT statements CD-71
Performing bulk updates and deletions in SQL CD-77

Bonus Chapter E: Working with Disk Files CD-79
The Number Is the Key CD-79
Choosing a file access mode CD-80
Not playing the numbers game CD-81
Closing the files you open CD-81
Reading and writing data CD-82

Bonus Chapter F: More on VBA Forms CD-83
More on Form and Control AppearanceCD-83
Choosing colors CD-83
Selecting fonts CD-84
Easy mouse tricks CD-85
Adding graphics CD-87
Making other formatting choices CD-88 - CD-89
Text box tips CD-90
Organizing forms with multipage controls CD-92
A tip for option buttonsCD-94
Check boxes CD-94
Selecting values with scroll bars and spin buttons CD-95
More on Form Programming CD-97
Referring to forms using variables CD-97
Detecting keystrokes CD-98
Advanced validation techniques CD-99

Index