Table of Contents

Introduction

Is This the Right Book?

So You Want to Be a Programmer. . . .

Why Bother?

What I Assume about You

Obligatory Typographical Conventions Section

Check Your Security Settings

How This Book Is Organized

Part I: Getting Started with Excel VBA Programming

Part II: How VBA Works with Excel

Part III: Programming Concepts

Part IV: Communicating with Your Users

Part V: Putting It All Together

Part VI: The Part of Tens

Icons Used in This Book

Getting the Sample Files

Now What?

Part I: Getting Started with Excel VBA Programming

Chapter 1: What Is VBA?

Okay, So What Is VBA?

What Can You Do with VBA?

Inserting a bunch of text

Automating a task you perform frequently

Automating repetitive operations

Creating a custom command

Creating a custom button

Developing new worksheet functions

Creating custom add-ins for Excel

Creating complete, macro-driven applications

Advantages and Disadvantages of VBA

VBA advantages

VBA disadvantages

VBA in a Nutshell

An Excursion into Versions

Chapter 2: Jumping Right In

First Things First

What You’ll Be Doing

Taking the First Steps

Recording the Macro

Testing the Macro

Examining the Macro

Modifying the Macro

Saving Workbooks That Contain Macros

Understanding Macro Security

More about the NameAndTime Macro

Part II: How VBA Works with Excel

Chapter 3: Working in the Visual Basic Editor

What Is the Visual Basic Editor?

Activating the VBE

Understanding VBE components

Working with the Project Window

Adding a new VBA module

Removing a VBA module

Exporting and importing objects

Working with a Code Window

Minimizing and maximizing windows

Creating a module

Getting VBA code into a module

Entering code directly

Using the macro recorder

Copying VBA code

Customizing the VBA Environment

Using the Editor tab

Using the Editor Format tab

Using the General tab

Using the Docking tab

Chapter 4: Introducing the Excel Object Model

Excel Is an Object?

Climbing the Object Hierarchy

Wrapping Your Mind around Collections

Referring to Objects

Navigating through the hierarchy

Simplifying object references

Diving into Object Properties and Methods

Object properties

Object methods

Object events

Finding Out More

Using VBA’s Help system

Using the Object Browser

Automatically listing properties and methods

Chapter 5: VBA Sub and Function Procedures

Subs versus Functions

Looking at Sub procedures

Looking at Function procedures

Naming Subs and Functions

Executing Sub procedures

Executing the Sub procedure directly

Executing the procedure from the Macro dialog box

Executing a macro by using a shortcut key

Executing the procedure from a button or shape

Executing the procedure from another procedure

Executing Function procedures

Calling the function from a Sub procedure

Calling a function from a worksheet formula

Chapter 6: Using the Excel Macro Recorder

Is It Live, or Is It VBA?

Recording Basics

Preparing to Record

Relative or Absolute?

Recording in absolute mode

Recording in relative mode

What Gets Recorded?

Recording Options

Macro name

Shortcut key

Store Macro In

Description

Is This Thing Efficient?

Part III: Programming Concepts

Chapter 7: Essential VBA Language Elements

Using Comments in Your VBA Code

Using Variables, Constants, and Data Types

Understanding variables

What are VBA’s data types?

Declaring and scoping variables

Working with constants

Pre-made constants

Working with strings

Working with dates

Using Assignment Statements

Assignment statement examples

About that equal sign

Smooth operators

Working with Arrays

Declaring arrays

Multidimensional arrays

Dynamic arrays

Using Labels

Chapter 8: Working with Range Objects

A Quick Review

Other Ways to Refer to a Range

The Cells property

The Offset property

Some Useful Range Object Properties

The Value property

The Text property

The Count property

The Column and Row properties

The Address property

The HasFormula property

The Font property

The Interior property

The Formula property

The NumberFormat property

Some Useful Range Object Methods

The Select method

The Copy and Paste methods

The Clear method

The Delete method

Chapter 9: Using VBA and Worksheet Functions

What Is a Function?

Using Built-In VBA Functions

VBA function examples

VBA functions that do more than return a value

Discovering VBA functions

Using Worksheet Functions in VBA

Worksheet function examples

Entering worksheet functions

More about using worksheet functions

Using Custom Functions

Chapter 10: Controlling Program Flow and Making Decisions

Going with the Flow, Dude

The GoTo Statement

Decisions, Decisions

The If-Then structure

The Select Case structure

Knocking Your Code for a Loop

For-Next loops

Do-While loop

Do-Until loop

Using For Each-Next Loops With Collections

Chapter 11: Automatic Procedures and Events

Preparing for the Big Event

Are events useful?

Programming event-handler procedures

Where Does the VBA Code Go?

Writing an Event-Handler Procedure

Introductory Examples

The Open event for a workbook

The BeforeClose event for a workbook

The BeforeSave event for a workbook

Examples of Activation Events

Activate and deactivate events in a sheet

Activate and deactivate events in a workbook

Workbook activation events

Other Worksheet-Related Events

The BeforeDoubleClick event

The BeforeRightClick event

The Change event

Events Not Associated with Objects

The OnTime event

Keypress events

Chapter 12: Error-Handling Techniques

Types of Errors

An Erroneous Example

The macro’s not quite perfect

The macro is still not perfect

Is the macro perfect yet?

Giving up on perfection

Handling Errors Another Way

Revisiting the EnterSquareRoot procedure

About the On Error statement

Handling Errors: The Details

Resuming after an error

Error handling in a nutshell

Knowing when to ignore errors

Identifying specific errors

An Intentional Error

Chapter 13: Bug Extermination Techniques

Species of Bugs

Identifying Bugs

Debugging Techniques

Examining your code

Using the MsgBox function

Inserting Debug.Print statements

Using the VBA debugger

About the Debugger

Setting breakpoints in your code

Using the Watch window

Using the Locals window

Bug Reduction Tips

Chapter 14: VBA Programming Examples

Working with Ranges

Copying a range

Copying a variable-sized range

Selecting to the end of a row or column

Selecting a row or column

Moving a range

Looping through a range efficiently

Looping through a range efficiently (Part II)

Prompting for a cell value

Determining the selection type

Identifying a multiple selection

Changing Excel Settings

Changing Boolean settings

Changing non-Boolean settings

Working with Charts

AddChart versus AddChart2

Modifying the chart type

Looping through the ChartObjects collection

Modifying chart properties

Applying chart formatting

VBA Speed Tips

Turning off screen updating

Turning off automatic calculation

Eliminating those pesky alert messages

Simplifying object references

Declaring variable types

Using the With-End With structure

Part IV: Communicating with Your Users

Chapter 15: Simple Dialog Boxes

UserForm Alternatives

The MsgBox Function

Displaying a simple message box

Getting a response from a message box

Customizing message boxes

The InputBox Function

InputBox syntax

An InputBox example

Another type of InputBox

The GetOpenFilename Method

The syntax for the GetOpenFilename method

A GetOpenFilename example

The GetSaveAsFilename Method

Getting a Folder Name

Displaying Excel’s Built-in Dialog Boxes

Chapter 16: UserForm Basics

Knowing When to Use a UserForm

Creating UserForms: An Overview

Working with UserForms

Inserting a new UserForm

Adding controls to a UserForm

Changing properties for a UserForm control

Viewing the UserForm Code window

Displaying a UserForm

Using information from a UserForm

A UserForm Example

Creating the UserForm

Adding the CommandButtons

Adding the OptionButtons

Adding event-handler procedures

Creating a macro to display the dialog box

Making the macro available

Testing the macro

Chapter 17: Using UserForm Controls

Getting Started with Dialog Box Controls

Adding controls

Introducing control properties

Dialog Box Controls: The Details

CheckBox control

ComboBox control

CommandButton control

Frame control

Image control

Label control

ListBox control

MultiPage control

OptionButton control

RefEdit control

ScrollBar control

SpinButton control

TabStrip control

TextBox control

ToggleButton control

Working with Dialog Box Controls

Moving and resizing controls

Aligning and spacing controls

Accommodating keyboard users

Testing a UserForm

Dialog Box Aesthetics

Chapter 18: UserForm Techniques and Tricks

Using Dialog Boxes

A UserForm Example

Creating the dialog box

Writing code to display the dialog box

Making the macro available

Trying out your dialog box

Adding event-handler procedures

Validating the data

Now the dialog box works

More UserForm Examples

A ListBox example

Selecting a range

Using multiple sets of OptionButtons

Using a SpinButton and a TextBox

Using a UserForm as a progress indicator

Creating a modeless tabbed dialog box

Displaying a chart in a UserForm

A Dialog Box Checklist

Chapter 19: Accessing Your Macros through the User Interface

Ribbon Customization

Customizing the Ribbon manually

Adding a macro to the Ribbon

Customizing the Ribbon with XML

Customizing Shortcut Menus

Commanding the CommandBars collection

Listing all shortcut menus

Referring to CommandBars

Referring to controls in a CommandBar

Properties of CommandBar controls

VBA Shortcut Menu Examples

Resetting all built-in, right-click menus

Adding a new item to the Cell shortcut menu

Disabling a shortcut menu

Creating a Custom Toolbar

Part V: Putting It All Together

Chapter 20: Creating Worksheet Functions — and Living to Tell about It

Why Create Custom Functions?

Understanding VBA Function Basics

Writing Functions

Working with Function Arguments

Function Examples

A function with no argument

A function with one argument

A function with two arguments

A function with a range argument

A function with an optional argument

Wrapper Functions

The NumberFormat function

The ExtractElement function

The SayIt function

The IsLike function

Functions That Return an Array

Returning an array of month names

Returning a sorted list

Using the Insert Function Dialog Box

Displaying the function’s description

Argument descriptions

Chapter 21: Creating Excel Add-Ins

Okay . . . So What’s an Add-In?

Why Create Add-Ins?

Working with Add-Ins

Add-In Basics

An Add-In Example

Setting up the workbook

Testing the workbook

Adding descriptive information

Protecting the VBA code

Creating the add-in

Opening the add-in

Distributing the add-in

Modifying the add-in

Part VI: The Part of Tens

Chapter 22: Ten VBA Questions (and Answers)

Chapter 23: (Almost) Ten Excel Resources

The VBA Help System

Microsoft Product Support

Internet Websites

Excel Blogs

Google

Bing

Local User Groups

My Other Books

Chapter 24: Ten VBA Do’s and Don’ts

Do Declare All Variables

Don’t Confuse Passwords with Security

Do Clean Up Your Code

Don’t Put Everything in One Procedure

Do Consider Other Software

Don’t Assume That Everyone Enables Macros

Do Get in the Habit of Experimenting

Don’t Assume That Your Code Will Work with Other Excel Versions

Do Keep Your Users in Mind

Don’t Forget About Backups