®
Excel 2007 VBA
Programming
FOR
DUMmIES
‰
by John Walkenbach
Revised by Jan Karel Pieterse
Excel® 2007 VBA Programming For Dummies®
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-
ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written
permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the
Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600.
Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing,
Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at
http://www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the
Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade
dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United
States and other countries, and may not be used without written permission. Microsoft and Excel are reg-
istered trademarks of Microsoft Corporation in the United States and/or other countries. All other trade-
marks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any
product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REP-
RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CON-
TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT
LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CRE-
ATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CON-
TAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR
OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A
COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE
AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION
OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FUR-
THER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFOR-
MATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE.
FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE
CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services, please contact our Customer Care
Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may
not be available in electronic books.
Library of Congress Control Number: 2006939593
ISBN: 978-0-470-04674-6
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
About the Author
John Walkenbach is the author of more than 50 spreadsheet books and lives
in southern Arizona. Visit his Web site at http://j-walk.com.
Dedication
“This book is dedicated to Jim Kloss and Esther Golton — my two favorite
people in Matanuska-Susitna county. By putting their names in this book, I’m
ensured of at least one sale in Alaska.”
Author’s Acknowledgments
Thanks to all of the talented people at Wiley Publishing for making it so easy
to write these books. And special thanks to Jan Karel Pieterse for his assis-
tance with this edition.
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments through our online registration form
located at www.dummies.com/register/.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Composition Services
Media Development Project Coordinator: Jennifer Theriot
Project Editor: Beth Taylor Layout and Graphics: Carl Byers, Stephanie D.
Executive Editor: Greg Croy Jumper, Barbara Moore,
Copy Editor: Beth Taylor Julie Trippetti
Technical Editor: Allen Wyatt Proofreaders: Laura Albert, John Greenough,
Techbooks
Editorial Manager: Jodi Jensen
Indexer: Techbooks
Media Development Coordinator:
Laura Atkinson
Media Project Supervisor: Laura Moss
Media Development Manager:
Laura VanWinkle
Media Development Associate Producer:
Richard Graves
Editorial Assistant: Amanda Foxworth
Sr. Editorial Assistant: Cherie Case
Cartoons: Rich Tennant
(www.the5thwave.com)
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Joyce Pepple, Acquisitions Director
Composition Services
Gerry Fahey, Vice President of Production Services
Debbie Stailey, Director of Composition Services
Contents at a Glance
Introduction .................................................................1
Part I: Introducing VBA ................................................9
Chapter 1: What Is VBA?..................................................................................................11
Chapter 2: Jumping Right In............................................................................................21
Part II: How VBA Works with Excel..............................33
Chapter 3: Working In the Visual Basic Editor..............................................................35
Chapter 4: Introducing the Excel Object Model ...........................................................53
Chapter 5: VBA Sub and Function Procedures .............................................................67
Chapter 6: Using the Excel Macro Recorder .................................................................79
Part III: Programming Concepts...................................91
Chapter 7: Essential VBA Language Elements ..............................................................93
Chapter 8: Working with Range Objects......................................................................113
Chapter 9: Using VBA and Worksheet Functions .......................................................125
Chapter 10: Controlling Program Flow and Making Decisions .................................139
Chapter 11: Automatic Procedures and Events..........................................................157
Chapter 12: Error-Handling Techniques ......................................................................177
Chapter 13: Bug Extermination Techniques ...............................................................191
Chapter 14: VBA Programming Examples ...................................................................203
Part IV: Communicating with Your Users ....................221
Chapter 15: Simple Dialog Boxes..................................................................................223
Chapter 16: UserForm Basics........................................................................................239
Chapter 17: Using UserForm Controls .........................................................................255
Chapter 18: UserForm Techniques and Tricks ...........................................................275
Chapter 19: Accessing Your Macros Through the User Interface ............................299
Part V: Putting It All Together ...................................315
Chapter 20: Creating Worksheet Functions and Living to Tell about It...................317
Chapter 21: Creating Excel Add-Ins..............................................................................333
Part VI: The Part of Tens ...........................................345
Chapter 22: Ten VBA Questions (And Answers) ........................................................347
Chapter 23: (Almost) Ten Excel Resources.................................................................351
Index .......................................................................355
Table of Contents
Introduction ..................................................................1
Is This the Right Book?....................................................................................1
So You Want to Be a Programmer . . . ............................................................2
Why Bother? .....................................................................................................2
What I Assume about You ...............................................................................3
Obligatory Typographical Conventions Section ..........................................4
Check Your Security Settings..........................................................................4
How This Book Is Organized...........................................................................6
Part I: Introducing VBA ..........................................................................6
Part II: How VBA Works with Excel ......................................................6
Part III: Programming Concepts............................................................6
Part IV: Communicating with Your Users ............................................6
Part V: Putting It All Together...............................................................7
Part VI: The Part of Tens .......................................................................7
Marginal Icons ..................................................................................................7
Get the Sample Files.........................................................................................8
Now What? ........................................................................................................8
Part I: Introducing VBA .................................................9
Chapter 1: What Is VBA? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Okay, So What Is VBA?...................................................................................11
What Can You Do with VBA?.........................................................................12
Inserting a bunch of text ....................................................................13
Automating a task you perform frequently.......................................13
Automating repetitive operations ......................................................13
Creating a custom command ..............................................................13
Creating a custom button....................................................................14
Developing new worksheet functions................................................14
Creating complete, macro-driven applications ................................14
Creating custom add-ins for Excel .....................................................14
Advantages and Disadvantages of VBA.......................................................14
VBA advantages....................................................................................15
VBA disadvantages...............................................................................15
VBA in a Nutshell ...........................................................................................16
An Excursion into Versions...........................................................................18
viii Excel 2007 VBA Programming For Dummies
Chapter 2: Jumping Right In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
First Things First ............................................................................................21
What You’ll Be Doing .....................................................................................22
Taking the First Steps ....................................................................................23
Recording the Macro .....................................................................................23
Testing the Macro ..........................................................................................25
Examining the Macro .....................................................................................25
Modifying the Macro......................................................................................28
Saving Workbooks that Contain Macros .....................................................29
Understanding Macro Security.....................................................................29
More about the NameAndTime Macro ........................................................31
Part II: How VBA Works with Excel ..............................33
Chapter 3: Working In the Visual Basic Editor . . . . . . . . . . . . . . . . . . . .35
What Is the Visual Basic Editor? ..................................................................35
Activating the VBE ...............................................................................35
Understanding VBE components .......................................................36
Working with the Project Explorer...............................................................38
Adding a new VBA module..................................................................39
Removing a VBA module .....................................................................39
Exporting and importing objects .......................................................40
Working with a Code Window.......................................................................40
Minimizing and maximizing windows ................................................40
Creating a module ................................................................................42
Getting VBA code into a module ........................................................42
Entering code directly .........................................................................43
Using the macro recorder ...................................................................45
Copying VBA code................................................................................47
Customizing the VBA Environment .............................................................47
Using the Editor tab .............................................................................48
Using the Editor Format tab................................................................50
Using the General tab ..........................................................................52
Using the Docking tab..........................................................................52
Chapter 4: Introducing the Excel Object Model . . . . . . . . . . . . . . . . . . .53
Excel Is an Object? .........................................................................................54
Climbing the Object Hierarchy.....................................................................54
Wrapping Your Mind around Collections....................................................56
Referring to Objects.......................................................................................56
Navigating through the hierarchy ......................................................57
Simplifying object references..............................................................58
Diving into Object Properties and Methods ...............................................59
Object properties .................................................................................59
Object methods ....................................................................................62
Object events ........................................................................................63
Table of Contents ix
Finding Out More ...........................................................................................63
Using VBA’s Help system .....................................................................63
Using the Object Browser....................................................................64
Chapter 5: VBA Sub and Function Procedures . . . . . . . . . . . . . . . . . . . .67
Subs versus Functions...................................................................................67
Looking at Sub procedures .................................................................68
Looking at Function procedures.........................................................68
Naming Subs and Functions................................................................69
Executing Sub procedures ............................................................................69
Executing the Sub procedure directly ...............................................71
Executing the procedure from the Macro dialog box ......................72
Executing a macro by using a shortcut key ......................................72
Executing the procedure from a button or shape ............................74
Executing the procedure from another procedure ..........................76
Executing Function procedures ...................................................................76
Calling the function from a Sub procedure .......................................77
Calling a function from a worksheet formula....................................77
Chapter 6: Using the Excel Macro Recorder . . . . . . . . . . . . . . . . . . . . .79
Is It Live or Is It VBA?.....................................................................................79
Recording Basics............................................................................................80
Preparing to Record.......................................................................................82
Relative or Absolute?.....................................................................................82
Recording in absolute mode ...............................................................82
Recording in relative mode .................................................................83
What Gets Recorded? ....................................................................................85
Recording Options .........................................................................................86
Macro name...........................................................................................87
Shortcut key ..........................................................................................87
Store Macro In.......................................................................................87
Description............................................................................................87
Is This Thing Efficient? ..................................................................................88
Part III: Programming Concepts ...................................91
Chapter 7: Essential VBA Language Elements . . . . . . . . . . . . . . . . . . . .93
Using Comments in Your VBA Code ............................................................93
Using Variables, Constants, and Data Types ..............................................95
Understanding variables .....................................................................95
What are VBA’s data types?.................................................................97
Declaring and scoping variables ........................................................98
Working with constants .....................................................................103
Working with strings ..........................................................................105
Working with dates.............................................................................106
x Excel 2007 VBA Programming For Dummies
Using Assignment Statements ....................................................................106
Assignment statement examples......................................................107
About that equal sign.........................................................................107
Other operators..................................................................................108
Working with Arrays ....................................................................................109
Declaring arrays .................................................................................109
Multidimensional arrays....................................................................110
Dynamic arrays...................................................................................111
Using Labels..................................................................................................111
Chapter 8: Working with Range Objects . . . . . . . . . . . . . . . . . . . . . . . .113
A Quick Review.............................................................................................113
Other Ways to Refer to a Range .................................................................114
The Cells property .............................................................................115
The Offset property ...........................................................................116
Referring to entire columns and rows .............................................116
Some Useful Range Object Properties.......................................................117
The Value property ............................................................................117
The Text property ..............................................................................118
The Count property ...........................................................................118
The Column and Row properties .....................................................118
The Address property........................................................................119
The HasFormula property .................................................................119
The Font property ..............................................................................120
The Interior property.........................................................................120
The Formula property .......................................................................121
The NumberFormat property ...........................................................121
Some Useful Range Object Methods..........................................................122
The Select method .............................................................................122
The Copy and Paste methods...........................................................123
The Clear method...............................................................................123
The Delete method.............................................................................124
Chapter 9: Using VBA and Worksheet Functions . . . . . . . . . . . . . . . .125
What Is a Function?......................................................................................125
Using Built-in VBA Functions......................................................................126
VBA function examples......................................................................126
VBA functions that do more than return a value ...........................128
Discovering VBA functions ...............................................................129
Using Worksheet Functions in VBA ...........................................................132
Worksheet function examples ..........................................................133
Entering worksheet functions...........................................................136
More about Using Worksheet Functions ...................................................136
Using Custom Functions .............................................................................137
Table of Contents xi
Chapter 10: Controlling Program Flow and Making Decisions . . . . .139
Going with the Flow, Dude ..........................................................................139
The GoTo Statement ....................................................................................140
Decisions, decisions...........................................................................141
The If-Then structure .........................................................................141
The Select Case structure .................................................................146
Knocking Your Code for a Loop .................................................................149
For-Next loops.....................................................................................150
Do-While loop .....................................................................................153
Do-Until loop .......................................................................................154
Looping through a Collection .....................................................................155
Chapter 11: Automatic Procedures and Events . . . . . . . . . . . . . . . . . .157
Preparing for the Big Event.........................................................................157
Are events useful? ..............................................................................159
Programming event-handler procedures ........................................160
Where Does the VBA Code Go? ..................................................................160
Writing an Event-Handler Procedure .........................................................161
Introductory Examples................................................................................163
The Open event for a workbook.......................................................163
The BeforeClose event for a workbook ...........................................165
The BeforeSave event for a workbook.............................................165
Examples of Activation Events ...................................................................166
Activate and deactivate events in a sheet.......................................166
Activate and deactivate events in a workbook...............................167
Workbook activation events .............................................................168
Other Worksheet-Related Events ...............................................................169
The BeforeDoubleClick event ...........................................................169
The BeforeRightClick event ..............................................................169
The Change event...............................................................................170
Events Not Associated with Objects .........................................................172
The OnTime event..............................................................................172
Keypress events..................................................................................174
Chapter 12: Error-Handling Techniques . . . . . . . . . . . . . . . . . . . . . . . . .177
Types of Errors .............................................................................................177
An Erroneous Example ................................................................................178
The macro’s not quite perfect ..........................................................179
The macro is still not perfect............................................................180
Is the macro perfect yet?...................................................................180
Giving up on perfection .....................................................................181
Handling Errors Another Way.....................................................................182
Revisiting the EnterSquareRoot procedure ...................................182
About the On Error statement ..........................................................183
xii Excel 2007 VBA Programming For Dummies
Handling Errors: The Details ......................................................................184
Resuming after an error.....................................................................184
Error handling in a nutshell ..............................................................186
Knowing when to ignore errors ........................................................186
Identifying specific errors .................................................................187
An Intentional Error .....................................................................................188
Chapter 13: Bug Extermination Techniques . . . . . . . . . . . . . . . . . . . . .191
Species of Bugs.............................................................................................191
Identifying Bugs............................................................................................192
Debugging Techniques ................................................................................193
Examining your code .........................................................................193
Using the MsgBox function ...............................................................194
Inserting Debug.Print statements ....................................................195
Using the VBA debugger....................................................................196
About the Debugger.....................................................................................196
Setting breakpoints in your code .....................................................196
Using the Watch window ...................................................................199
Using the Locals Window ..................................................................201
Bug Reduction Tips......................................................................................201
Chapter 14: VBA Programming Examples . . . . . . . . . . . . . . . . . . . . . . .203
Working with Ranges ...................................................................................203
Copying a range ..................................................................................204
Copying a variable-sized range.........................................................205
Selecting to the end of a row or column..........................................206
Selecting a row or column.................................................................207
Moving a range ...................................................................................207
Looping through a range efficiently.................................................208
Prompting for a cell value .................................................................209
Determining the selection type .......................................................210
Identifying a multiple selection ........................................................211
Changing Excel Settings ..............................................................................211
Changing Boolean settings................................................................212
Changing non-Boolean settings ........................................................212
Working with Charts ....................................................................................213
Modifying the chart type...................................................................214
Looping through the ChartObjects collection................................214
Modifying chart properties ...............................................................215
Applying chart formatting.................................................................215
VBA Speed Tips ............................................................................................216
Turning off screen updating..............................................................216
Turning off automatic calculation ....................................................217
Eliminating those pesky alert messages .........................................218
Simplifying object references............................................................219
Declaring variable types....................................................................219
Using the With-End With structure ............................................................220
Table of Contents xiii
Part IV: Communicating with Your Users.....................221
Chapter 15: Simple Dialog Boxes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .223
Why Create UserForms?..............................................................................223
The MsgBox Function ..................................................................................224
Displaying a simple message box.....................................................225
Getting a response from a message box..........................................225
Customizing message boxes .............................................................226
The InputBox Function................................................................................229
InputBox syntax..................................................................................229
An InputBox example.........................................................................229
The GetOpenFilename Method...................................................................231
The syntax...........................................................................................232
A GetOpenFilename example............................................................232
Selecting multiple files.......................................................................234
The GetSaveAsFilename Method ...............................................................235
Getting a Folder Name .................................................................................236
Displaying Excel’s Built-in Dialog Boxes....................................................236
Chapter 16: UserForm Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .239
Knowing When to Use a UserForm ............................................................239
Creating UserForms: An Overview.............................................................240
Working with UserForms.............................................................................241
Inserting a new UserForm .................................................................241
Adding controls to a UserForm ........................................................242
Changing properties for a UserForm control..................................243
Viewing the UserForm Code window...............................................244
Displaying a UserForm.......................................................................245
Using information from a UserForm ................................................245
A UserForm Example ...................................................................................246
Creating the UserForm.......................................................................246
Adding the CommandButtons ..........................................................247
Adding the OptionButtons ................................................................248
Adding event-handler procedures....................................................250
Creating a macro to display the dialog box ....................................251
Making the macro available ..............................................................252
Testing the macro...............................................................................253
Chapter 17: Using UserForm Controls . . . . . . . . . . . . . . . . . . . . . . . . . .255
Getting Started with Dialog Box Controls .................................................255
Adding controls ..................................................................................255
Introducing control properties.........................................................257
Dialog Box Controls: The Details ...............................................................259
CheckBox control ...............................................................................259
ComboBox control .............................................................................260
CommandButton control...................................................................261
xiv Excel 2007 VBA Programming For Dummies
Frame control......................................................................................262
Image control ......................................................................................262
Label control .......................................................................................263
ListBox control ...................................................................................264
MultiPage control ...............................................................................265
OptionButton control.........................................................................266
RefEdit control ....................................................................................267
ScrollBar control.................................................................................267
SpinButton control .............................................................................268
TabStrip control..................................................................................269
TextBox control ..................................................................................269
ToggleButton control .........................................................................270
Working with Dialog Box Controls .............................................................270
Moving and resizing controls............................................................270
Aligning and spacing controls ..........................................................271
Accommodating keyboard users......................................................272
Testing a UserForm ............................................................................273
Dialog Box Aesthetics..................................................................................274
Chapter 18: UserForm Techniques and Tricks . . . . . . . . . . . . . . . . . . .275
Using Dialog Boxes.......................................................................................275
A UserForm Example ...................................................................................275
Creating the dialog box......................................................................276
Writing code to display the dialog box............................................278
Making the macro available ..............................................................279
Trying out your dialog box ...............................................................279
Adding event-handler procedures....................................................280
Validating the data..............................................................................282
Now the dialog box works.................................................................282
More UserForm Examples...........................................................................282
A ListBox example..............................................................................282
Selecting a range.................................................................................287
Using multiple sets of OptionButtons..............................................288
Using a SpinButton and a TextBox ...................................................289
Using a UserForm as a progress indicator ......................................291
Creating a tabbed dialog box ............................................................295
Displaying a chart in a dialog box ....................................................296
A Dialog Box Checklist.................................................................................297
Chapter 19: Accessing Your Macros
Through the User Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .299
CommandBars and Excel 2007 ...................................................................299
Excel 2007 Ribbon Customization..............................................................301
Working with CommandBars ......................................................................304
Commanding the CommandBars collection ...................................304
Listing all shortcut menus.................................................................304
Table of Contents xv
Referring to CommandBars...............................................................305
Referring to controls in a CommandBar..........................................306
Properties of CommandBar controls ...............................................307
VBA Shortcut Menu Examples....................................................................309
Resetting all built-in right-click menus ............................................309
Adding a new item to the Cell shortcut menu ................................309
Disabling a shortcut menu ................................................................311
Creating a Custom Toolbar .........................................................................312
Part V: Putting It All Together....................................315
Chapter 20: Creating Worksheet Functions
and Living to Tell about It . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .317
Why Create Custom Functions? .................................................................317
Understanding VBA Function Basics .........................................................318
Writing Functions .........................................................................................319
Working with Function Arguments ............................................................319
Function Examples.......................................................................................320
A function with no argument ............................................................320
A function with one argument ..........................................................320
A function with two arguments ........................................................322
A function with a range argument ....................................................323
A function with an optional argument .............................................324
A function with an indefinite number of arguments ......................326
Functions That Return an Array.................................................................327
Returning an array of month names ................................................327
Returning a sorted list .......................................................................328
Using the Insert Function Dialog Box ........................................................330
Displaying the function’s description..............................................330
Argument descriptions ......................................................................331
Chapter 21: Creating Excel Add-Ins . . . . . . . . . . . . . . . . . . . . . . . . . . . .333
Okay . . . So What’s an Add-In? ...................................................................333
Why Create Add-Ins?....................................................................................334
Working with Add-Ins...................................................................................335
Add-in Basics ................................................................................................336
An Add-in Example.......................................................................................337
Setting up the workbook ...................................................................337
Testing the workbook ........................................................................339
Adding descriptive information .......................................................340
Protecting the VBA code ...................................................................341
Creating the add-in .............................................................................341
Opening the add-in.............................................................................341
Distributing the add-in.......................................................................342
Modifying the add-in ..........................................................................342
xvi Excel 2007 VBA Programming For Dummies
Part VI: The Part of Tens ............................................345
Chapter 22: Ten VBA Questions (And Answers) . . . . . . . . . . . . . . . . . .347
The Top Ten Questions about VBA............................................................347
Chapter 23: (Almost) Ten Excel Resources . . . . . . . . . . . . . . . . . . . . . .351
The VBA Help System ..................................................................................351
Microsoft Product Support .........................................................................351
Internet Newsgroups ...................................................................................352
Internet Web Sites ........................................................................................353
Excel Blogs ....................................................................................................353
Google............................................................................................................353
Local User Groups........................................................................................354
My Other Book .............................................................................................354
Index........................................................................355
Introduction
G reetings, prospective Excel programmer . . .
Thanks for buying my book. I think you’ll find that it offers a fast, enjoyable way
to discover the ins and outs of Microsoft Excel programming. Even if you don’t
have the foggiest idea of what programming is all about, this book can help you
make Excel jump through hoops in no time (well, it will take some time).
Unlike most programming books, this one is written in plain English, and
even normal people can understand it. Even better, it’s filled with information
of the “just the facts, ma’am” variety — and not the drivel you might need
once every third lifetime.
Is This the Right Book?
Go to any large bookstore and you’ll find many Excel books (far too many,
as far as I’m concerned). A quick overview can help you decide whether this
book is really right for you. This book
Is designed for intermediate to advanced Excel users who want to
master Visual Basic for Applications (VBA) programming.
Requires no previous programming experience.
Covers the most commonly used commands.
Is appropriate for Excel 2007.
Just might make you crack a smile occasionally — it even has cartoons.
If you are using Excel 2000, XP, or 2003, this book is not for you. Excel 2007 is
so different from previous versions. If you’re still using a pre-2007 version of
Excel, locate a book that is specific to that version.
This is not an introductory Excel book. If you’re looking for a general-purpose
Excel book, check out any of the following books, which are all published
by Wiley:
Excel 2007 For Dummies, by Greg Harvey
Excel 2007 Bible, by John Walkenbach (yep, that’s me)
Excel 2007 For Dummies Quick Reference, by John Walkenbach
(me again) and Colin Banfield
2 Excel 2007 VBA Programming For Dummies
Notice that the title of this book isn’t The Complete Guide to Excel VBA
Programming For Dummies. I don’t cover all aspects of Excel programming —
but then again, you probably don’t want to know everything about this topic.
In the unlikely event that you want a more comprehensive Excel program-
ming book, you might try Microsoft Excel 2007 Power Programming With VBA,
by John Walkenbach (is this guy prolific, or what?), also published by Wiley.
So You Want to Be a Programmer . . .
Besides earning money to pay my bills, my main goal in writing this book is
to show Excel users how to use the VBA language — a tool that helps you
significantly enhance the power of the world’s most popular spreadsheet.
Using VBA, however, involves programming. (Yikes! The p word.)
If you’re like most computer users, the word programmer conjures up an
image of someone who looks and behaves nothing like you. Perhaps words
such as nerd, geek, and dweeb come to mind.
Times have changed. Computer programming has become much easier, and
even so-called normal people now engage in this activity. Programming simply
means developing instructions that the computer automatically carries out.
Excel programming refers to the fact that you can instruct Excel to automati-
cally do things that you normally do manually — saving you lots of time and
(you hope) reducing errors. I could go on, but I need to save some good stuff
for Chapter 1.
If you’ve read this far, it’s a safe bet that you need to become an Excel
programmer. This could be something you came up with yourself or
(more likely) something your boss decided. In this book, I tell you enough
about Excel programming so that you won’t feel like an idiot the next time
you’re trapped in a conference room with a group of Excel aficionados. And
by the time you finish this book, you can honestly say, “Yeah, I do some
Excel programming.”
Why Bother?
Most Excel users never bother to explore VBA programming. Your interest in
this topic definitely places you among an elite group. Welcome to the fold! If
you’re still not convinced that mastering Excel programming is a good idea,
I’ve come up with a few good reasons why you might want to take the time to
learn VBA programming.
Introduction 3
It will make you more marketable. Like it or not, Microsoft’s applications
are extremely popular. You may already know that all applications in
Microsoft Office support VBA. The more you know about VBA, the better
your chances for advancement in your job.
It lets you get the most out of your software investment (or, more
likely, your employer’s software investment). Using Excel without
knowing VBA is sort of like buying a TV set and watching only the
odd-numbered channels.
It will improve your productivity (eventually). Mastering VBA
definitely takes some time, but you’ll more than make up for this in
the amount of time you ultimately save because you’re more productive.
Sort of like what they told you about going to college.
It’s fun (well, sometimes). Some people really enjoy making Excel do
things that are otherwise impossible. By the time you finish this book,
you just might be one of those people.
Now are you convinced?
What I Assume about You
People who write books usually have a target reader in mind. For this book,
my target reader is a conglomerate of dozens of Excel users I’ve met over the
years (either in person or out in cyberspace). The following points more or
less describe my hypothetical target reader:
You have access to a PC at work — and probably at home.
You’re running Excel 2007.
You’ve been using computers for several years.
You use Excel frequently in your work, and you consider yourself to be
more knowledgeable about Excel than the average bear.
You need to make Excel do some things that you currently can’t make it do.
You have little or no programming experience.
You understand that the Help system in Excel can actually be useful. Face
it, this book doesn’t cover everything. If you get on good speaking terms
with the Help system, you’ll be able to fill in some of the missing pieces.
You need to accomplish some work, and you have a low tolerance for
thick, boring computer books.
4 Excel 2007 VBA Programming For Dummies
Obligatory Typographical
Conventions Section
All computer books have a section like this. (I think some federal law requires
it.) Read it or skip it.
Sometimes, I refer to key combinations — which means you hold down one
key while you press another. For example, Ctrl+Z means you hold down the
Ctrl key while you press Z.
For menu commands, I use a distinctive character to separate menu items.
For example, you use the following command to open a workbook file:
File➪Open
Note, that in Excel 2007, there is no such thing as a “File” menu visible on
your screen. In fact the File menu has been replaced with the Office button, a
little round contraption that shows up on the top-left side of any Office appli-
cation that has implemented what is called the Ribbon. Any text you need to
enter appears in bold. For example, I might say, enter =SUM(B:B) in cell A1.
Excel programming involves developing code — that is, the instructions Excel
follows. All code in this book appears in a monospace font, like this:
Range(“A1:A12”).Select
Some long lines of code don’t fit between the margins in this book. In such
cases, I use the standard VBA line continuation character sequence: a space
followed by an underscore character. Here’s an example:
Selection.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
When you enter this code, you can type it as written or place it on a single
line (omitting the spaces and the underscore characters).
Check Your Security Settings
It’s a cruel world out there. It seems that some scam artist is always trying to
take advantage of you or cause some type of problem. The world of comput-
ing is equally cruel. You probably know about computer viruses, which can