Chapter 1 Excel Objects

Click to open or copy the sample files for this book

This chapter provides an overview of the Microsoft Excel object model. An object in Excel is something that can be programmed or, in essence, controlled. Excel's object model contains 128 different objects, ranging from simple objects such as rectangles and textboxes to complicated objects such as pivottables and charts. Before you can create Excel applications, you must have not only a good understanding of the functionality that various Excel objects offer but also a firm knowledge of the structure of the Excel object model.

Excel objects are discrete entities that offer various pieces of data analysis functionality. You create applications in Excel by tying these objects together using Visual Basic for Applications (VBA), Excel's new macro language. (Chapter 2 gives you a complete overview of the VBA language.) The end of this chapter focuses on showing you how to begin building custom applications with four commonly used Excel objects: Application, Workbook, Worksheet, and Range. Additional objects are discussed in detail throughout the course of this book. Note that all Excel objects are fully documented in the online VBA Help that comes with Excel 5—and documented in printed form in the Microsoft Excel Visual Basic for Applications Reference (Microsoft Press).

Objects and Their Properties and Methods

All objects in Excel have what are known as "properties" and "methods." VBA is a tool through which you can control Excel objects by manipulating their properties and methods. To develop custom applications in Excel, you must understand the concepts of objects, properties, and methods as well as know the specific details about the properties and methods of Excel's objects.

F Y I

VBA Naming Conventions

In VBA code, the name of an object, a property, or a method is written as one word (for example, the OptionButton object). In addition, all such names begin with a capital letter, and if a name consists of two concatenated words, the first letter of the second word is capitalized as well (for example, the HasPassword property or the PrintPreview method).

A Real-World Example

Let's take a look at a real-world example of an object and its properties and methods. Think of everything that you encounter in your daily life that can be controlled: your car, your phone, your television, your microwave, and even your computer. Just about everything you encounter can be controlled in some way. Even the earth is subject to the gravitational control of the sun and the other planets. These are all objects in the true definition of the word. In fact, every definable thing is an object. So, in essence, an object can be defined as some "thing."

In addition, all objects can be described. For each object, there is a set of associated adjectives that represent various measurable aspects of the object. A property is a measurable aspect of an object. Take a car as an example. It is an object that has properties of color (red), year (1990), make (Nissan), number of seats (two), and top speed (145 mph), and numerous other measurable aspects. Properties represent all aspects of an object. If you measure all the properties of an object, you'll have a complete and accurate description of that object.

As properties are adjectives, methods are, in effect, verbs that represent actions that can be performed on or by an object. If we look again at the example of a car, the car's methods would include start, accelerate, decelerate, and stop.

Examples of Excel Workbook Properties

Excel objects are similar to real-world objects in the sense that each Excel object has a unique set of properties used to describe it and a unique set of methods representing the actions that can be performed on or by it. Let's take a look at the workbook as an example of an Excel object. Workbooks are simply Excel files. When describing a Workbook object, we're simply talking about the characteristics of an Excel file. The following is a partial list of the properties of an Excel Workbook object:

As we review this set of properties, a couple of important facts come to light. First, all properties have values associated with them—be they string values (as in Workbook Name), Boolean values (as in the Workbook HasPassword property), numeric values, or some other kind. Second, properties can be specific to a single object, or they can apply to several different objects. For example, the Workbook object is the only Excel object that has a HasPassword property; it is also the only object that has a ReadOnly property. On the other hand, 58 Excel objects have a Name property. In fact, Name is one of the most prevalent properties in Excel, which makes sense because most real-life objects have names.

Getting and Setting Properties

When dealing with properties using VBA in Excel, you can perform two types of actions: You can set a property, or you can get a property setting. When doing either, you must reference both the object name and the property name, separating the two with the dot operator (.). In addition, you set or get a property by using the equals operator (=). For example, to set the Author property of the workbook named BOOK1.XLS, you would use the following VBA syntax:

Workbooks("BOOK1.XLS").Author = "Jane Smith"

The five parts of the line of code above are as follows:

Note When writing VBA code to set property values, you must include all of the elements listed above. The Workbook object here is specified using an index to the Workbooks collection object. You'll find an explanation of this kind of reference in the section "Referencing Objects: Singular Objects vs. Objects in Collections," beginning on page 10. We'll go into more detail about each of the elements and the various ways you can reference them later in this book.

F Y I

Using This Code in a VBA Macro

To write an entire VBA macro that sets the Author property of the Workbook object, include the keyword Sub followed by the macro name on the first line of the macro and the keywords End Sub on the last line of the macro, as in the following example:

Sub SetAuthorName()
Workbooks("BOOK1.XLS").Author = "Jane Smith"
End Sub

Getting property settings works in much the same way as does setting them except that the elements in the VBA command appear in the reverse order. Also, when getting a property setting, you usually use a variable to hold the value of the property. The line of code below assigns the value of the Author property of the Workbook object to a string variable named AuthorName:

AuthorName = Workbooks("BOOK1.XLS").Author

The parts of the line of VBA code used to get a property are similar to those used to set a property:

Note If you haven't yet saved your workbook, its name is still simply Book1. If you don't specify a new name using the Save As command, Excel adds the file extension .XLS when it saves the file, and the name becomes BOOK1.XLS. (For Macintosh users, the default name of a new file is WORKBOOK1, and a file extension is not automatically added.)

Examples of Excel Workbook Methods

Excel's Workbook object also has many methods that represent actions that can be performed on or by the workbook. Here are a few of these methods:

Calling Methods

The VBA syntax for calling a method differs from that of setting or getting properties. All that is required for a method call is a reference to the object and the method. In addition, each method in Excel has a set of "arguments," or additional pieces of information, that can be specified to indicate how the method is to be carried out. In VBA, many methods have optional arguments—that is, depending on how you want a particular method to be executed, you can specify all, some, or none of the method's arguments. For example, the Close method of the Workbook object has three arguments:

Note The first letter of an argument name is most often lowercase. And if an argument name is made up of two concatenated words, the first letter of the second word is usually but not always capitalized.

Passing Arguments to Methods

Here is an example of the VBA syntax you would use to call the Close method without passing any arguments:

Workbooks("BOOK1.XLS").Close

If you call the Close method without passing any arguments, the arguments take on default values. For example, if you omit the first argument, saveChanges, and changes have been made to the file, Excel by default asks the user whether the file should be saved:

The default for the fileName argument is the file's current name, and the default for the routeWorkbook argument is False. This example shows the VBA syntax for calling the Close method while passing all three arguments:

Workbooks("BOOK1.XLS").Close True, "MYBOOK.XLS", False

When passing the arguments, you merely append them to the method call and separate multiple argument values with commas; be certain the arguments are in the proper order. For Close, for example, the appropriate order is saveChanges, fileName, and routeWorkbook. Also be aware that two different methods of passing arguments are available when you call a method. You can pass arguments "by name," or you can pass arguments "by order," as in the example above. When you pass an argument by name, you must specify three elements: the name of the argument (for example, saveChanges), followed by the colon-equals assignment operator (:=), and then the value for the argument. The example below shows the appropriate VBA syntax for executing the Close method while passing all arguments by name:

Workbooks("BOOK1.XLS").Close saveChanges:=True, _ 
  fileName:="BOOK1.XLS", routeWorkbook:=False

When you pass arguments by name in VBA, you need not pass them in order. For example, you can accomplish the same method call as the one shown above by passing the arguments by name in a different order:

Workbooks("BOOK1.XLS").Close routeWorkbook:=False, _ 
  saveChanges:=True, fileName:="BOOK1.XLS"

F Y I

The Line Continuation Character

The underscore character (_) at the end of a line in VBA indicates that the code is continued on the next line; you must precede the underscore with a space. In VBA, one line of code can contain up to 1024 characters, but breaking up a line can make your code more readable. We'll use the continuation character in this book to break up long lines of code in order to fit them on the page.

To call the Close method and pass only the first argument, saveChanges, you could pass the argument by order using the following:

Workbooks("BOOK1.XLS").Close True

Or you could pass the argument by name using the following:

Workbooks("BOOK1.XLS").Close saveChanges:=True

To pass the first and third arguments, you could do so by order by leaving a blank for the second argument:

Workbooks("BOOK1.XLS").Close True, , False

Or you could do so by name:

Workbooks("BOOK1.XLS").Close saveChanges:=True, routeWorkbook:=False

Referencing Objects: Singular Objects vs. Objects in Collections

The examples above showed that when you reference a property or method of a particular object, you simply append the name of the property or method to the object reference using the dot operator (.). You can reference objects, though, in two different ways: You can reference the name of a single object directly, or you can reference an index in a collection of objects. To understand these two ways of referencing objects, it's important that you have a clear understanding of collections. A collection, in its simplest definition, is a group of like objects. All Excel objects fall into two classes: singular objects and objects in a collection. Singular objects are referenced directly by name. Objects in a collection are referenced by an index in the collection. Approximately half of the objects in Excel are singular objects, and the other half are objects in collections.

Note Three objects in Excel—Range, Sheets, and DrawingObjects—deviate slightly from this classification scheme. The Range object is classified as a singular object, yet it assumes characteristics of both a singular object and an object in a collection. Sheets and DrawingObjects are both collections that contain collections of several other objects. Range, Sheets, and DrawingObjects are discussed in the following sections: "The Range Object—An Exception," beginning on page 15 in this chapter; "Multi-Sheet Workbooks," beginning on page 39 in Chapter 2; and "The DrawingObjects Collection," beginning on page 135 in Chapter 3.

F Y I

Collections Are Also Objects

Although a collection contains groups of objects, a collection itself is also an object—a singular object. It is an object that contains a group of like objects. This concept might be difficult to grasp at first, but it will be easier to understand as you become more familiar with the Excel object model. Perhaps an example would best illustrate the point.

The Worksheets collection contains all Worksheet objects. Each Worksheet object in the collection has properties and methods associated with it. It just so happens that the Worksheets collection also has a different set of properties and methods associated with it. You could say that there are three types of objects in Excel: singular objects, objects in collections, and collections. But technically speaking, collections are really singular objects. We won't dwell on the point, however; simply remember that collections are objects themselves and as such are associated with their own set of properties and methods.

Which Objects Are Singular, and Which Are Objects in Collections?

Of the 128 objects in Excel, you might wonder which are singular objects and which are objects in collections. You could memorize the names of all the objects as well as whether each object is singular or in a collection. Or you could apply two simple rules of intuition and avoid the work of memorization:

RULE 1 A singular object has only one instance in a given context. That is, it is a unique object—there is only one of this type of object in this object's context.

RULE 2 An object in a collection has multiple instances in a given context. That is, there are several of this type of object in the same context.

Applying these rules does require that you understand the 128 objects in Excel, and you will likely understand them after you read this book. In the meantime, let's look at a few simple examples. Excel has an object named Application. The Application object is a singular object that represents the Excel application. It is singular because Excel has only one Application object. The Font object is another example of a singular object. For any given cell on an Excel worksheet, only one instance of the Font object exists. Font does have multiple properties, including Name (Courier, Times New Roman, Helvetica, and so on), Bold (True or False), Italic (True or False), and Size (the size of the font in points). Yet, for a particular cell, there is only one Font object. Therefore, Font is a singular object.

The Worksheet object, on the other hand, is an object in a collection. Any Excel workbook file can have multiple worksheets and, therefore, multiple instances of the Worksheet object. For example, if you open a new workbook file in Excel, by default 16 worksheets are included in the workbook file—or 16 instances of the Worksheet object. Because the Worksheet object can exist in multiple instances within the same context, it is, by definition, an object in a collection. The Chart object provides another example. In an Excel workbook file, you can have multiple charts and, therefore, multiple instances of a Chart object. As such, the Chart object is an object in a collection. Figure 1-1 on the next page displays a collection of Chart objects.

Figure 1-1. A collection of Chart objects.

Referencing Objects Directly or by Collection Index

As mentioned, singular objects are referenced directly, and objects in collections are referenced by specifying an index in the collection in which the object exists. Let's take a look first at an example of referencing a singular object. The Application object, which is singular, has several properties, including the following:

To set the Caption property, you reference the Application object directly:

Application.Caption = "My Custom Application"

Note

Macintosh users should use the following:

ActiveWindow.Caption = "My Custom Application"

Below you see the result of setting the Caption property of the Application object:

To turn off screen updating during macro execution, you set the ScreenUpdating property to False, again referencing the Application object directly:

Application.ScreenUpdating = False

Now let's take a look at referencing an object in a collection. Because the Worksheet object is an object in a collection, you must reference the Worksheet object by specifying an index in the Worksheets collection. To set the Name property of the Worksheet object that corresponds to the first worksheet, for example, you use the following code:

Worksheets(1).Name = "My First Worksheet"

The following screen example shows the result of setting the Name property of the Worksheet object:

And to set the name of the third worksheet, you use the following VBA code:

Worksheets(3).Name = "My Third Worksheet"

Note When you index the Worksheets collection to reference a specific Worksheet object, be sure to use the plural "Worksheets." You must do so for most collections in Excel, with only two exceptions—Range and SeriesCollection.

Indexing Collections by Number or by Name

You can specify an index in a collection by number (as shown in the examples above) or by name. When writing VBA code, you'll encounter times when using one or the other indexing scheme is advantageous. For example, it is usually easiest to process the items in a collection one by one using a numeric index. If you have a specific item (such as a "totals" worksheet), however, you can use a name index to jump directly to it without having to keep track of its numeric position in the collection.

Keep in mind when indexing by number that worksheets are numbered according to the order in which they are added to the workbook file. If you try to reference a Worksheet object by specifying an index number that is higher than the number of worksheets in the Worksheets collection, you will receive an error message at runtime. For example, if only 16 worksheets exist in the active workbook, any attempt to set properties of or call methods on Worksheets(17) will fail.

If you don't know the index number for an object in a collection, you can reference it by name instead. Each Worksheet object in a Worksheets collection has a name associated with it. These are the names displayed on the worksheet tabs at the bottom of the Excel screen. Let's assume you want to set the Visible property of a worksheet. The Visible property can take a value of True or False; if Visible is True, the worksheet is displayed, and if Visible is False, the worksheet is hidden from view. If the worksheet is named My Worksheet, for example, you might have no idea what numeric place that particular Worksheet object occupies in the Worksheets collection. Therefore, you could index the Worksheets collection by name instead of by number, as in the following example:

Worksheets("My Worksheet").Visible = False

Tip When indexing a collection by name, you must indicate the particular object name in double quotation marks within the parentheses. Also, VBA ignores case when indexing objects by name. For example, Worksheets("MY WORKSHEET") is the same as Worksheets("my worksheet").

F Y I

Beware of Changing the Name Property

Take care when you set the Name property of objects in collections because the Name property affects the way in which an object is referenced when indexed by name. For example, when the following two lines of code are executed, the second line generates an error message:

Worksheets("My Worksheet").Name = "New Worksheet Name"
Worksheets("My Worksheet").Visible = False

It generates an error message because the first line changes the name of the My Worksheet worksheet to New Worksheet Name. When VBA attempts in the second line to index the Worksheets collection by name using the worksheet's old name, it cannot find any Worksheet object in the collection by that name, and execution fails.

Let's look at some other examples of indexing collections by name. The Chart object has a property named HasLegend that can take a value of True or False. If HasLegend is True, a legend appears on the chart; if it is False, no legend appears. The Chart object is an object in a collection. Therefore, if you have a chart named Chart1 and you want to have a legend displayed on the chart, you index the Charts collection by name, as in the following code sample:

Charts("Chart1").HasLegend = True

The example chart below shows the result of setting the HasLegend property of the Chart object to True:

Let's suppose you have an application that opens and closes several Excel workbooks, one of which is named BOOK2.XLS. The Workbook object is an object in a collection. So to close BOOK2.XLS, you index the Workbooks collection by name and call the Close method, as in the following example:

Workbooks("BOOK2.XLS").Close

The Range Object--An Exception

We've seen the differences between referencing singular objects and referencing objects in collections and looked at examples of how objects in collections can be indexed either by number or by name. One Excel object—the Range object—falls into the gray area between singular objects and objects in collections. The Range object is used to reference a cell or a group of cells on an Excel worksheet. It is, by definition, a singular object; however, it also exhibits some of the behavior of an object in a collection. For example, to reference a specific range on a worksheet, you must index the Range object by address or by name in a manner similar to the way in which you index an object in a collection.

Suppose you wanted to change the contents of a particular cell. Range has a property named Value that represents the value contained in a cell or a group of cells. In addition, each cell on an Excel worksheet has an address that is specified by combining the column letter displayed at the top of the worksheet and the row number displayed on the left side of the worksheet. For example, the address of the first cell in a worksheet is A1. To set the Value property of the first cell on a worksheet, you could index the Range object using an address, as in the following example:

Range("A1").Value = 1

Here you see the result of setting the Value property of the Range object corresponding to the address A1:

Worksheet ranges can also have names. You can therefore index the Range object using the name of a cell or group of cells instead of an address. In the following code example, the first line sets the Name property of the first cell, referencing the Range object by address. The second line sets the Value property of the same cell, this time referencing the Range object by name:

Range("A1").Name = "FirstCell"
Range("FirstCell").Value = 1

Note

Cells that have names still retain their addresses. In the second line of code above, therefore, an index to the Range object by address would still execute properly. You should be aware, though, that you cannot index the Range object by number. For example, the following code example generates an error message:

Range(1).Value = 1

When you set properties of or call methods on the Range object, you can use an address index that contains multiple cells. For example, the following code sets the Value property of all the cells that fall into the address A1:F20.

Range("A1:F20").Value = 1

The Excel Object Hierarchy

Of the 128 different objects in Excel 5, not all exist on the same level—that is, some objects are contained within others. You can think of these levels as tiers in a hierarchy. (See Figure 1-2, beginning on page 18.) You need to understand this hierarchy and become familiar with the different tiers at which each object exists if you are to reference Excel objects effectively in your VBA code.

The Tiers of the Hierarchy

The topmost tier of the Excel object hierarchy is occupied by a single object: Application. The Application object represents Excel itself, and all other Excel objects fall under Application.

Seven objects are included in the second tier:

The third, fourth, and fifth tiers of the hierarchy include a variety of additional objects used to access functionality that the second tier objects contain. The Excel object hierarchy has a treelike structure. For example, if we descend from the Workbook object on the second tier down to the third tier of the hierarchy, we encounter the Worksheet, Chart, Module, DialogSheet, Style, Window, Name, RoutingSlip, and Mailer objects. Or if we descend from the Toolbar object on the second tier down to the third tier of the hierarchy, we encounter only one object, ToolbarButton.

The diagram in Figure 1-2 shows the Microsoft Excel 5 object model hierarchy. In the diagram, collections appear in boxes, with the names of the objects in the collection in parentheses. Although Excel has 128 objects, a different number of nodes are shown in this diagram. That's because each collection can itself be treated as an individual object. Also, certain objects—such as Font, Border, and Interior—are repeated in several places. The total number of objects does come out to 128.

Refer to the object model diagram shown in Figure 1-2 on the next page when you need help with the hierarchy of Excel 5's objects. For example, if you are writing code to set a property of the Range object, you can see from the diagram that the Range object falls under the Worksheet object in the hierarchy, which in turn falls under the Workbook object, which itself falls under the Application object. Or if you are writing code to call a method on the Legend object, you can see from the diagram that you have to descend from the Application object to the Workbook object and then to the Chart object before you finally reach the Legend object.

Figure 1-2. The Excel object hierarchy.

Using the Excel Object Hierarchy

How does the Excel object hierarchy affect you when you are writing VBA code? To manipulate the properties and methods of an object, you must sometimes reference all objects that lie on the hierarchical path to that object. You traverse that path down to a specific object by using the dot operator (.).

For example, let's suppose you are writing a VBA macro to set the Value property of a Range object that represents the first cell in the first worksheet of the first workbook in Excel. Using the full hierarchical path, the reference to Range appears as follows:

Application.Workbooks(1).Worksheets(1).Range("A1").Value = 1

Referencing the Application object implicitly

Although the code above executes properly in Excel, it is not always necessary to traverse the entire hierarchical object path when setting a property of or calling a method on a particular object. How far from the top of the hierarchy you must begin depends on the context in which the property setting or method call is made. The code example above, which starts at Application and steps all the way down to Range, could be executed anywhere in Excel under any circumstances and will always assign 1 to the Value property of the Range("A1") object on the first worksheet in the first workbook (provided a workbook is open). If this code is executed in Excel, as it would usually be, there is no need to reference the Application object. Application refers to Excel, so in the absence of an explicit reference to the Application object—and as long as the code is executed inside of Excel—VBA understands that the code is to be executed on Excel's Application object. Therefore, we can remove the reference to Application, as shown here:

Workbooks(1).Worksheets(1).Range("A1").Value = 1

Executing this line of code in Excel has the exact same effect as does executing the earlier line of code, which includes the reference to Application.

Referencing the active workbook implicitly

It is possible in Excel to have several workbook files open at one time. However, only one workbook can be active at a time—that is, a user can enter or manipulate data in only one workbook at any given time. The active workbook is displayed in front of all other open workbooks so that the active workbook window is on top in the Excel work area; the inactive workbook windows are displayed behind the active workbook. The preceding line of code uses an index to the Workbooks collection to reference the first workbook in the collection. Let's assume that instead of setting the property of the first workbook, the goal is to set the property of the active workbook. To do so, you use a Workbook object reference named ActiveWorkbook.

Object references such as ActiveWorkbook and ThisWorkbook are explained thoroughly in the "FYI" titled "Accessing Objects Through Properties and Methods," beginning on page 23. For now, here is an example:

ActiveWorkbook.Worksheets(1).Range("A1").Value = 1

This code executes on the workbook that is currently active, which might not be the same as Workbooks(1).

Tip You can also use ThisWorkbook to reference a Workbook object. ThisWorkbook, however, always refers to the workbook that actually holds the code being executed and not necessarily to the active workbook.

If only one workbook is open and that workbook is the one for which you want to set the property, you need not include the workbook reference in the hierarchical object path. VBA implicitly determines that the property setting is to occur on the one open workbook. You could therefore eliminate the workbook reference and use the shortened code shown here:

Worksheets(1).Range("A1").Value = 1

Note If the code above is executed while multiple workbooks are open, the property setting takes place in the active workbook.

Referencing the active sheet implicitly

In the same way that the ActiveWorkbook reference accesses the active workbook, a reference named ActiveSheet accesses the active sheet. If the property setting were to occur on the active worksheet, the preceding line of code could be changed to the following:

ActiveSheet.Range("A1").Value = 1

Here is the result of the altered line of code:

In addition, if the workbook has only one worksheet or if the context is such that the property setting is, by default, to occur on the active worksheet, the worksheet reference could be removed entirely:

Range("A1").Value = 1

This line of code sets the Value property of Range("A1") equal to 1 on the active worksheet. Note, though, that some objects in Excel have what is known as a "default property." Value is the default property of the Range object. That being the case, the line of code above could be shortened even further:

Range("A1") = 1

As mentioned, ActiveWorkbook and ActiveSheet can be used to reference the active workbook and the active worksheet. The active range—those cells on the worksheet that have been selected—can also be referenced by using the Application object's Selection property. Selection, however, can refer to other objects in addition to the Range object. During execution, VBA determines what type of object has been selected and evaluates Selection to the appropriate object. When using Selection, you cannot use default properties, so if Selection is used to set the Value property of a Range object, a specific reference to the Value property must be included, as the following code example shows:

Selection.Value = 1

Executing this code assigns 1 to the Value property of the currently selected range, be it Range("A1") or Range("A1:Z256").

At Which Level Should Object References Begin?

Looking back at the preceding discussion, you can see that—assuming the proper context—all of the following lines of code produce the same result:

Application.Workbooks(1).Worksheets(1).Range("A1").Value = 1
Workbooks(1).Worksheets(1).Range("A1").Value = 1
ActiveWorkbook.Worksheets(1).Range("A1").Value = 1
Worksheets(1).Range("A1").Value = 1
ActiveSheet.Range("A1").Value = 1
Range("A1").Value = 1
Range("A1") = 1
Selection.Value = 1

So at which level on the hierarchical object path should object references begin? If you were to play it safe, you would start all object references at the Application object. By doing so, you would always be certain about the objects in the path, and your code would always execute without error (assuming proper syntax). You would also find yourself typing an enormous amount of code. You should, therefore, try to keep track of the context in which object references are made and traverse the hierarchical object path accordingly, using longer paths where needed to avoid any errors that might arise when you are unsure of the context in which the code will execute. You can, however, abbreviate object references to take advantage of longer object references without having to type so much code; you do so by using object variables and With statements, both of which are covered in Chapter 2.

F Y I

Accessing Objects Through Properties and Methods

Before moving on, let's clarify two points about references to objects. As mentioned, singular objects are referenced by name, and objects in collections are referenced by an index in the collection. When you reference a singular object by name, you are actually using a property that points to the singular object. For example, Legend is a singular object that is contained in the Chart object. The VBA reference to the Legend object in a chart appears as follows:

Charts(1).Legend

In the above line of code, Legend is actually a property of the Chart object—a property that can be used to reference the Legend object associated with that particular chart.

When you specify an index in a collection to reference an object in a collection, you are making a method call; you pass an argument of either an integer or a string that corresponds to the object in the collection that you want returned. This example is used to reference the first worksheet in the first workbook:

Application.Workbooks(1).Worksheets(1)

The reference to Worksheets is actually a call to a method named Worksheets—a method of the Workbook object. The reference to Workbooks is, in turn, a call to a method of the Application object.

This distinction—that singular-object references are actually properties and that object-in-a-collection references are actually methods—is important to understand when you search for help on particular objects in Excel's online VBA Help. For example, if you look up the VBA Help topic for the Chart object, you find a property named Legend. This property returns the Legend object that's contained in the Chart object. On the other hand, if you look up the VBA Help topic for the Workbook object, you find a method named Worksheets. You can pass an argument to this method so that one Worksheet object is returned from the Worksheets collection that is contained in the Workbook object.

In addition, references to ActiveWorkbook, ThisWorkbook, ActiveSheet, and Selection are actually properties. For example, the Application object has a property named ActiveWorkbook that represents the currently active Workbook object. The following line of code calls the Close method on the active workbook:

ActiveWorkbook.Close

You could produce the same result using the full hierarchical object path:

Application.ActiveWorkbook.Close

When you use the ActiveWorkbook property and omit the reference to Application, VBA implicitly determines that the property is that of the Application object. The same holds true for ThisWorkbook, ActiveSheet, and Selection; for these as well, VBA determines the appropriate object by the context of the reference.

This use of properties and methods to reference objects runs counter to the definitions of properties and methods provided at the beginning of this chapter. Don't let this fact alter your view of the overall structure of the object model; it is relevant only to how the object model is documented in Excel.

Online VBA Help

Full online help is available for all objects, properties, and methods in Excel. After you start writing code in VBA, you will find the online reference an invaluable tool. By choosing Contents from the Help menu in Excel and then selecting Programming With Visual Basic, you can access help topics on all Excel objects as well as on their associated properties and methods. You can also get a listing of all Excel objects by selecting Objects from the main Visual Basic Reference Contents screen. And to search for a particular object, you simply choose the Search button and then enter the name of the object.

Help screens for all Excel objects are structured in the same manner. The top of the screen displays the name of the object, and the body of the screen presents a description of the object. Under the name of the object, the headings Properties and Methods, shown in the following illustration, are displayed in green:

By clicking either heading, you can access lists of the properties or methods for the displayed object. In the Properties and Methods lists, you can click a particular property or method name to access a help screen. All property and method help screens are structured in the same manner, as described below and as shown on the sample screen at the top of the next page. The top of the screen displays the name of the property or method, and the body of the screen is broken up into the following sections:

At the top of the help screen, under the property or method name, you'll find two green labels: "See Also" and "Example." By clicking See Also, you can access other help topics related to the displayed property or method, and by clicking Example, you can view an example of syntactically correct code that shows the property being set or the method being called. Note that on the Example screen you also have the option of copying the code example to the clipboard so that you can paste it directly to your VBA module.

For a printed version of the VBA Help file, see the Microsoft Excel Visual Basic for Applications Reference, available from Microsoft Press.

The Application, Workbook, Worksheet, and Range Objects

The remainder of this book focuses on how to build custom applications using Excel objects. Although this book provides information about nearly every object in Excel, some objects are discussed at great length, and others because of space constraints are mentioned only briefly. Here we take a look at four Excel objects: Application, Workbook, Worksheet, and Range. These are four of the most commonly used objects in Excel, and gaining a basic understanding of them now will help you as you continue to read this book.

The diagram below shows the hierarchical structure of these four objects:

Figure 1-3 on the facing page displays these four objects as they appear in Excel.

The Application Object

Application is the topmost object in the Excel object hierarchy and represents Excel itself. Because Excel VBA applications run in Excel, you can think of the

Figure 1-3. The Application, Workbook, Worksheet, and Range objects as they appear in Excel.

Application object as representing the environment in which VBA applications run. Any property settings or method calls made on the Application object affect all of Excel and thereby also affect all VBA applications that run in the Excel environment.

The next two sections follow a format that is repeated for each object introduced here. A partial list of properties presents a short description of a property, the type of values the property can hold, information about whether the property is read/write or read only, and a segment of VBA code that shows how the value of the property can be set or retrieved. A partial list of methods gives a description of a method, the arguments that can be passed to the method, and an example of how the method can be called.

Only a few of the more important properties and methods are listed. For example, the Application object has over 100 properties and over 60 methods, but only a few of the more important ones are described here. For a complete list of properties and methods or for more information about a particular property or method, see Excel's online VBA Help.

Properties of the Application object

Caption: The Caption property is the caption that is displayed in the Excel title bar. String; read/write. Not available on the Macintosh.

Application.Caption = "My Custom Application"

DisplayAlerts: If DisplayAlerts is True, built-in Excel alerts are displayed. If it is False, alerts are not displayed. True or False; read/write.

Application.DisplayAlerts = False

Path: The Path property is the path to the directory in which Excel is installed. String; read only.

ExcelPath = Application.Path

ScreenUpdating: If ScreenUpdating is True, Excel updates the screen display as macros execute. If it is False, the screen display is not updated during execution. This property is True by default; it remains set only during execution of the macro in which the setting is made. True or False; read/write.

Application.ScreenUpdating = False

WindowState: The WindowState property is the state of the Application window. WindowState can take three different values:

Read/write.

Application.WindowState = xlNormal

Methods of the Application object

Calculate: The Calculate method forces all formulas in all worksheets in all open workbooks to be recalculated. Arguments: none.

Application.Calculate

Help: The Help method displays a help topic from a specified help file. Arguments:

Application.Help helpFile:="MAINXL.HLP", helpContextId:=100

Quit: The Quit method closes the Excel application. Note that if the DisplayAlerts property is set to False when the Quit method is called, Excel does not prompt the user to save any open workbook files. Arguments: none.

Application.Quit

Run: The Run method is used to execute an XLM macro. XLM is the Excel 4.0 macro language. Arguments: The first argument of Run is a string that represents the name of the macro to be run. Run can take additional arguments; the number and type depends on the number and type of arguments the macro expects.

Application.Run macro:="OldMacro", arg1:=100, arg2:="Revenue"

The Workbook Object

The Workbook object falls directly below the Application object in the Excel object hierarchy and represents an Excel workbook file. In terms of application development, think of the Workbook object as the delivery mechanism, or the container, of any VBA application that you create in Excel. A VBA application can span more than one workbook. Generally speaking, however, a single application can be characterized as existing in a single workbook. Any property setting or method call that you perform on the Workbook object affects your entire application.

A workbook can come in two forms: an .XLS file (or what is characterized as a standard Excel workbook) or an .XLA file (known as an Excel "add-in file"; .XLA files are fully compiled and hidden VBA applications). You can compile VBA code without creating an .XLA file; however, if you create an .XLA file, you will be certain that your code is, indeed, fully compiled. Also, .XLAs allow you to hide your code from users. Topics related to .XLA files are discussed in greater depth in Chapter 12. The following two sections describe some of the Workbook object's more commonly used properties and methods. For more information about these properties and methods, see Excel's online VBA Help.

Properties of the Workbook object

HasRoutingSlip: If HasRoutingSlip is True, a routing slip is added to the workbook. If it is False, any existing routing slips are removed from the workbook. A routing slip is used to route a workbook to several recipients through a MAPI compatible e-mail network. True or False; read/write.

Workbooks("BOOK1.XLS").HasRoutingSlip = True

Name: The Name property is the name of the workbook. Note that Name is a read-only property; to change the name of a workbook file, you must save the file with a new name using the SaveAs method. String; read only.

WorkbookName = ThisWorkbook.Name

Path: The Path property is the path to the workbook file. String; read only.

WorkbookPath = ActiveWorkbook.Path

Saved: If Saved is True, no changes have been made to the workbook since it was last saved. If it is False, changes have been made to the workbook since it was last saved. True or False; read only.

If Not(ActiveWorkbook.Saved) Then
  ActiveWorkbook.Save
End If

Methods of the Workbook object

Activate: The Activate method activates the first window associated with the workbook and makes the specified workbook active. Arguments: none.

Workbooks("MYAPP.XLS").Activate

Close: The Close method closes the workbook. Arguments:

ActiveWorkbook.Close saveChanges:=False

Protect: The Protect method protects the workbook so that no changes can be made to it. Arguments:

Workbooks(1).Protect "password", True, True

Save: The Save method saves the workbook. Arguments:

ActiveWorkbook.Save

The Worksheet Object

The Worksheet object is contained in the Workbook object and serves several purposes in a VBA application in Excel. Its most important purpose perhaps is as the basis for designing forms; the majority of custom forms in Excel are designed using the Worksheet object. Worksheets also provide a powerful grid that can be used to display and manipulate data. In addition, worksheets contain over 400 built-in Excel functions that can perform advanced numeric calculations at lightning speed. And worksheets can serve as miniature databases. Their tablelike structure makes them ideal for storing, summarizing, and even performing lookup functions on small data sets.

Here we discuss a few of the Worksheet object's more commonly used properties and methods. For more information about these properties and methods, see Excel's online VBA Help. Worksheets themselves are discussed in greater detail in Chapter 5, which provides information about designing forms. In addition, Chapter 8 covers the different ways in which you can handle data on a worksheet. Recall that a worksheet can be referenced either by an index in the Worksheets collection or by using the Application object's ActiveSheet property.

Properties of the Worksheet object

Index: This is the numeric index of the specified worksheet object in the Worksheets collection. Integer; read only.

ActiveSheet.Name = "WkSheet" & ActiveSheet.Index

Name: This is the name of the worksheet. String; read/write.

Worksheets(1).Name = "My Worksheet"

UsedRange: The UsedRange property returns a Range object that references the range on the worksheet that contains data. Range object; read only.

Dim Range1 As Range
Set Range1 = Worksheets(1).UsedRange

Visible: If Visible is True, the worksheet is displayed. If Visible is False, the worksheet is hidden from view, but the user can restore the worksheet to view by choosing the Sheet command from the Format menu. If Visible is xlVeryHidden, the worksheet is hidden from view and can be restored to view only through a macro. True, False, or xlVeryHidden; read/write.

Worksheets("Main").Visible = xlVeryHidden

Methods of the Worksheet object

Activate: The Activate method activates the specified worksheet. Arguments: none.

Worksheets("My Worksheet").Activate

Calculate: The Calculate method forces all formulas on the worksheet to be recalculated. Arguments: none.

Worksheets(1).Calculate

Delete: The Delete method deletes the worksheet from the workbook. Arguments: none.

Worksheets("My Worksheet").Delete

Protect: The Protect method protects the worksheet so that no changes can be made to it. Arguments:

Worksheets("My Sheet").Protect "password"

The Range Object

The Range object is contained in the Worksheet object and is used to represent one or more cells on a worksheet. The Range object's primary purpose is to hold and display individual pieces of data: integers, strings, or formulas. The worksheet cells that a Range object represents actually possess a degree of intelligence. From a cell, you can access over 400 built-in Excel functions; you can call VBA functions; and you can even establish links to other cells that exist on the same worksheet, on other worksheets, or in other workbooks. Understanding the flexibility and power of the Range object allows you to tap into the power of Excel's built-in calculation engine and to create more powerful data analysis applications. We touch here on a few of the most commonly used properties and methods associated with the Range object, but we will deal with Range in greater detail in the section titled "Using the Range Object to Create Forms," beginning on page 246 in Chapter 5. For more information about these properties and methods, see Excel's online VBA Help.

Properties of the Range object

Count: The Count property is the number of cells in a range. Integer; read only.

NumOfCells = Worksheets(1).UsedRange.Count

Dependents: The Dependents property returns a range that contains all the dependents of the referenced range. Dependents are cells that reference the range in a formula. Range object; read only.

Dim Range1 As Range
Set Range1 = Worksheets(1).Range("A1").Dependents
MsgBox Range1.Address

Name: The Name property is the name of a range. String; read/write.

Worksheets(1).Range("A1").Name = "FirstCell"

Value: The Value property is the value contained in a range. If the range contains multiple cells, the Value property is an array that contains values for all the cells. Boolean, Integer, Long, Single, Double, Currency, Date, or String; read/write.

Worksheets(1).Range("FirstCell").Value = 1

Methods of the Range object

Calculate: The Calculate method forces all formulas in the range to be recalculated. Arguments: none.

Worksheets(1).Range("A1:F20").Calculate

ClearContents: The ClearContents method clears all values from the range. Arguments: none.

Worksheets(1).Range("A1:F20").ClearContents

Copy: The Copy method copies the values in the range either to another range or to the clipboard. Argument:

Worksheets(1).Range("A1").Copy

S U M M A R Y

This chapter has covered the concepts of objects, properties, and methods. You'll want to remember these important points:

• Excel 5 is made up of 128 objects. Each object represents a feature or a piece of functionality in Excel.
• Every Excel object has a unique set of properties and methods. You program Excel by using Visual Basic for Applications (VBA) to manipulate the properties and methods of Excel objects.
• A property represents a value or a setting that describes an object. VBA can be used to either set or get a property setting.
• A method represents an action that can be taken by or on an object. A method usually has a set of arguments associated with it that specify information about how the method is to be carried out. When using VBA to call a method, passing arguments is in most cases optional; you can pass all arguments, only some, or none. If arguments are used, however, they can be passed by order or by name.
• Objects come in two different forms: singular objects and objects in collections. A singular object is a unique object only one instance of which occurs in a given context. An object in a collection is an object multiple instances of which occur in the same context.
• You reference singular objects directly. You reference objects in collections by indexing the appropriate collection either by name or by number.
• The Range object is by definition a singular object, but it is referenced in a manner similar to that of a collection: by indexing Range by name or by address.
• Excel objects are arranged in a hierarchical structure; some objects are contained in others.
• In setting a property of or calling a method on an Excel object, it is sometimes necessary to specify the entire hierarchical path for the object. The starting position on the hierarchical object path, however, depends on the context in which the property setting or the method call is made, and the path can sometimes be shortened.
• Full online VBA Help is available in Excel for all objects, properties, and methods. You access VBA Help by choosing Contents from the Help menu in Excel and then selecting Programming With Visual Basic. Excel's online VBA Help is an excellent source of information about VBA, Excel objects, properties, and methods—as is the Microsoft Excel Visual Basic for Applications Reference (Microsoft Press).
• The Application object exists at the top of the Excel object hierarchy and represents the environment in which VBA applications are run.
• The Workbook object is contained in the Application object. The Workbook object represents an Excel file and serves as the container or delivery mechanism for VBA applications.
• The Worksheet object is contained in the Workbook object and serves as the basis for designing custom forms in Excel.
• The Range object is contained in the Worksheet object and serves a variety of purposes that involve data handling and data display.