We're all surrounded by objects that we perceive by vision or touch. We distinguish things by their properties, how they are related to other objects, and how they are affected by an action. Most objects have some sort of functional or aesthetic purpose, and many are collections of objects grouped together. An obvious example would be your computer. It consists of a monitor, a keyboard, speakers, a processor, disk drives, a mouse, and perhaps other components. Each component is further composed of objects—until you finally reach a fundamental element.
Software provides a similar ordering of objects. You can't, of course, put them on your mantel, but you can distinguish them by their properties and the relationships they have with one another. In Office, almost all the functionality you work with and all the viewable content you create is represented by an equivalent object in Visual Basic for Applications. Because these objects are programmable, you can develop a Visual Basic program that manipulates the properties that an object exposes. The collections of Office objects are categorized by either Word, Excel, PowerPoint, Access, Outlook, or Office, and they allow you to navigate down to the smallest detail of information in any of your documents.
Most objects are described in relation to another object. For example, a key on your keyboard doesn't stand on its own; it's a functioning part of the whole keyboard, which in turn is part of your computer. It's the relationship of objects that forms the basis of an object model in Office; the model is the hierarchy of objects in relation to each other.
All Office applications have the same general hierarchy model of objects, with the Application object residing at the top. Each object represents an element of an application, such as a shape on a slide, a cell in a worksheet, a word in a document, or a table in a database. Navigating up and down the object model hierarchy is similar to using a road map, which displays the routes you might take to reach certain destinations.
The Application object represents the starting point. From the Application object, you travel down a branched highway, selecting objects to pass through until you reach the object you want. If you want to change the color of a shape in a PowerPoint slide, you start with the Application object (PowerPoint), indicate which presentation the slide belongs to, and then find the slide that contains the shape. Finally, you reach your destination by selecting the shape on the slide.
To manipulate an object's property, such as the text of the first shape on the first slide in a PowerPoint presentation, you have to traverse down the branches of the PowerPoint object model hierarchy tree. In Visual Basic programming, each branch in the object model diagram is represented by the dot operator (.), which is accessed by the period key on your keyboard.
TIP
The verbal translation of Visual Basic code uses "dot" instead of "period." It's used in the same way as when you hear "dot" used to distinguish the extension of a filename in the Windows operating system. For example, for a file named MyFile.xls, you'll usually hear "MyFile dot X-L-S."
Sub ChangingText()
and before End Sub:
Application.Presentations(1).Slides(1).Shapes(1) _ .TextFrame.TextRange = "Some text." |
In the above line of code, you don't need to type "Application" and the subsequent period. When you're writing Visual Basic code in the PowerPoint Visual Basic Editor window, the Application object, which in this case represents the PowerPoint application, is implied. Likewise, if you're writing Visual Basic code in Excel, when you access a Workbook object you don't need to specify the Excel application object (though it never hurts to be explicit about which application you're using). However, if you want to access a PowerPoint object while writing code in Excel, you need to make an explicit reference to the PowerPoint application object. In Chapter 7, "Communicating Across Microsoft Office," you'll learn how to drive other applications from one specific application.
With this procedure you added text to the first shape of the first slide in the first presentation in PowerPoint.
IMPORTANT
The text in the line of Visual Basic code in step 6 of this example parallels the objects being manipulated. However, as you'll see in the "Object Browser" section of this chapter, in a number of cases what you type in code doesn't map directly to the object name in the hierarchy tree.
In Office, you'll find that although a number of objects are of the same type, each is distinguished by a unique name or index value. Together, these objects form a collection. Think back to the computer metaphor. Your keyboard has a set of keys that represent a collection of keys. Each key is uniquely distinguished by an index value in the collection of keys, but many keys are used for the same purpose (entering text) and thus are of the same object type. The names of collections are always plural, such as Sections in Word, Worksheets in Excel, and Shapes in PowerPoint.
In PowerPoint, the Presentations collection object represents a collection of all the presentations currently open in PowerPoint. The objects within the Presentations collection are Presentation objects, and each Presentation object in the collection contains a collection of Slide objects. To access a single object item in a Visual Basic collection, you refer to it in one of two ways. The first is to type the collection name followed by a period (.), and then type Item(<index>), where <index> represents either the name of the specific item in the collection or its index value (position) in the collection. Pressing F5 runs the procedure you're currently working on. Using this method, the code sample used in step 6 in the preceding example would look like this:
Application.Presentations.Item(1).Slides.Item(1) _
.Shapes.Item(1).TextFrame.TextRange = "Some text."
|
Or, as shown in step 6, you can remove the keyword Item and the preceding period and just type in the collection name followed immediately by the index value. Either syntax works, but the abbreviated version requires less typing and usually produces more readable code.
In Excel, the Workbooks collection is the equivalent of the Presentations collection in PowerPoint. The Workbooks collection object represents all workbooks currently open in Excel. Each Workbook object has a collection of Worksheet objects. In Word, the equivalent to the Presentations or Workbooks collection is the Documents collection, which represents all documents currently open in Word. Within each Document object in the collection, a Sections collection represents all sections in the document.
Every collection in Office allows you to access each item in the collection as well as the number of objects in the collection.
Sub
NumberOfShapes() and before End Sub:
MsgBox ActiveSheet.Shapes.Count |
ActiveSheet refers to the worksheet currently displayed in the Excel application window. Each worksheet has a collection of Shape objects. A shape in Excel is any object in the worksheet that floats above the cells. The Count property in the line of code you just inserted returns the number of shapes in the current active worksheet.
Notice that each time you add shapes to the active worksheet, the value displayed in the message box is incremented by the number of shapes you added. Try deleting shapes from the active worksheet as well. When you run the macro, you'll see the number of shapes in the Shapes collection decrease by an equivalent amount.
Each object that's part of a computer contains components with specific characteristics. For example, the keyboard is made up of a collection of keys, and each has properties that distinguish it from the others. Each key has a different label, such as "A," "ESC," or "F1," and is uniquely positioned on the keyboard. The SPACEBAR is usually much bigger than the other keys, and it doesn't have a label. The label and position are properties of a key. In addition, the keys provide a method of entering data into the computer by allowing the user to perform the action of pressing them. Thus, "press" is a method of a key.
When you press a key such as "A," you trigger an event. The Windows operating system, working with another program, handles the event in a number of ways, depending on the conditions. If the computer is turned off, nothing happens. If the computer is on and the current, active application is a word processor such as Word, the letter "a" is displayed on the screen at the position of the cursor.
Each Office object contains one of three types of members: a property, a method, or an event.
| Object Member Type | Description |
|---|---|
| Property | A characteristic attribute, such as size, position, or shape, that defines or describes an object. (Adjective) |
| Method | An action, such as save, close, or delete, that you can perform on or with an object. (Verb) |
| Event | Something that takes place, such as a click, a press, or a change, that causes an object to react. (Noun) |
In most cases, you can retrieve the value of a property or you can set a property to a specific value. However, you'll find a number of instances where a property is read-only. This means you can retrieve the property value but you can't set it. When you can set a value to a property, the range of values you can assign often has restrictions. For example, the size of a font used in text in a Word document must be a number between 1 and 1638.
In addition, you can only set a property to a specific type of value (although there are some cases where you can set a property to multiple types of values). For example, to set the characteristics of a font used in text, you can use the Name, Size, and Bold properties. You can only assign an integer value to the Size property (which sets the size of the font used in text). On the other hand, you can only assign a string value to the Name property (which indicates the name of a font, such as Times New Roman). Finally, you can only assign to the Bold property one of the two Boolean values, True or False.
To set a property, you equate a value to the property with an equal sign. To retrieve a property value, you just specify the property and, in most cases, assign it to a temporary variable.
MsgBox Application.Left Application.Left = 50 MsgBox Application.Left |
The procedure moves the PowerPoint application window to approximately position 50 (near the left side of your screen). The first line of code sets the Left property value of the application window and displays the property value in a message box. The second line sets the property value to 50, and the third displays the resulting value.
Throughout an Office object model, whether that's Word, Excel, PowerPoint, or Access, you often set object properties to an integer value within a range specified by the specific application. But in order to set a property value to a valid integer you need to know the valid range of integers and understand what each value represents. The enumeration value is a label that represents an integer, but you don't necessarily have to know what the integer is. In order to provide a convenient way to select a value from a known number of choices—and to make your code more readable and understandable—each object model provides a list of enumeration values. An enumeration represents a finite list of unique integers, each of which has a specific name and special meaning in the context in which it's used.
NOTE
Each application in Office provides a set of enumeration values. Each enumeration value in a specific application has a name with a two-letter prefix. In Word, Excel, PowerPoint, Access, and Outlook, enumeration names are prefixed with wd, xl, pp, ac, and ol, respectively.
When you create a new slide in PowerPoint, you can select a slide layout from the list in the New Slide dialog box. Each layout is different and displays a title, text, chart, clip art, and a few other placeholders in a unique combination and position. Layout is a property of a Slide object, and through Visual Basic you can determine the current slide layout or set the layout you want.
ActivePresentation.Slides(1).Layout= |
When you type the equal sign, the program displays a drop-down list showing all the possible values to which the Layout property can be equated. As you'll see later in the "Object Browser" section of this chapter, these are enumeration values and are really integers.
TIP
If you don't see the drop-down list of enumeration values, the Auto List Members feature isn't currently selected. On the Tools menu in the Visual Basic Editor window, click Options. In the Editor tab, select the Auto List Members check box in the Code Settings group and click OK.
Notice how the name of the enumeration value describes the actual property setting. For example, ppLayoutBlank represents a slide with a blank layout.
TIP
Each Office application provides a set of enumerations that represent predefined integer values. For example, ppLayoutBlank represents the number 12. You should use the enumeration instead of the integer value because it makes your code more understandable when other developers read it or when you come back to read it later.
When you work with an object's properties, you use an equal sign; when you work with an object's methods, you don't. With some methods, you just type the method name, but in a number of cases you can send information, or pass arguments, to a method.
ActivePresentation.SaveAs |
NOTE
If the Auto Quick Info window isn't displayed, click Options on the Tools menu in the Visual Basic Editor. In the Editor tab of the Options dialog box, select Auto Quick Info and click OK.
For now, you just need to pass the first argument to the SaveAs method. This argument is the filename under which you want to save the presentation. The other arguments in the list are optional.
As you'll see in the section of this chapter entitled "Learning the Members of the Object Model," Auto Quick Info and Auto List Members are Visual Basic Editor features that make it unnecessary for you to memorize the syntax of every member in an object model.
In the context of Visual Basic programming, when an event such as saving a document takes place, you can set up your code (procedures) to respond to it. These procedures have specific names and are referred to as event procedures. An event procedure contains code that you write to perform some action to handle the event when it's triggered. Event procedures have a syntax like that shown in the following table.
NOTE
Chapter 5 describes how to handle events that occur when creating, opening, saving, printing, or closing any Word, Excel, or PowerPoint document. Chapter 10 shows you how to handle such events as the user changing the selection of content or double-clicking or right-clicking on content in the same three applications.
| Application | Event Procedure Example |
|---|---|
| Excel | Sub Workbook_Open() |
| Sub Workbook_NewSheet (ByVal Sh As Object) | |
| Word | Sub Document_Open |
| Sub Document_Close |
In some cases the application passes an argument to the event procedure. In one of the preceding cases, when you open a new worksheet in Excel, Excel passes the newly created sheet to the NewSheet event procedure so that you can start working with the new worksheet right away.
A Word Document object exposes properties, methods, and events. The Document object represents an open document in Word and allows you to access every word, table, shape, or other element in the document.
The ThisDocument project item is often referred to as the code module "behind" the Document object. When you insert ActiveX controls into a document, the controls are listed in the code module belonging to the document in which they reside. In Excel, controls reside on a worksheet and are listed in the code module for the Worksheet object. In PowerPoint, controls on a slide are listed in the code module for the Slide object.
NOTE
By default, PowerPoint doesn't display the code module for each Slide object in the Project Explorer. When you insert the first ActiveX control into a slide, the Project Explorer lists the code module for the Slide object. Excel lists code modules for each Worksheet object by default.
MsgBox ActiveDocument.Name |
This line displays the name of the active document. Name is a read-only property of the Document object, and it returns a string value representing the document's filename. If you haven't saved the document yet, the Name property represents the default name that was given to the document when you created it. (The default name for the document is displayed in the Word application window's title bar.) The Name property behaves the same way for both the Workbook object in Excel and for the Presentation object in PowerPoint.
ActiveDocument.SaveAs "C:\Temp\MyDoc.doc" MsgBox ActiveDocument.Name |
The Document object supports a SaveAs method that represents the act of saving the document with a specified filename. The complete procedure looks like this:
Sub WordDoc()
MsgBox ActiveDocument.Name
ActiveDocument.SaveAs "C:\Temp\MyDoc.doc"
MsgBox ActiveDocument.Name
End Sub
|
IMPORTANT
Make sure that the folder "C:\Temp" is a valid folder on your machine. If it's not, either create C:\Temp or change the line above to indicate a folder where the document can be saved. Once the document is saved, the line above displays a message box with the text: "MyDoc.doc." The Name property of the Document object now represents the filename, without the path.
The items in the Procedure drop-down list represent the list of exposed events that the Document object can react to. The Document object provides three separate event procedures: Document_Close, Document_New, and Document_Open. If a placeholder was created for the New event procedure, you can ignore it.
MsgBox "This document is now CLOSING." |
When the document closes, it's an event. When this event occurs and there's code in the event handler for the Close event of the document, the event handler runs. In this case, a message box saying "This document is now CLOSING." is displayed just before the document is closed and removed from memory.
MsgBox "This document is now OPEN!" |
When the document opens, it's an event. When this event occurs and there's code in the event handler, the event handler runs. In this case, a message box displaying the text "This document is now OPEN!" is displayed just after the document is opened and loaded into memory.
You can open and close the document several times. Each time you do, you trigger the Open and Close events, and the code you wrote in the event procedures runs.