Excel 2007 VBA Programming For Dummies

 

by John Walkenbach

 

 

 

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 assistance 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 Media Development

Project Editor: Beth Taylor

Executive Editor: Greg Croy

Copy Editor: Beth Taylor

Technical Editor: Allen Wyatt

Editorial Manager: Jodi Jensen

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)

Composition Services

Project Coordinator: Jennifer Theriot

Layout and Graphics: Carl Byers, Stephanie D. Jumper, Barbara Moore, Julie Trippetti

Proofreaders: Laura Albert, John Greenough, Techbooks

Indexer: Techbooks

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

Title

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

Marginal Icons

Get the Sample Files

Now What?

Part I : Introducing VBA

Chapter 1: What Is VBA?

Okay, So What Is VBA?

What Can You Do with VBA?

Advantages and Disadvantages of VBA

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?

Working with the Project Explorer

Working with a Code Window

Customizing the VBA Environment

Chapter 4: Introducing the Excel Object Model

Excel Is an Object?

Climbing the Object Hierarchy

Wrapping Your Mind around Collections

Referring to Objects

Diving into Object Properties and Methods

Finding Out More

Chapter 5: VBA Sub and Function Procedures

Subs versus Functions

Executing Sub procedures

Executing Function procedures

Chapter 6: Using the Excel Macro Recorder

Is It Live or Is It VBA?

Recording Basics

Preparing to Record

Relative or Absolute?

What Gets Recorded?

Recording Options

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

Using Assignment Statements

Working with Arrays

Using Labels

Chapter 8: Working with Range Objects

A Quick Review

Other Ways to Refer to a Range

Some Useful Range Object Properties

Some Useful Range Object Methods

Chapter 9: Using VBA and Worksheet Functions

What Is a Function?

Using Built-in VBA Functions

Using Worksheet Functions in VBA

More about Using Worksheet Functions

Using Custom Functions

Chapter 10: Controlling Program Flow and Making Decisions

Going with the Flow, Dude

The GoTo Statement

Knocking Your Code for a Loop

Looping through a Collection

Chapter 11: Automatic Procedures and Events

Preparing for the Big Event

Where Does the VBA Code Go?

Writing an Event-Handler Procedure

Introductory Examples

Examples of Activation Events

Other Worksheet-Related Events

Events Not Associated with Objects

Chapter 12: Error-Handling Techniques

Types of Errors

An Erroneous Example

Handling Errors Another Way

Handling Errors: The Details

An Intentional Error

Chapter 13: Bug Extermination Techniques

Species of Bugs

Identifying Bugs

Debugging Techniques

About the Debugger

Bug Reduction Tips

Chapter 14: VBA Programming Examples

Working with Ranges

Changing Excel Settings

Working with Charts

VBA Speed Tips

Using the With-End With structure

Part IV : Communicating with Your Users

Chapter 15: Simple Dialog Boxes

Why Create UserForms?

The MsgBox Function

The InputBox Function

The GetOpenFilename Method

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

A UserForm Example

Chapter 17: Using UserForm Controls

Getting Started with Dialog Box Controls

Dialog Box Controls: The Details

Working with Dialog Box Controls

Dialog Box Aesthetics

Chapter 18: UserForm Techniques and Tricks

Using Dialog Boxes

A UserForm Example

More UserForm Examples

A Dialog Box Checklist

Chapter 19: Accessing Your Macros Through the User Interface

CommandBars and Excel 2007

Excel 2007 Ribbon Customization

Working with CommandBars

VBA Shortcut Menu Examples

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

Functions That Return an Array

Using the Insert Function Dialog Box

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

Part VI : The Part of Tens

Chapter 22: Ten VBA Questions (And Answers)

The Top Ten Questions about VBA

Chapter 23: (Almost) Ten Excel Resources

The VBA Help System

Microsoft Product Support

Internet Newsgroups

Internet Web Sites

Excel Blogs

Google

Local User Groups

My Other Book

Introduction

Greetings, 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

bullet Is designed for intermediate to advanced Excel users who want to master Visual Basic for Applications (VBA) programming.

bullet Requires no previous programming experience.

bullet Covers the most commonly used commands.

bullet Is appropriate for Excel 2007.

bullet 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:

bullet Excel 2007 For Dummies, by Greg Harvey

bullet Excel 2007 Bible, by John Walkenbach (yep, that’s me)

bullet Excel 2007 For Dummies Quick Reference, by John Walkenbach (me again) and Colin Banfield

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 programming 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 automatically 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.

bullet 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.

bullet 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.

bullet 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.

bullet 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:

bullet You have access to a PC at work — and probably at home.

bullet You’re running Excel 2007.

bullet You’ve been using computers for several years.

bullet You use Excel frequently in your work, and you consider yourself to be more knowledgeable about Excel than the average bear.

bullet You need to make Excel do some things that you currently can’t make it do.

bullet You have little or no programming experience.

bullet 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.

bullet You need to accomplish some work, and you have a low tolerance for thick, boring computer books.

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 application 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 computing is equally cruel. You probably know about computer viruses, which can cause some nasty things to happen to your system. But did you know that computer viruses can also reside in an Excel file? It’s true. In fact, it’s relatively easy to write a computer virus by using VBA. An unknowing user can open an Excel file and spread the virus to other Excel workbooks.

Over the years, Microsoft has become increasingly concerned about security issues. This is a good thing, but it also means that Excel users need to understand how things work. You can check Excel’s security settings by using the File⇒Excel Options⇒Trust Center⇒Trust Center Settings command. There is a plethora of options in there. If you click the Macro Settings tab, your options are:

bullet Disable all macros without notification: Macros will not work, regardless of what you do.

bullet Disable all macros with notification: When you open a workbook with macros you will either see the Message Bar open with an option you can click to enable macros, or (if the VBE is open), you’ll get a message asking if you want to enable macros.

bullet Disable all macros except digitally signed macros: Only macros with a digital signature are allowed to run (but even for those signatures you haven’t marked as trusted you still get the security warning).

bullet Enable all macros (not recommended; potentially dangerous code can run).

Consider this scenario: You spend a week writing a killer VBA program that will revolutionize your company. You test it thoroughly, and then send it to your boss. He calls you into his office and claims that your macro doesn’t do anything at all. What’s going on? Chances are, your boss’s security setting does not allow macros to run. Or, maybe he chose to disable the macros when he opened the file.

Bottom line? Just because an Excel workbook contains a macro, it is no guarantee that the macro will ever be executed. It all depends on the security setting and whether the user chooses to enable or disable macros for that file.

In order to work with this book, you will need to enable macros for the files you work with. My advice is to use the second security level. Then when you open a file that you’ve created, you can simply enable the macros. If you open a file from someone you don’t know, you should disable the macros and check the VBA code to ensure that it doesn’t contain anything destructive or malicious.

How This Book Is Organized

I divided this book into six major parts, each of which contains several chapters. Although I arranged the chapters in a fairly logical sequence, you can read them in any order you choose. Here’s a quick preview of what’s in store for you.

Part I: Introducing VBA

Part I has but two chapters. I introduce the VBA language in the first chapter. In Chapter 2, I let you get your feet wet right away by taking you on a hands-on guided tour.

Part II: How VBA Works with Excel

In writing this book, I assume that you already know how to use Excel. The four chapters in Part II give you a better grasp on how VBA is implemented in Excel. These chapters are all important, so I don’t recommend skipping past them, okay?

Part III: Programming Concepts

The eight chapters in Part III get you into the nitty-gritty of what programming is all about. You may not need to know all this stuff, but you’ll be glad it’s there if you ever do need it.

Part IV: Communicating with Your Users

One of the coolest parts of programming in Excel is designing custom dialog boxes (well, at least I like it). The chapters in Part IV show you how to create dialog boxes that look like they came straight from the software lab at Microsoft.

Part V: Putting It All Together

The chapters in Part VI pull together information from the preceding chapters. You discover how to include your own custom buttons in the Excel user interface, you find out how to develop custom worksheet functions, create add-ins, design user-oriented applications, and even work with other Office applications.

Part VI: The Part of Tens

Traditionally, books in the For Dummies series contain a final part that consists of short chapters with helpful or informative lists. Because I’m a sucker for tradition, this book has two such chapters that you can peruse at your convenience. (If you’re like most readers, you’ll turn to this part first.)

Marginal Icons

Somewhere along the line, a market research company must have shown that publishers can sell more copies of their computer books if they add icons to the margins of those books. Icons are those little pictures that supposedly draw your attention to various features, or help you decide whether something is worth reading.

I don’t know if this research is valid, but I’m not taking any chances. So here are the icons you encounter in your travels from front cover to back cover:

When you see this icon, the code being discussed is available on the Web. Download it, and eliminate lots of typing. See “Get the Sample Files” below, for more information.

This icon flags material that you might consider technical. You may find it interesting, but you can safely skip it if you’re in a hurry.

Don’t skip information marked with this icon. It identifies a shortcut that can save you lots of time (and maybe even allow you to leave the office at a reasonable hour).

This icon tells you when you need to store information in the deep recesses of your brain for later use.

Read anything marked with this icon. Otherwise, you may lose your data, blow up your computer, cause a nuclear meltdown — or maybe even ruin your whole day.

Get the Sample Files

This book has its very own Web site where you can download the example files discussed and view Bonus Chapters. To get these files, point your Web browser to:

www.dummies.com/go/excel2007vba

Having the sample files will save you a lot of typing. Better yet, you can play around with them and experiment with various changes. In fact, I highly recommend playing around with these files. Experimentation is the best way to master VBA.

Now What?

Reading this introduction was your first step. Now, it’s time to move on and become a programmer (there’s that p word again!).

If you’re a programming virgin, I strongly suggest that you start with Chapter 1 and progress in chapter order until you’ve discovered enough. Chapter 2 gives you some immediate hands-on experience, so you have the illusion that you’re making quick progress.

But it’s a free country (at least it was when I wrote these words); I won’t sic the Computer Book Police on you if you opt to thumb through randomly and read whatever strikes your fancy.

I hope you have as much fun reading this book as I did writing it.

Part I

Introducing VBA

In this part . . .

E very book must start somewhere. This one starts by introducing you to Visual Basic for Applications (and I’m sure you two will become very good friends over the course of a few dozen chapters). After the introductions are made, Chapter 2 walks you through a real-live Excel programming session.