Excel 2007 VBA programming for dummies

Make Excel 2007 work for you! This clear, nonintimidating guide shows you how to use VBA to create Excel apps that look and work the way you want. Packed with plenty of sample programs, it explains how to work with range objects, control program flow, develop custom dialog boxes, create custom toolbars and menus, and much more.

Discover how to

Grasp essential programming concepts Use the Visual Basic Editor Navigate the new Excel user interface Communicate with your users Deal with errors and bugs

Author Notes

John Walkenbach is a leading authority on spreadsheet software and creator of the award-winning Power Utility Pak.

Introduction	p. 1
Is This the Right Book?	p. 1
So You Want to Be a Programmer	p. 2
Why Bother?	p. 2
What I Assume about You	p. 3
Obligatory Typographical Conventions Section	p. 4
Check Your Security Settings	p. 4
How This Book Is Organized	p. 6
Part I Introducing VBA	p. 6
Part II How VBA Works with Excel	p. 6
Part III Programming Concepts	p. 6
Part IV Communicating with Your Users	p. 6
Part V Putting It All Together	p. 7
Part VI The Part of Tens	p. 7
Marginal Icons	p. 7
Get the Sample Files	p. 8
Now What?	p. 8
Part I Introducing VBA	p. 9
Chapter 1 What Is VBA?	p. 11
Okay, So What Is VBA?	p. 11
What Can You Do with VBA?	p. 12
Inserting a bunch of text	p. 13
Automating a task you perform frequently	p. 13
Automating repetitive operations	p. 13
Creating a custom command	p. 13
Creating a custom button	p. 14
Developing new worksheet functions	p. 14
Creating complete, macro-driven applications	p. 14
Creating custom add-ins for Excel	p. 14
Advantages and Disadvantages of VBA	p. 14
VBA advantages	p. 15
VBA disadvantages	p. 15
VBA in a Nutshell	p. 16
An Excursion into Versions	p. 18
Chapter 2 Jumping Right In	p. 21
First Things First	p. 21
What You'll Be Doing	p. 22
Taking the First Steps	p. 23
Recording the Macro	p. 23
Testing the Macro	p. 25
Examining the Macro	p. 25
Modifying the Macro	p. 28
Saving Workbooks that Contain Macros	p. 29
Understanding Macro Security	p. 29
More about the NameAndTime Macro	p. 31
Part II How VBA Works with Excel	p. 33
Chapter 3 Working In the Visual Basic Editor	p. 35
What Is the Visual Basic Editor?	p. 35
Activating the VBE	p. 35
Understanding VBE components	p. 36
Working with the Project Explorer	p. 38
Adding a new VBA module	p. 39
Removing a VBA module	p. 39
Exporting and importing objects	p. 40
Working with a Code Window	p. 40
Minimizing and maximizing windows	p. 40
Creating a module	p. 42
Getting VBA code into a module	p. 42
Entering code directly	p. 43
Using the macro recorder	p. 45
Copying VBA code	p. 47
Customizing the VBA Environment	p. 47
Using the Editor tab	p. 48
Using the Editor Format tab	p. 50
Using the General tab	p. 52
Using the Docking tab	p. 52
Chapter 4 Introducing the Excel Object Model	p. 53
Excel Is an Object?	p. 54
Climbing the Object Hierarchy	p. 54
Wrapping Your Mind around Collections	p. 56
Referring to Objects	p. 56
Navigating through the hierarchy	p. 57
Simplifying object references	p. 58
Diving into Object Properties and Methods	p. 59
Object properties	p. 59
Object methods	p. 62
Object events	p. 63
Finding Out More	p. 63
Using VBA's Help system	p. 63
Using the Object Browser	p. 64
Chapter 5 VBA Sub and Function Procedures	p. 67
Subs versus Functions	p. 67
Looking at Sub procedures	p. 68
Looking at Function procedures	p. 68
Naming Subs and Functions	p. 69
Executing Sub procedures	p. 69
Executing the Sub procedure directly	p. 71
Executing the procedure from the Macro dialog box	p. 72
Executing a macro by using a shortcut key	p. 72
Executing the procedure from a button or shape	p. 74
Executing the procedure from another procedure	p. 76
Executing Function procedures	p. 76
Calling the function from a Sub procedure	p. 77
Calling a function from a worksheet formula	p. 77
Chapter 6 Using the Excel Macro Recorder	p. 79
Is It Live or Is It VBA?	p. 79
Recording Basics	p. 80
Preparing to Record	p. 82
Relative or Absolute?	p. 82
Recording in absolute mode	p. 82
Recording in relative mode	p. 83
What Gets Recorded?	p. 85
Recording Options	p. 86
Macro name	p. 87
Shortcut key	p. 87
Store Macro In	p. 87
Description	p. 87
Is This Thing Efficient?	p. 88
Part III Programming Concepts	p. 91
Chapter 7 Essential VBA Language Elements	p. 93
Using Comments in Your VBA Code	p. 93
Using Variables, Constants, and Data Types	p. 95
Understanding variables	p. 95
What are VBA's data types?	p. 97
Declaring and scoping variables	p. 98
Working with constants	p. 103
Working with strings	p. 105
Working with dates	p. 106
Using Assignment Statements	p. 106
Assignment statement examples	p. 107
About that equal sign	p. 107
Other operators	p. 108
Working with Arrays	p. 109
Declaring arrays	p. 109
Multidimensional arrays	p. 110
Dynamic arrays	p. 111
Using Labels	p. 111
Chapter 8 Working with Range Objects	p. 113
A Quick Review	p. 113
Other Ways to Refer to a Range	p. 114
The Cells property	p. 115
The Offset property	p. 116
Referring to entire columns and rows	p. 116
Some Useful Range Object Properties	p. 117
The Value property	p. 117
The Text property	p. 118
The Count property	p. 118
The Column and Row properties	p. 118
The Address property	p. 119
The HasFormula property	p. 119
The Font property	p. 120
The Interior property	p. 120
The Formula property	p. 121
The NumberFormat property	p. 121
Some Useful Range Object Methods	p. 122
The Select method	p. 122
The Copy and Paste methods	p. 123
The Clear method	p. 123
The Delete method	p. 124
Chapter 9 Using VBA and Worksheet Functions	p. 125
What Is a Function?	p. 125
Using Built-in VBA Functions	p. 126
VBA function examples	p. 126
VBA functions that do more than return a value	p. 128
Discovering VBA functions	p. 129
Using Worksheet Functions in VBA	p. 132
Worksheet function examples	p. 133
Entering worksheet functions	p. 136
More about Using Worksheet Functions	p. 136
Using Custom Functions	p. 137
Chapter 10 Controlling Program Flow and Making Decisions	p. 139
Going with the Flow, Dude	p. 139
The GoTo Statement	p. 140
Decisions, decisions	p. 141
The If-Then structure	p. 141
The Select Case structure	p. 146
Knocking Your Code for a Loop	p. 149
For-Next loops	p. 150
Do-While loop	p. 153
Do-Until loop	p. 154
Looping through a Collection	p. 155
Chapter 11 Automatic Procedures and Events	p. 157
Preparing for the Big Event	p. 157
Are events useful?	p. 159
Programming event-handler procedures	p. 160
Where Does the VBA Code Go?	p. 160
Writing an Event-Handler Procedure	p. 161
Introductory Examples	p. 163
The Open event for a workbook	p. 163
The BeforeClose event for a workbook	p. 165
The BeforeSave event for a workbook	p. 165
Examples of Activation Events	p. 166
Activate and deactivate events in a sheet	p. 166
Activate and deactivate events in a workbook	p. 167
Workbook activation events	p. 168
Other Worksheet-Related Events	p. 169
The BeforeDoubleClick event	p. 169
The BeforeRightClick event	p. 169
The Change event	p. 170
Events Not Associated with Objects	p. 172
The OnTime event	p. 172
Keypress events	p. 174
Chapter 12 Error-Handling Techniques	p. 177
Types of Errors	p. 177
An Erroneous Example	p. 178
The macro's not quite perfect	p. 179
The macro is still not perfect	p. 180
Is the macro perfect yet?	p. 180
Giving up on perfection	p. 181
Handling Errors Another Way	p. 182
Revisiting the EnterSquareRoot procedure	p. 182
About the On Error statement	p. 183
Handling Errors: The Details	p. 184
Resuming after an error	p. 184
Error handling in a nutshell	p. 186
Knowing when to ignore errors	p. 186
Identifying specific errors	p. 187
An Intentional Error	p. 188
Chapter 13 Bug Extermination Techniques	p. 191
Species of Bugs	p. 191
Identifying Bugs	p. 192
Debugging Techniques	p. 193
Examining your code	p. 193
Using the MsgBox function	p. 194
Inserting Debug.Print statements	p. 195
Using the VBA debugger	p. 196
About the Debugger	p. 196
Setting breakpoints in your code	p. 196
Using the Watch window	p. 199
Using the Locals Window	p. 201
Bug Reduction Tips	p. 201
Chapter 14 VBA Programming Examples	p. 203
Working with Ranges	p. 203
Copying a range	p. 204
Copying a variable-sized range	p. 205
Selecting to the end of a row or column	p. 206
Selecting a row or column	p. 207
Moving a range	p. 207
Looping through a range efficiently	p. 208
Prompting for a cell value	p. 209
Determining the selection type	p. 210
Identifying a multiple selection	p. 211
Changing Excel Settings	p. 211
Changing Boolean settings	p. 212
Changing non-Boolean settings	p. 212
Working with Charts	p. 213
Modifying the chart type	p. 214
Looping through the ChartObjects collection	p. 214
Modifying chart properties	p. 215
Applying chart formatting	p. 215
VBA Speed Tips	p. 216
Turning off screen updating	p. 216
Turning off automatic calculation	p. 217
Eliminating those pesky alert messages	p. 218
Simplifying object references	p. 219
Declaring variable types	p. 219
Using the With-End With structure	p. 220
Part IV Communicating with Your Users	p. 221
Chapter 15 Simple Dialog Boxes	p. 223
Why Create UserForms?	p. 223
The MsgBox Function	p. 224
Displaying a simple message box	p. 225
Getting a response from a message box	p. 225
Customizing message boxes	p. 226
The InputBox Function	p. 229
InputBox syntax	p. 229
An InputBox example	p. 229
The GetOpenFilename Method	p. 231
The syntax	p. 232
A GetOpenFilename example	p. 232
Selecting multiple files	p. 234
The GetSaveAsFilename Method	p. 235
Getting a Folder Name	p. 236
Displaying Excel's Built-in Dialog Boxes	p. 236
Chapter 16 UserForm Basics	p. 239
Knowing When to Use a UserForm	p. 239
Creating UserForms: An Overview	p. 240
Working with UserForms	p. 241
Inserting a new UserForm	p. 241
Adding controls to a UserForm	p. 242
Changing properties for a UserForm control	p. 243
Viewing the UserForm Code window	p. 244
Displaying a UserForm	p. 245
Using information from a UserForm	p. 245
A UserForm Example	p. 246
Creating the UserForm	p. 246
Adding the CommandButtons	p. 247
Adding the OptionButtons	p. 248
Adding event-handler procedures	p. 250
Creating a macro to display the dialog box	p. 251
Making the macro available	p. 252
Testing the macro	p. 253
Chapter 17 Using UserForm Controls	p. 255
Getting Started with Dialog Box Controls	p. 255
Adding controls	p. 255
Introducing control properties	p. 257
Dialog Box Controls: The Details	p. 259
CheckBox control	p. 259
ComboBox control	p. 260
CommandButton control	p. 261
Frame control	p. 262
Image control	p. 262
Label control	p. 263
ListBox control	p. 264
MultiPage control	p. 265
OptionButton control	p. 266
RefEdit control	p. 267
ScrollBar control	p. 267
SpinButton control	p. 268
TabStrip control	p. 269
TextBox control	p. 269
ToggleButton control	p. 270
Working with Dialog Box Controls	p. 270
Moving and resizing controls	p. 270
Aligning and spacing controls	p. 271
Accommodating keyboard users	p. 272
Testing a UserForm	p. 273
Dialog Box Aesthetics	p. 274
Chapter 18 UserForm Techniques and Tricks	p. 275
Using Dialog Boxes	p. 275
A UserForm Example	p. 275
Creating the dialog box	p. 276
Writing code to display the dialog box	p. 278
Making the macro available	p. 279
Trying out your dialog box	p. 279
Adding event-handler procedures	p. 280
Validating the data	p. 282
Now the dialog box works	p. 282
More UserForm Examples	p. 282
A ListBox example	p. 282
Selecting a range	p. 287
Using multiple sets of OptionButtons	p. 288
Using a SpinButton and a TextBox	p. 289
Using a UserForm as a progress indicator	p. 291
Creating a tabbed dialog box	p. 295
Displaying a chart in a dialog box	p. 296
A Dialog Box Checklist	p. 297
Chapter 19 Accessing Your Macros Through the User Interface	p. 299
CommandBars and Excel 2007	p. 299
Excel 2007 Ribbon Customization	p. 301
Working with CommandBars	p. 304
Commanding the CommandBars collection	p. 304
Listing all shortcut menus	p. 304
Referring to CommandBars	p. 305
Referring to controls in a CommandBar	p. 306
Properties of CommandBar controls	p. 307
VBA Shortcut Menu Examples	p. 309
Resetting all built-in right-click menus	p. 309
Adding a new item to the Cell shortcut menu	p. 309
Disabling a shortcut menu	p. 311
Creating a Custom Toolbar	p. 312
Part V Putting It Alt Together	p. 315
Chapter 20 Creating Worksheet Functions and Living to Tell about It	p. 317
Why Create Custom Functions?	p. 317
Understanding VBA Function Basics	p. 318
Writing Functions	p. 319
Working with Function Arguments	p. 319
Function Examples	p. 320
A function with no argument	p. 320
A function with one argument	p. 320
A function with two arguments	p. 322
A function with a range argument	p. 323
A function with an optional argument	p. 324
A function with an indefinite number of arguments	p. 326
Functions That Return an Array	p. 327
Returning an array of month names	p. 327
Returning a sorted list	p. 328
Using the Insert Function Dialog Box	p. 330
Displaying the function's description	p. 330
Argument descriptions	p. 331
Chapter 21 Creating Excel Add-Ins	p. 333
Okay...So What's an Add-In?	p. 333
Why Create Add-Ins?	p. 334
Working with Add-Ins	p. 335
Add-in Basics	p. 336
An Add-in Example	p. 337
Setting up the workbook	p. 337
Testing the workbook	p. 339
Adding descriptive information	p. 340
Protecting the VBA code	p. 341
Creating the add-in	p. 341
Opening the add-in	p. 341
Distributing the add-in	p. 342
Modifying the add-in	p. 342
Part VI The Part of Tens	p. 345
Chapter 22 Ten VBA Questions (And Answers)	p. 347
The Top Ten Questions about VBA	p. 347
Chapter 23 (Almost) Ten Excel Resources	p. 351
The VBA Help System	p. 351
Microsoft Product Support	p. 351
Internet Newsgroups	p. 352
Internet Web Sites	p. 353
Excel Blogs	p. 353
Google	p. 353
Local User Groups	p. 354
My Other Book	p. 354
Index	p. 355

Available:*

On Order

Summary

Summary

Author Notes

Table of Contents