Introduction

Welcome to Excel 2010 Power Programming with VBA. If your job involves developing Excel workbooks that others will use — or if you simply want to get the most out of Excel — you've come to the right place.

Topics Covered

This book focuses on Visual Basic for Applications (VBA), the programming language built into Excel (and other applications that make up Microsoft Office). More specifically, it will show you how to write programs that automate various tasks in Excel. This book covers everything from recording simple macros through creating sophisticated user-oriented applications and utilities.

This book does not cover Microsoft Visual Studio Tools for Office (VSTO). VSTO is a relatively new technology that uses Visual Basic .NET and Microsoft Visual C#. VSTO can also be used to control Excel and other Microsoft Office applications.

What You Need to Know

This is not a book for beginning Excel users. If you have no experience with Excel, a better choice might be my Excel 2010 Bible, which provides comprehensive coverage of all the features of Excel. That book is meant for users of all levels.

To get the most out of this book, you should be a relatively experienced Excel user. I didn't spend much time writing basic how-to information. In fact, I assume that you know the following:

• How to create workbooks, insert sheets, save files, and so on

• How to navigate through a workbook

• How to use the Excel Ribbon user interface

• How to enter formulas

• How to use Excel's worksheet functions

• How to name cells and ranges

• How to use basic Windows features, such as file management techniques and the Clipboard

If you don't know how to perform the preceding tasks, you could find some of this material over your head, so consider yourself warned. If you're an experienced spreadsheet user who hasn't used Excel 2010, Chapter 2 presents a brief overview of what this product offers.

What You Need to Have

To make the best use of this book, you need a copy of Excel 2010. Although most of the material also applies to Excel 2003 and later versions, I assume that you're using Excel 2010. Although Excel 2007 and Excel 2010 are radically different from their predecessors, the VBA environment hasn't changed at all. If you plan to develop applications that will be used in earlier versions of Excel, I strongly suggest that you don't use Excel 2010 for your development work. Rather, use the earliest version of Excel that the target audience will be using.

This book isn't intended for any version of Excel for Macintosh. Any computer system that can run Windows will suffice, but you'll be much better off with a fast machine with plenty of memory. Excel is a large program, and using it on a slower system or a system with minimal memory can be extremely frustrating.

I recommend using a high-resolution video driver (1280 × 1024 is adequate, and 1600 × 1200 is even better). For optimal results, try a dual-monitor system and place Excel on one screen and the Visual Basic Editor on the other. You'll soon become spoiled.

To use the examples on the companion CD, you also need a CD-ROM drive.

Conventions in This Book

Take a minute to skim this section and learn some of the typographic conventions used throughout this book.

Excel commands

Beginning with Excel 2007, the product features a brand-new “menu-less” user interface. In place of a menu system, Excel uses a context-sensitive Ribbon system. The words along the top (such as Insert, View, and so on) are known as tabs. Click a tab, and the Ribbon of icons displays the commands that are most suited to the task at hand. Each icon has a name that is (usually) displayed next to or below the icon. The icons are arranged in groups, and the group name appears below the icons.

The convention I use in this book is to indicate the tab name, followed by the group name, followed by the icon name. So, for example, the command used to toggle word wrap within a cell is indicated as:

Home⇒Alignment⇒Wrap Text

Clicking the first tab, labeled File, takes you to a new screen called Backstage. The Backstage window has commands along the left side of the window. To indicate Backstage commands, I use the word File, followed by the command. For example, the following command displays the Excel Options dialog box:

File⇒Excel Options

Visual Basic Editor commands

The Visual Basic Editor is the window in which you work with your VBA code. The VB Editor uses the traditional menu-and-toolbar interface. A command like the following means to click the Tools menu and select the References menu item:

Tools⇒References

Keyboard conventions

You need to use the keyboard to enter data. In addition, you can work with menus and dialog boxes directly from the keyboard — a method that you might find easier if your hands are already positioned over the keys.

Input

Input that you are supposed to type from the keyboard appears in boldface — for example, enter =SUM(B2: B50) into cell B51.

More lengthy input usually appears on a separate line in a monospace font. For example, I might instruct you to enter the following formula:

=VLOOKUP(StockNumber,PriceList,2)

VBA code

This book contains many snippets of VBA code, as well as complete procedure listings. Each listing appears in a monospace font; each line of code occupies a separate line. (I copied these listings directly from the VBA module and pasted them into my word processor.) To make the code easier to read, I often use one or more tabs to create indentations. Indentation is optional, but it does help to delineate statements that go together.

If a line of code doesn't fit on a single line in this book, I use the standard VBA line continuation sequence: At the end of a line, a space followed by an underscore character indicates that the line of code extends to the next line. For example, the following two lines are a single code statement:

If Right(ActiveCell, 1) = “!” Then ActiveCell _

= Left(ActiveCell, Len(ActiveCell) - 1)

You can enter this code either on two lines, exactly as shown, or on a single line without the underscore character.

Functions, filenames, and named ranges

Excel's worksheet functions appear in uppercase font, like so: “Enter a SUM formula in cell C20.” VBA procedure names, properties, methods, and objects appear in monospace font: “Execute the GetTotals procedure.” I often use mixed uppercase and lowercase letters to make these names easier to read.

I also use the monospace font for filenames and named ranges in a worksheet — for example: Open myfile.xlsm and select the range named data.

Mouse conventions

If you're reading this book, you're well versed in mouse usage. The mouse terminology I use is all standard fare: pointing, clicking, right-clicking, dragging, and so on.

What the Icons Mean

Throughout the book, I use icons to call your attention to points that are particularly important:

I use this icon to indicate that the material discussed is new to Excel 2010.

I use Note icons to tell you that something is important — perhaps a concept that could help you master the task at hand or something fundamental for understanding subsequent material.

Tip icons indicate a more efficient way of doing something or a technique that might not be obvious.

These icons indicate that an example file is on the companion CD-ROM. (See “About the Companion CD-ROM,” later in this Preface.) This CD holds many of the examples that I show in the book.

I use Caution icons when the operation that I'm describing can cause problems if you're not careful.

I use the Cross-Reference icon to refer you to other chapters that have more to say on a subject.

How This Book Is Organized

The chapters of this book are grouped into eight main parts.

Part I: Some Essential Background

In this part, I set the stage for the rest of the book. Chapter 1 presents a brief history of spreadsheets so that you can see how Excel fits into the big picture. In Chapter 2, I offer a conceptual overview of Excel 2010 — quite useful for experienced spreadsheet users who are switching to Excel. In Chapter 3, I cover the essentials of formulas, including some clever techniques that might be new to you. Chapter 4 covers the ins and outs of the various files used and generated by Excel.

Part II: Excel Application Development

This part consists of just two chapters. In Chapter 5, I broadly discuss the concept of a spreadsheet application. Chapter 6 goes into more detail and covers the steps typically involved in a spreadsheet application development project.

Part III: Understanding Visual Basic for Applications

Chapters 7 through 11 make up Part III, and these chapters include everything that you need to know to learn VBA. In this part, I introduce you to VBA, provide programming fundamentals, and detail how to develop VBA subroutines and functions. Chapter 11 contains many useful VBA examples.

Part IV: Working with UserForms

The four chapters in this part cover custom dialog boxes (also known as UserForms). Chapter 12 presents some built-in alternatives to creating custom UserForms. Chapter 13 provides an introduction to UserForms and the various controls that you can use. Chapters 14 and 15 present many examples of custom dialog boxes, ranging from basic to advanced.

Part V: Advanced Programming Techniques

Part V covers additional techniques that are often considered advanced. The first three chapters discuss how to develop utilities and how to use VBA to work with pivot tables and charts (including the new Sparkline graphics). Chapter 19 covers event handling, which enables you to execute procedures automatically when certain events occur. Chapter 20 discusses various techniques that you can use to interact with other applications (such as Word). Chapter 21 concludes Part V with an in-depth discussion of creating add-ins.

Part VI: Developing Applications

The chapters in Part VI deal with important elements of creating user-oriented applications. Chapter 22 discusses how to modify the new Ribbon interface. Chapter 23 describes how to modify Excel's shortcut menus. Chapter 24 presents several different ways to provide online help for your applications. In Chapter 25, I present some basic information about developing user-oriented applications, and I describe such an application in detail.

Part VII: Other Topics

The six chapters in Part VII cover additional topics. Chapter 26 presents information regarding compatibility. In Chapter 27, I discuss various ways to use VBA to work with files. In Chapter 28, I explain how to use VBA to manipulate Visual Basic components such as UserForms and modules. Chapter 29 covers the topic of class modules. Chapter 30 explains how to work with color in Excel. I finish the part with a useful chapter that answers many common questions about Excel programming.

Part VIII: Appendixes

Four appendixes round out the book. Appendix A contains useful information about Excel resources online. Appendix B is a reference guide to all VBA's keywords (statements and functions). I explain VBA error codes in Appendix C, and Appendix D describes the files available on the companion CD-ROM.

About the Companion CD-ROM

The inside back cover of this book contains a CD-ROM that holds many useful examples that I discuss in the text. When I write about computer-related material, I emphasize learning by example. I know that I learn more from a well-thought-out example than from reading a dozen pages in a book. I assume that this is true for many other people. Consequently, I spent more time developing the examples on the CD-ROM than I did writing chapters.

The files on the companion CD-ROM aren't compressed, so you can access them directly from the CD.

Refer to Appendix D for a description of each file on the CD-ROM.

About the Power Utility Pak Offer

Toward the back of the book, you'll find a coupon that you can redeem for a discounted copy of my popular Power Utility Pak software. PUP is an award-winning collection of useful Excel utilities and many new worksheet functions. I developed this package exclusively with VBA.

I think you'll find this product useful in your day-to-day work with Excel. You can also purchase the complete VBA source code for a nominal fee. Studying the code is an excellent way to pick up some useful programming techniques.

You can take Power Utility Pak for a test drive by installing the 30-day trial version available at my Web site:

http://spreadsheetpage.com

How to Use This Book

You can use this book any way that you please. If you choose to read it from cover to cover, be my guest. But because I'm dealing with intermediate-to-advanced subject matter, the chapter order is often immaterial. I suspect that most readers will skip around, picking up useful tidbits here and there. If you're faced with a challenging task, you might try the index first to see whether the book specifically addresses your problem.

Reach Out

The publisher and I want your feedback. After you've had a chance to use this book, please take a moment to visit the Wiley Publishing Web site to give us your comments. (Go to www.wileycom and then click the Contact Us link.) Please be honest in your evaluation. If you thought a particular chapter didn't tell you enough, let us know. Of course, I would prefer to receive comments like, “This is the best book I've ever read,” or “Thanks to this book, I was promoted and now make $124,000 a year.”

I get at least a half dozen questions every day, via e-mail, from people who have read my books. I appreciate the feedback. Unfortunately, I simply don't have the time to reply to specific questions. Appendix A provides a good list of sources that can answer your questions.

I also invite you to visit my Web site, which contains lots of Excel-related material. The URL is

http://spreadsheetpage.com