Chapter 2 Excel Object Model Objects Properties and

Chapter 2 Excel Object Model

Objects, Properties, and Methods • • Attributes of an object are called properties (ex. Height, color, width, for example). Each property as a value. Properties have methods which are actions. Methods also have qualifiers called arguments which indicate how a method is carried out. Parts of speech analogy: Objects correspond to nouns, properties correspond to adjectives, methods correspond to verbs, and arguments correspond adverbs. Common objects in Excel: ranges, worksheets, charts, and workbooks. Range is considered an object since it has properties. – – – • Value property of a cell can either text or numeric. Horizontal. Alignment property can be left, center, or right aligned. Formula property which stores a formula in a cell. Method of Range object include Copy which has a Destination argument. Clear. Contents is another Range method which performs a highlight and delete operation. It deletes the contents of the range but does not change formatting. Clear method will also clear formatting of the cell range along with its’ contents. Object Browser can be used as a dictionary for properties and methods of objects.

Collections as Objects • • Collections contain multiple objects. Collections have properties and methods. The properties and methods differ from the objects stored in a collection. Collections have fewer properties and methods than objects. The Count property and Add method are the most often use. Count property indicates how many objects are in the collection. The Add method allows new objects to be added to the collection. In Excel, collection objects are plural while an object is singular. – Example: The Worksheets collection is the collection of all worksheets in the workbook. Any one of these worksheets is a Worksheet object. – Note: Worksheets can be counted or another worksheet can be added to the collection. – Properties of Worksheet object: Name, Visible, with the Delete being a commonly used method. • • • Range does not have a collection object. The Range object is a little singular and a little plural since it can refer to a single cell, a rectangular range, a union of several rectangular ranges, an entire column, or an entire row. It should be noted Ranges are the most often used objects and most difficult to master in all the varied forms.

Hierarchy of Objects • • • With in the collection there is a natural hierarchy. Objects within the collection have their own properties and methods. The full diagram of the Excel object model appears in Figure 2. 2 on page 10. All objects and collections are displayed in a hierarchy. The Application object is at the top followed by the Workbooks collection. The Workbooks is a collection of all open Workbook objects. From any Workbooks collection you can step down to the Worksheets collection which is a collection of all worksheets. The hierarchy can be broken down when viewed on-line by clicking the arrow next to the desired collection. The hierarchy are very important in the context of VBA programming. You can view the different collection hierarchies by going the VBA editor (Alt+F 11), Microsoft Visual Basic Help, search for Excel Collection of Objects.