The Object Hierarchy
As you've seen, your computer's objects are arranged in a hierarchy with the most general object (the computer as a whole) at the top. Lower levels progress through more specific objects (such as the system unit, the motherboard, and the processor).
Each Office application's objects are arranged in a hierarchy also. The most general object-the Application object-refers to the program itself. In Word, for example, the Application object contains more than 30 objects, including the Documents object (the collection of all open documents, each one being a Document object), the Options object (the settings available in the Options dialog box), and the RecentFiles object (the names of the files that have been used most recently).
Many of these objects have objects beneath them in the hierarchy. A Document object, for example, contains objects that represent the document's characters, words, sentences, paragraphs, bookmarks, and much more. Similarly, a Paragraph object contains objects for the paragraph format and the tab stops.
To specify an object in the hierarchy, you usually start with the uppermost object and add the lower objects, separated by periods. For example, here's one way you could refer to the first word in the second paragraph in a document named Memo.doc:
Application.Documents("Memo.doc").Paragraphs(2).Range.Words(1)
As you'll see, there are ways to shorten such long-winded "hierarchical paths."
Working with Object Properties
Every object has a defining set of characteristics. These characteristics are called the object's properties, and they control the appearance and position of the object. For example, each Window object has a WindowState property you can use to display a window as maximized, minimized, or normal. Similarly, a Word Document object has a Name property to hold the filename, a Saved property that tells you whether or not the document has changed since the last save, a Type property to hold the document type (regular or template), and many more.
When you refer to a property, you use the following syntax:
Object.Property
For example, the following expression refers to the ActiveWindow property of the Application object:
Application.ActiveWindow
You will come across the word "active" quite often in your VBA travels, so let's make sure you know what it means. In the VBA world, active describes the item with which you're currently working. In Word, for example, the document you're currently using is the active document. Similarly, in Excel the worksheet cell that you're editing or formatting is the active cell. In programming lingo, the active item is said to have the focus.
One of the most confusing aspects of objects and properties is that some properties do double-duty as objects. Figure below show uses an Excel example to illustrate this. The Application object has an ActiveWindow property that tells you the name of the active window. However, ActiveWindow is also a Window object. Similarly, the Window object has an ActiveCell property that specifies the active cell, but ActiveCell is also a Range object. Finally, a Range object has a Font property, but a font is also an object with its own properties (Italic, Name, Size, and so on).
In other words, lower-level objects in the object hierarchy are really just properties of their parent objects. This idea will often help you to reduce the length of a hierarchical path (and thus reduce the abuse your typing fingers must bear). For example, consider the following object path:
Application.ActiveWindow.ActiveCell.Font.Italic
Here, an object such as ActiveCell implicitly refers to the ActiveWindow and Application objects, so you can knock the path down to size, as follows:
ActiveCell.Font.Italic
Setting the Value of a Property
To set a property to a certain value, you use the following syntax:
Object.Property=value
Here, value is an expression that returns the value to which you want to set the property. As such, it can be any of VBA's recognized data types, including the following:
- A numeric value-For example, the following statement sets the size of the font in the active cell to 14:
ActiveCell.Font.Size = 14 - A string value-The following example sets the font name in the active cell to Times New Roman:
ActiveCell.Font.Name = "Times New Roman" - A logical value (in other words, True or False)-The following statement turns on the Italic property in the active cell:
ActiveCell.Font.Italic = True
Returning the Value of a Property
Sometimes you need to know a property's current setting before changing the property or performing some other action. You can find out a property's current value by using the following syntax:
variable = Object.Property
Here, variable is a variable or another property. For example, the following statement stores the contents of the active cell in a variable named cellContents:
cellContents = ActiveCell.Value