Getting Started

  1. Open Excel with a clean, blank workbook. If you have other workbooks open, such as the Personal Macro Workbook, close them. (To find any hidden workbooks, click Unhide on the Window menu.)

  2. Restore the Excel workbook window (not the Excel application window) so that it is not maximized.

  3. If the Visual Basic toolbar is not visible, use the right mouse button to click any toolbar and click Visual Basic on the shortcut menu.

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 tidy 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 objects in 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 collection of 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 four feet seven 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 collection has its own 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 different list of properties than Jared's property list, Students is a different object class from Student. But because Mr. Osgood's Students collection and Mrs. Middlefield's Students collection 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.

Excel objects have properties also. 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 some of the properties-such as the name of a worksheet or the height of a cell-is easy. Changing other properties-such as 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.

A collection object has a list of methods separate from the list of methods that belongs 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 moves into 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.

Sometimes the distinction between a method and a property is vague. 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? Here are some concepts that might help:

  • Methods can change propertiesSome methods 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). Likewise, in Excel, the ChartWizard method can change several properties of the chart.

  • Properties can involve actionsSetting 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. 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). But closing a file is a method because there's no trace of the file left in memory after you're done.


    Most of the time, you don't need to worry about the difference between properties and methods. Excel has online tools to help you find the methods and properties for objects, and you might not need to know which is which. For example, you can turn on the macro recorder, carry out a task, and then modify the code that the macro recorder produced- without ever really knowing whether Excel used a property or a method for any given action.

In summary, an individual item is an object from one object class, while a collection of those items is an object from a different object class. A single item from one collection can contain an entire collection of other objects. For example, a single school from the district's collection of schools can contain an entire collection of classrooms. Each object belongs to an object class that has a unique list of properties and methods. Many different individual objects (Student objects) can belong to a single object class (the Student object class), in which case they all share the same list of properties and methods while each retains its individuality. In this chapter, you'll learn how to work with many kinds of Excel objects.

Microsoft Excel 2002 Visual Basic for Applications Step by Step
FrameMaker 6: Beyond the Basics
ISBN: 735613591
EAN: 2147483647
Year: 2005
Pages: 122
Authors: Lisa Jahred

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net