Table of Contents
Acknowledgments xi
Introduction xiii
Chapter 1 Excel Macros: A Quick Start in Excel VBA Programming 1
Macros and VBA 2
Excel Macro-Enabled File Formats 2
Macro Security Settings 3
Enabling the Developer Tab in Excel 6
Using the Built-in Macro Recorder 8
Planning a Macro 8
Recording a Macro 9
Editing Recorded Macros 15
Macro Comments 20
Cleaning up the Macro Code 21
Running a Macro 23
Testing and Debugging a Macro 23
Saving and Renaming a Macro 25
Printing Macro Code 26
Improving Your Recorded Macros 26
Creating a Master Macro 28
Various Methods of Running Macros 29
Running the Macro Using a Keyboard Shortcut 29
Running the Macro from the Quick Access Toolbar 30
Running the Macro from a Worksheet Button 34
Summary 36
Chapter 2 Excel Programming Environment: A Quick Overview of its Tools and Features (VBE) 37
Understanding the Project Explorer Window 38
Understanding the Properties Window 39
Understanding the Code Window 40
Setting the VBE Options 41
Syntax and Programming Assistance 42
List Properties/Methods 42
List Constants 43
Parameter Info 44
Quick Info 45
Complete Word 45
Indent/Outdent 45
Comment Bio ck/Un comment Block 46
Using the Object Browser 46
Locating Procedures with the Object Browser 52
Using the VBA Object Library 52
Using the Immediate Window 54
Obtaining Information in the Immediate Window 57
Working with Worksheet Cells and Ranges 58
Using the Range Property 59
Using the Cells Property 59
Using the Offset Property 60
Using the Resize Property 61
Using the End Property 63
Moving, Copying, and Deleting Cells 63
Working with Rows and Columns 64
Obtaining Information about the Worksheet 65
Entering Data and Formatting Cells 65
Returning Information Entered in a Worksheet 66
Finding Out about Cell Formatting 66
Working with Workbooks and Worksheets 67
Working with Windows 69
Working with the Excel Application 70
Summary 71
Chapter 3 Excel VBA Fundamentals: A Quick Reference to Writing VBA Code 73
Excel Objects, Properties, and Methods 73
Microsoft Excel Object Model 75
Writing Simple and Complex VBA Statements 76
Breaking Up Long VBA Statements 80
Saving Results of VBA Statements 80
Introducing Data Types 81
Using Variables 83
How to Create Variables 84
How to Declare Variables 85
Specifying the Data Type of a Variable 88
Assigning Values to Variables 90
Forcing Declaration of Variables 94
Understanding the Scope of Variables 96
Procedure-Level (Local) Variables 96
Module-Level Variables 96
Project-Level Variables 98
Lifetime of Variables 99
Finding a Variable Definition 99
Determining a Data Type of a Variable 99
Using Constants 101
Built-in Constants 102
Converting between Data Types 103
Using Static Variables in VBA Procedures 106
Using Object Variables in VBA Procedures 107
Using Specific Object Variables 110
Summary 110
Chapter 4 Excel VBA Procedures: A Quick Guide to Writing Function Procedures 111
Understanding Function Procedures 112
Creating a Function Procedure 112
Various Methods of Running Function Procedures 114
Running a Function Procedure from a Worksheet 115
Running a Function Procedure from Another VBA Procedure 117
Ensuring Availability of Your Custom Functions 117
Passing Arguments to Function Procedures 118
Specifying Argument Types 119
Passing Arguments by Reference and by Value 121
Using Optional Arguments 123
Testing a Function Procedure 124
Locating Built-in Functions 125
Getting to Know the MsgBox Function 125
Returning Values from the MsgBox Function 132
Getting to Know the InputBox Function 134
Determining and Converting Data Types 136
Using the InputBox Method 138
Summary 142
Chapter 5 Adding Decisions to Excel VBA Programs: A Quick Introduction to Conditional Statements 143
Relational and Logical Operators 143
Using If…Then Statement 144
Using If…Then…Else Statement 148
Using If…Then…ElseIf Statement 150
Nested If…Then Statements 152
Using the Select Case Statement 153
Using Is with the Case Clause 155
Specifying a Range of Values in a Case Clause 156
Specifying Multiple Expressions in a Case Clause 157
Writing a VBA Procedure with Multiple Conditions 157
Using Conditional Logic in Function Procedures 160
Summary 161
Chapter 6 Adding Repeating Actions to Excel VBA Programs: A Quick Introduction to Looping Statements 163
Introducing Looping Statements 163
Understanding Do…While and Do…Until Loops 164
Avoiding Infinite Loops 168
Executing a Procedure Line by Line 168
Understanding While…Wend Loop 169
Understanding For…Next Loop 170
Understanding For…Each…Next Loop 173
Exiting Loops Early 174
Using a Do…While Statement 175
Using Loops and Conditionals 176
Summary 177
Chapter 7 Storing Multiple Values in Excel VBA Programs: A Quick Introduction to Working with Arrays 179
Understanding Arrays 179
Declaring Arrays 181
Array Upper and Lower Bounds 183
Initializing and Filling an Array 183
Filling an Array Using Individual Assignment Statements 183
Filling an Array Using the Array Function 184
Filling an Array Using For…Next Loop 184
Using a One-Dimensional Array 185
Using a Two-Dimensional Array 187
Using a Dynamic Array 188
Using Array Functions 190
The Array Function 190
The IsArray Function 191
The Erase Function 191
The LBound and UBound Functions 192
Troubleshooting Errors in Arrays 193
Using the ParamArray Keyword 194
Data Entry with an Array 195
Sorting an Array with Excel 196
Summary 198
Chapter 8 Keeping Track of Multiple Values in Excel VBA Programs: A Quick Introduction to Creating and Using Collections 199
Working with Built-in Collections 199
Creating Your Own Collection 201
Adding Objects to a Custom Collection 201
Determining the Number of Items in Your Collection 203
Accessing Items in a Collection 203
Removing Items from a Collection 203
Updating Items in a Collection 204
Returning a Collection from a Function 207
Using Custom and Built-in Collections Together 209
Collections versus Arrays 214
Watching the Execution of Your VBA Procedures 214
Summary 219
Chapter 9 Excel Tools for Testing and Debugging: A Quick Introduction to Testing VBA Programs 221
Testing VBA Procedures 221
Stopping a Procedure 222
Using Breakpoints 223
When to Use a Breakpoint 227
Using the Immediate Window in Break Mode 228
Using the Stop and Assert Statements 228
Using the Watch Window 230
Removing Watch Expressions 234
Using Quick Watch 234
Using the Locals Windows and the Call Stack Dialog Box 235
Navigating with Bookmarks 237
Trapping Errors 238
Using the Err Object 239
Setting Error Trapping Options in a VBA Project 243
Stepping through VBA Procedures 244
Stepping Over a Procedure and Running to Cursor 245
Setting the Next Statement 246
Showing the Next Statement 247
Stopping and Resetting VBA Procedures 247
Terminating a Procedure Based on a Condition 247
Summary 250
Index 253
