What Is an Object?

The easiest way to understand objects in Excel is to compare them to objects in the real world. In the real world, cities, schools, classrooms, and students are all objects. A city is dotted with schools, a school is lined with classrooms, a classroom is packed with students-and all the students are arranged in rows of tidy desks, smiling happily and listening attentively to the kind, wise, and firm but patient teacher. Well, maybe not all the desks are tidy.

Objects Come in Collections

Look around you. The world consists of objects in collections, which are in turn objectsin other collections: rooms in apartments in buildings in complexes, flowers in beds in yards in neighborhoods, rocks on crags on mountains in ranges, children in households in extended families in clans. Each object-each city, each student, each flower, each mountain, and each family-is an individual item, yet each also belongs to a collectionof similar objects, and each collection of objects is itself an individual item within a larger collection.

If you’re a city official thinking about the collection of School objects, you might refer to the collection of schools as a group: “All the schools have asbestos problems.” Or you might refer to an individual school: “We need to replace the light fixtures at Jefferson Elementary School.” When you do refer to an individual school, you might refer to the school by name: “Jefferson Elementary School, as you may know, was named for the esteemed author of the Declaration of Independence.” Or you might refer to it by its position in the collection: “The first school built in our city, back in 1887, is the one I attended as a child.” Or (if you are conducting a driving tour) you might refer to the individual school by pointing: “Notice the classic architecture of this magnificent school building.”

An Excel workbook is like a school. Just as you can have more than one school in a city, you can have more than one workbook open in Excel. Each workbook is individual and unique, yet each is a Workbook object. You can refer to the entire collection of open workbooks as a group (“Close all the open workbooks”), or you can refer to individual workbooks. If you refer to an individual workbook, you can specify the workbook by name (“Open the Chapter 3 workbook”), by position (“What is the first workbook in the list of recently opened files?”), or by pointing (“Save the active workbook”).

A worksheet in a workbook is like a classroom in a school, and worksheet cells are like students in a classroom, arranged in neat little rows and columns. Excel also has other collections of objects: menu items in menus in menu bars, columns in a series in a group in a chart, items in fields in rows in a PivotTable. You can refer to each collection, whether in Excel or in the natural world, as a whole or you can refer to a single item within the collection. When you refer to a single item within the collection, you can refer to it by name, by position, or by pointing.

Objects Have Properties

Do you see that little boy in Mrs. Middlefield’s class-the one in the third row, in the fourth seat over? He’s about 4 feet 7 inches tall. His hair is short. The color of his shirt is blue. His name is Jared. And his eyes are closed.

The boy’s height, hair length, shirt color, name, and eye state are properties of that one particular Student object. The little girl sitting behind him also has Height, HairLength, ShirtColor, Name, and EyeState properties, but the values of her properties are different. The boy is a different object than the girl, but each is a Student object.

The boy’s desk is also an object, a Desk object. A Desk object has a Height property, as does a Student object, but a Desk object does not have a HairLength property. Likewise, a Student object does not have a ManufacturerName property, as a Desk object does. Because the boy and the desk have different lists of properties, they are different types, or classes, of objects. Because the boy and girl share the same list of properties-even though they have different values for the properties-they both belong to the same class of object. They both belong to the Student object class. Sharing the same list of properties is what makes two objects belong to the same object class.

Just as Jared is an object-a Student object-Mrs. Middlefield’s entire collection of students is also an object-a Students object. The Students object is a collection and has its own set of properties; the properties of the collection are not the same as the properties of the individual objects contained within it. For example, you don’t really care about a HairLength property of the entire collection of students. (Would that be total hair length or average hair length?) But a collection object does have properties of its own. For example, the number of students in the collection (the Count) is a property of the Students object. Because Mrs. Middlefield’s collection of students has a property list that is different from the property list of the one student Jared. Student and Students are two different object classes. But because Mr. Osgood’s collection of students and Mrs. Middlefield’s collection of students do both have the same list of properties-even though they might have different values for the properties-both collections belong to the same Students object class. The Students object class is different than the Student object class because the two object classes have different lists of properties.

Some properties are easy to change. You could perhaps change Jared’s EyeState property with a good, sharp rap with a ruler on his desk. (And of course, he can change the property right back after you look the other way.) You might even change Jared’s name to Gerard temporarily for French language instruction. But changing Jared’s height, weight, eye color, or gender probably falls outside the scope of a normal school activity.

Each Excel object class has a list of properties as well, and some of the properties are easy to change and some are not. A workbook has an author. A worksheet has a name. A cell has a width, a height, and a value. A menu has a caption. A collection of worksheets has a count of the worksheets in the collection. Changing the name of a worksheet or the height of a cell is easy. But changing the count of cells on a worksheet probably falls outside the scope of a normal macro activity.

Objects Have Methods

Look, Mrs. Middlefield is telling the class to stand up. She’s leading them in a stirring rendition of “Row, Row, Row Your Boat.” Student objects can sing songs. Singing a song is an activity. Student objects also do other activities. Student objects eat. Student objects draw pictures. One student might sing, or eat, or draw well; another student might sing, or eat, or draw badly; but they both share the ability to do the action. Desk objects, on the other hand, do not sing, eat, or draw. Desk objects might squeak, whereas Student objects generally don’t. In the same way that different classes of objects have different lists of properties, they also have different lists of activities they can do. The activities an object can do are called methods. Objects that belong to the same class can all do the same methods.

As with properties, the list of methods that belong to a particular collection object is usually different from the list of methods that belong to the individual items in it. One of the most important methods for most collections is adding a new item to the collection. When a new student joins the class, you are executing the Add method on the Students object, not on an individual Student object. When the construction bond passes and the school gets a new wing, you are executing the Add method on the Classrooms object. You don’t add the new classroom to an individual classroom; you add it to the collection of classrooms.

Most Excel collection objects have an Add method for adding a new item to the collection, and they all have an Item property for establishing a link, or a reference, to an individual item in the collection. Excel worksheet objects also have a Calculate method for causing all the cells to recalculate, and Excel charts have a ChartWizard method that quickly changes various attributes of a chart.

VBA has a name for the combined list of methods and properties of an object: members. So instead of saying, “What are the methods and properties of a Worksheet object?” you can say “What are the members of a Worksheet object?” The word members makes it sound as if an object class is a club, and you have to be a suitable method or property to be able to belong to the club-and in a way, that’s exactly how an object class works. The terminology might be a little confusing, because you might think that Jaredis a member of the Student class. But he’s not. The members of the student class are its properties (HairLength and EyeColor) and its methods (FingerPaint and Eat). Jared isan instance of the Student class, which means he is a specific example of a student. You could say, “There are too many rules around here; for instance, they won’t let me sleep during class.” And that means you’re giving a specific example of a rule. In the sameway, Jared is a specific example of a student. He is an instance of a Student. Michael and Rupert and Sachiko and LaDean are all instances of a Student class, so each one has the same list of methods and properties. If you want to really impress people, just casually say, “Yes, but actually each instance has the same list of members as every other instance in the class, don’t you think?”

Sometimes the distinction between a method and a property is nothing more than which one the object designer chose to use. When Jared opens his eyes, is he carrying out the OpenEyes method (an action), or is he assigning a new value to his EyeState property? So just thinking of the whole list as the list of members is sometimes convenient. But sometimes it’s useful to remember why there are both members and properties. Here are some concepts that might help.

Methods Can Change Properties

Most methods can, and do, change properties. When Jared carries out the Fingerpaint method, the action happens to change his ShirtColor property. When he goes home and carries out the WashClothes method, the ShirtColor property changes back (with perhaps a few residual stains). But the method doesn’t remember what it did. After you use the WashClothes method, you still have to use the ShirtColor property to find out what color it is now. Likewise, in Excel, the Add method of the Workbooks object changes the value of the Count property, but the Add method doesn’t tell you what the new count is.

Properties Can Involve Actions

Setting a property usually involves some kind of action. When you change the classroom’s WallColor property, you do get out the paint rollers and the ladders and start working, but you are more concerned about the finished attribute of the wall than about the action that changed the attribute. Consequently, when you have finished, you can tell your friends that the value of the WallColor property is now BrightYellow. You use the same WallColor property to explain the resulting state that you used to carry out the action. In Excel, hiding a worksheet is an example of setting a property (because the worksheet is still there, and you might want to change the property back, and while it is hidden you can tell people what the current status is). But closing a file is a method because there’s no trace of the file left in memory after you’re done.

Tip 

If you are using only recorded macros, you don’t need to worry about the difference between properties and methods, because the macro recorder uses the appropriate method or property. But it is useful to know the distinction because of what you do to change the recorded macro: If it’s a method, you modify the macro by changing its arguments. If it’s a property, you modify the macro by changing what you assign to the property. The same distinction applies, of course, if you are writing the macro from scratch. Excel’s online tools and help topics indicate whether something is a method or a property.

In summary, an individual item (a desk or a worksheet) is an instance from one object class, while a collection of those items (a bunch of desks or a bunch of worksheets) is an instance from a different object class. A single instance from one collection can contain an entire collection of other objects. For example, a single instance from the district’s collection of schools can contain an entire collection of classrooms, and a single instance from Excel’s collection of workbooks can contain an entire collection of worksheets. Each instance belongs to an object class that has a unique list of members (methods and properties). A single object class (the Student object class or the Worksheet object class) can have many different individual instances (Jared or Michiko or Budget2007). In that case, each instance shares the same list of members, while retaining its individual values for the specific properties. In this chapter, you’ll learn how to work with many kinds of Excel objects.