Topics Covered

Preface

Welcome to Excel 2007 Power Programming with VBA . If your job involves developing spreadsheets 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 2007 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 2007 Ribbon
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 2007, 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 2007. Although most of the material also applies to Excel 2000 and later versions, I assume that you are using Excel 2007. Although Excel 2007 is radically different from its predecessors, the VBA environment has not 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 2007 for your development work.

Most of the material in this book also applies to Excel for Macintosh. However, I did no compatibility testing with the Mac version, so you're on your own.

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 (1024 — 768 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

Excel 2007 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

The large round icon in the upper left corner of Excel 2007's window is knows as the Office Button. When I refer to commands that use the Office Button, I abbreviate it as Office. For example, the following command displays the Excel Options dialog box:

Office Excel Options

VBA editor commands

The VBA editor is the window in which you work with your VBA code. The VBA 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 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 upper- and lowercase 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.