Chapter 3: Exploring Visual Basic for Applications

---

This chapter provides an overview of how macros work in the context of Microsoft Office Excel 2003. The second part of the chapter covers macro recording and other related topics, but the first part steps back and gives you some perspective on what is happening when you create a macro. The quick version of that story is that Visual Basic for Applications (VBA) sees Excel as a series of objects that have attributes to describe them and actions those objects “know” how to take. Chapter 1, “What’s New in Microsoft Office Excel 2003,” showed you some of the basic elements you’ll encounter when using and programming Excel; this chapter takes that analysis a few steps further by showing you how object-oriented programming languages represent those elements, and how to manipulate those objects using the macro recorder.

An Overview of Object-Oriented Programming

A computer program is, at its base, nothing more than a set of instructions the computer executes in a specified order. In Excel, that order may change based on the contents of the worksheet the program is working with, but one fundamental principle behind programming languages is that if you give a program an identical data set to work with you will get the same result every time.

The first generation of popular programming languages were procedural languages, which meant that programmers designed an algorithm, or procedure, for the program to follow and defined variables (placeholders for values) as the program developed. As programs became increasingly complex, the need for descriptive variable names increased in importance. While it’s easy to create a variable named price to store the price of a product you offer for sale in a store, it’s difficult to write an expandable program that can keep track of all of the prices in an ever-changing product inventory. Every time you wanted to add a product to your store, you would need to create a new variable. Doing that once or twice is no big deal, but if you write a lot of programs and want to save time and effort by reusing your code, you need some way of organizing your program around the things in your environment.

Procedural programmers attempted to solve the problem by representing the things in their environment using an abstract data type, which is a collection of characteristics and operations that reflect the values and actions associated with something you need to represent in a program (such as a product). For example, a garden supply store could have both an indoor sales area and a greenhouse, with products associated with each location. The abstract data type product might have values reflecting the name of the product, the category to which the product belongs, the product’s price, the product’s supplier, a description, and so on. It’s important to note, however, that defining an abstract data type for a product doesn’t create a place to hold the values and actions associated with that product. Instead, you would need to create an instance of the product abstract data type to store the data and actions associated with the new brand of potting soil you just started offering for sale in your garden supply store. The instance would have a unique identifier within the system, such as product001, and the program would know that the product name, category, price, description, and supplier would all refer to that particular product.

While abstract data types are a handy way to define sets of variables in a program, the underlying structure of the programming languages that use abstract data types is still procedural because there can be routines that exist outside of the abstract data types. In an object-oriented programming language, every aspect of your computer code is based around the things in your environment. Those “things,” not surprisingly, are represented as objects, and all actions and data are encapsulated within those objects. In Excel, those objects could be workbooks, worksheets, ranges of cells, or external files. In general, there are four aspects of objects you can use to flesh out a program:

• Properties

• Methods

• Events

• Collections

Properties

In brief (actually, in total as well), properties are variables that describe some aspect of the object in which they are included. A common property for objects in Excel is Name, which holds the identifying value you or Excel assigned to the workbook, worksheet, cell range, or other object to which you’re referring. If you change the worksheet’s name, whether by using VBA code or by right-clicking the worksheet’s tab on the tab bar, clicking Rename, and editing the value, you change the value that is stored in the Name property. You can set new values for some worksheet properties (such as Name) directly, but to change other workbook properties you need to either take action using the Excel interface (such as by protecting a cell range) or a method (described in the next subsection).

In VBA, properties are referred to in a program using dot notation, where the object name is written first, the property name is written second, and the two elements are separated by a period. For example, to change the name of a worksheet, you would use the Worksheet.Name property. Changing the name of a worksheet when you’ve edited or updated the values in one or more worksheet cells lets you and your colleagues know that the data on that sheet is new and should be checked before the worksheet is included in any final written products.

Methods

A method is an action that an object “knows” how to perform. For example, you probably know that the worksheet displayed in the Excel window is referred to as the active worksheet. In Excel VBA, you can change the worksheet you’re affecting with your VBA code by calling the target worksheet’s Activate method. After the Activate method runs, the worksheet to which it is attached moves to the front of the worksheets in the Excel window and becomes available for editing. As with properties, methods are called using dot notation. To recalculate all of the formulas in a worksheet, for example, you would call the Worksheet.Calculate method.

Events

Just as a property is a quantifiable attribute of an object and a method is an action an object knows how to take, an event is an action an object recognizes as having happened. For example, Excel 2003 knows about the following events (among many others):

• A workbook is opened or closed.

• A worksheet is activated or deactivated.

• A workbook is saved.

• A chart is clicked.

• A key (or combination of keys) is pressed.

• Data is typed into a cell.

• The formulas in a worksheet are recalculated.

• A hyperlink is followed.

Excel comes with a number of event handlers, or code routines that watch for particular actions to occur. When one of those actions does occur, and you’ve told Excel what you want it to do when the event happens, Excel will run the code in your event handler. For example, if after creating a new workbook you want Excel to display all open workbooks as a cascaded set of windows, you could create the following event handler:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
Application.Windows.Arrange xlArrangeStyleCascade
End Sub

Don’t worry if you’re not sure what each and every element of the event handler routine does; for now you can concentrate on the middle line of code, which tells the Excel application to arrange its windows using the cascade style. It’s the same result that would occur if you clicked Window, Arrange, Cascade in the Excel menu system, but if it’s an action you want to happen every time a particular event occurs, you can use VBA to make it happen and save you the trouble.

Collections

The final element of object-oriented programming with which you should be familiar is the collection. As the name implies, a collection is a group of objects of the same type that are contained within another object. For example, a workbook contains a collection of one or more worksheets. If you wanted to make a change to every worksheet in a workbook, you could step through every worksheet in the collection and make the change programmatically.

If you’ve programmed before, you’ve probably run into the For…Next loop, which lets you repeat a set of instructions a number of times using something like the following sequence, which adds the directory path of the active workbook to the right section of the footer on the first three worksheets:

For i = 1 to 3
Worksheets(i).PageSetup.RightFooter = Path
Next i

The problem with hard-coding (that is, assigning a set value to) the upper limit of a For…Next loop is that you would need to change the code every time you added or deleted a worksheet. That’s no big deal once or twice, but if you’re managing a lot of code you’ll inevitably forget to change it in a few places, causing errors you’ll have to fix. Worse yet, those errors might not be noticed until the proofreader discovers that the first 500 printed copies of your annual report aren’t formatted correctly and you’ve turned off your wireless phone as you while away your time on the beach. Yes, you can use a bit of code to discover the number of worksheets in your workbook, but there’s a simpler way to do it: use a For Each…Next loop instead. For Each…Next loops find the number of objects in a collection, such as worksheets in a workbook, and step through each occurrence. In this example, the preceding code would be written this way.

For Each Wksht in Worksheets
Wksht.PageSetup.RightFooter = Path
Next Wksht

Instead of incrementing the value in a standard For…Next loop, the For Each…Next loop simply looks for the next member of the Worksheets collection and stops when it doesn’t find one.

For more information on For…Next and For Each…Next loops, see “Controlling Program Flow” in Chapter 4, “VBA Programming Starter Kit.”

---