Writing Excel Macros with VBA, 2nd Edition

Chapter 1. Introduction

Microsoft Excel is an application of enormous power and flexibility. But despite its powerful feature set, there is a great deal that Excel either does not allow you to do or does not allow you to do easily through its user interface. In these cases, we must turn to Excel programming.

Let me give you two examples that have come up in my consulting practice.

Selecting Special Cells

The Excel user interface does not have a built-in method for selecting worksheet cells based on various criteria. For instance, there is no way to select all cells whose value is between 0 and 100 or all cells that contain a date later than January 1, 1998. There is also no way to select only those cells in a given column that are different from their immediate predecessors. This can be very useful when you have a sorted column and want to extract a set of unique values, as shown in Figure 1-1.

Figure 1-1. Selecting unique values

I have been asked many times by clients if Excel provides a way to make such selections. After a few such questions, I decided to write an Excel utility for this purpose. The dialog for this utility is shown in Figure 1-2. With this utility, the user can select a match type (such as number, date, or text) and a match criterion. If required, the user supplies one or two values for the match. This has proven to be an extremely useful utility.

Figure 1-2. The Select Special utility

In this book, we will develop a simpler version of this utility, whose dialog is shown in Figure 1-3. This book will also supply you with the necessary knowledge to enhance this utility to something similar to the utility shown in Figure 1-2.

Figure 1-3. Select Special dialog

Setting a Chart’s Data Point Labels

As you may know, data labels can be edited individually by clicking twice (pausing in between clicks) on a data label. This places the label in edit mode, as shown in Figure 1-4. Once in edit mode, we can change the text of a data label (which breaks any links) or set a new link to a worksheet cell. Accomplishing the same thing programmatically is also very easy. For instance, the code:

ActiveChart.SeriesCollection(1).DataLabels(2).Text = "=MyChartSheet!R12C2"

sets the data label for the second data point to the value of cell B12. Note that the formula must be in R1C1 notation. (We will explain the code in Chapter 21, so don’t worry about the details now.)

Figure 1-4. A data label in edit mode

Unfortunately, however, Excel does not provide a simple way to link all of the data labels for a data series with a worksheet range, beyond doing this one data label at a time. In Chapter 21, we will create such a utility, the dialog for which is shown in Figure 1-5. This dialog provides a list of all the data series for the selected chart. The user can select a data series and then define a range to which the data labels will be linked or from which the values will be copied. If the cell values are copied, no link is established, and so changes made to the range are not reflected in the chart. There is also an option to control whether formatting is linked or copied.

Figure 1-5. Set Data Labels dialog

I hope that these illustrations have convinced you that Excel programming can at times be very useful. Of course, you can do much more mundane things with Excel programs, such as automating the printing of charts, sorting worksheets alphabetically, and so on.

Topics in Learning Excel Programming

In general, the education of an Excel programmer breaks down into a few main categories, as follows.

The Visual Basic Editor

First, you need to learn a bit about the environment in which Excel programming is done. This is the so-called Visual Basic Editor or Excel VBA Integrated Development Environment (IDE for short). We take care of this in Chapter 3 and Chapter 4.

The Basics of Programming in VBA

Next, you need to learn a bit about the basics of the programming language that Excel uses. This language is called Visual Basic for Applications (VBA). Actually, VBA is used not only by Microsoft Excel, but also by the other major components in the Microsoft Office application suite: Access, Word, and PowerPoint. Any application that uses VBA in this way is called a host application for VBA. (There are also a number of non-Microsoft products that use VBA as their underlying programming language. Among the most notable is Visio, a vector-based drawing program.) It is also used by the standalone programming environment called Visual Basic (VB).

We will discuss the basics of the VBA programming language in Chapter 5 through Chapter 8.

Object Models and the Excel Object Model

Each VBA host application (Word, Access, Excel, PowerPoint, Visual Basic) supplements the basic VBA language by providing an object model to deal with the objects that are particular to that application.

For instance, Excel VBA includes the Excel object model , which deals with such objects as workbooks, worksheets, cells, rows, columns, ranges, charts, pivot tables, and so on. On the other hand, the Word object model deals with such objects as documents, templates, paragraphs, fonts, headers, tables, and so on. Access VBA includes two object models, the Access object model and the DAO object model, that allow the programmer to deal with such objects as database tables, queries, forms, and reports. (To learn more about the Word, Access, and DAO object models, see my books Learning Word Programming and Access Database Design and Programming, also published by O’Reilly.)

Thus, an Excel programmer must be familiar with the general notion of an object model and with the Excel object model in particular. We discuss object models in general in Chapter 9, and our discussion of the Excel object model takes up most of the remainder of the book.

Incidentally, the Excel object model is quite extensive—a close second to the Word object model in size and complexity, with almost 200 different objects.

Lest you be too discouraged by the size of the Excel object model, I should point out that you only need to be familiar with a handful of objects to program meaningfully in Excel VBA. In fact, as we will see, the vast majority of the “action” is related to just seven objects: Application, Range, WorksheetFunction, Workbook, Worksheet, PivotTable, and Chart.

To help you get an overall two-dimensional picture of the Excel object model, as well as detailed local views, I have written special object browser software. (The object browser comes with over a dozen other object models as well.) For more information, please visit http://www.romanpress.com.

Whether you are interested in Excel programming to be more efficient in your own work or to make money writing Excel programs for others to use, I think you will enjoy the increased sense of power that you get by knowing how to manipulate Excel at the programming level. And because Excel programming involves accessing the Excel object model by using the Visual Basic for Applications programming language—the same programming language used in Microsoft Word, Access, and PowerPoint—after reading this book, you will be half-way to being a Word, Access, and PowerPoint programmer as well!