Learning the Members of the Object Model

Just as an application is rich in features and functionality, the equivalent object model is filled with members that represent the same features and functionality. An average object model in Office contains hundreds of members, including collections, objects, properties, methods, and events. To learn all of the members of a model, you may have to switch frequently between the corresponding Visual Basic Help file and the Visual Basic Editor window.

Visual Basic for Applications provides four tools that simplify the search for the list of properties, methods, or events that an object supports. They are the Macro Recorder, the Object Browser, Auto List Members, and Auto Quick Info.

Macro Recorder

Word, Excel, and PowerPoint provide a macro recorder that helps you quickly learn the object model of the application. (Access and Outlook don't provide a macro recorder.) Macro recording provides the equivalent Visual Basic code of an action that you conduct through an application's graphical interface.

For example, to change the color of a shape on a PowerPoint slide, you select the shape, click AutoShape on the Format menu, and then select a fill color from the Color drop-down list (in the Color And Lines tab). The equivalent macro-recorded code is as follows:

Sub Macro1()" Macro recorded 1/1/99 by David Boctor' ActiveWindow.Selection.SlideRange.Shapes _ .AddShape(msoShapeRectangle, 246, _ 288, 138, 132).Select With ActiveWindow.Selection.ShapeRange .Fill.Visible = msoTrue .Fill.Solid .Fill.ForeColor.RGB = RGB(128, 0, 0) End With End Sub

NOTE
Macro recording is selection-based. This means that the Macro Recorder will record actions on selected objects in an active window.

The macro-recorded code gives you a good start on whatever you might want to do or create with Visual Basic code. Look at the first working line of code in the macro above and you'll see how to add a shape to a slide. If you want to add a shape to a specific slide (for example, the second one) rather than the slide in the active window, you can replace the code in the Macro1 procedure with the following code in Macro2:

Sub Macro2() Dim NewShape As Shape Set NewShape = ActivePresentation.Slides(2). _ Shapes.AddShape(msoShapeRectangle, 246, _ 288, 138, 132) End Sub

Make sure you have at least two slides in the active presentation. The code above adds a rectangle to the second slide. This line closely resembles the first line of the recorded macro. You just replaced ActiveWindow.Selection.SlideRange with ActivePresentation.Slides(2) so that your code works on a specific slide; you don't have to have the slide displayed in the active window. This macro also demonstrates how to declare a variable (NewShape) that represents the new shape that's added to the slide. You'll learn more about declaring variables and working with shapes and other objects later in this book.

Although most macro-recorded code doesn't provide the exact code you need for your solution, it does provide the exact syntax you need to manipulate many objects within Word, Excel, and PowerPoint. It also helps you write Visual Basic code without constantly searching through the Help file.

Recording Macros

A macro is a Visual Basic for Applications program that automates a series of actions. These can range from a simple procedure you conduct with your document content and application tools to complex solutions that manipulate your document content and interact with other Office features. A macro resides within the Visual Basic project of a Word document, an Excel workbook, a PowerPoint presentation, or an Access database. Because macros are usually actions grouped together, they enable you to accomplish a series of common, often repetitive, tasks automatically with a single command. You often create simple macros to make editing and formatting tasks readily available through a dialog box.

Word, Excel, and PowerPoint each offer an easy way to create a simple macro: with the Macro Recorder. Access and Outlook don't provide a macro recorder. You can see a recorded macro's contents in the Visual Basic Editor, where you can easily modify the macro. Recording a macro is like recording music or video. When you play it back, it automatically repeats recorded actions.

Each macro you record is stored in a Visual Basic code module that's attached to the open document, workbook, presentation, or template. Using the Visual Basic Editor, you can edit macros or move macros from one code module to another in any open Visual Basic project.

Record a Macro that Sets the Same Formatting to Multiple Shapes

Imagine that you want to manually change the color of a shape on a PowerPoint slide. To do that you have to select the shape, choose AutoShape from the Format menu, and then select a color from the Fill Color drop-down list (in the Color And Lines tab in the Format AutoShape dialog box). To determine the equivalent functionality in Visual Basic syntax isn't necessarily a straightforward task for a beginner or even an experienced Visual Basic programmer. Fortunately, macro recording provides what you need without much effort.

Start PowerPoint. In the opening PowerPoint dialog box, select Blank Presentation and click OK.

In the New Slide dialog box, select the second slide AutoLayout (Bulleted List) and click OK.

On the Tools menu, point to Macro, and then click Record New Macro.

The Record Macro dialog box is displayed. Notice the default macro name in the Macro Name box and the presentation name in which the recorded macro will be stored.

Click OK to accept the default names.

Macro recording has started, and the Stop Recording toolbar is displayed on your screen.

Until macro recording is stopped, the equivalent Visual Basic code of most of the actions you conduct is recorded in a code module in the Visual Basic Editor.

On the Drawing toolbar, which is the same for Word, Excel, and PowerPoint, click the Rectangle button.

Rectangle

Drag a rectangle anywhere on the slide.

On the Format menu, click AutoShape to display the Format AutoShape dialog box.

In the Colors And Lines tab, click the Color drop-down list and select a color from the color grid. Click OK.

Stop the macro recording by clicking the Stop Recording button on the Stop Recording toolbar.

Stop Recording

Start the Visual Basic Editor by pressing ALT+F11 and, in the Project Explorer window, open Presentation1 by clicking the plus sign next to it.

This is the presentation that you selected to store the recorded macro.

If necessary, open the Modules folder by clicking the plus sign next to it.

Double-click Module1. This displays the Code window.

The following code was recorded:

Sub Macro1() ' ' Macro recorded 1/1/99 by David Boctor ' ActiveWindow.Selection.SlideRange.Shapes _ .AddShape(msoShapeRectangle, 246, _ 288, 138, 132).Select With ActiveWindow.Selection.ShapeRange .Fill.Visible = msoTrue .Fill.Solid .Fill.ForeColor.RGB = RGB(128, 0, 0) End With End Sub

Your recorded code may be slightly different. This macro is currently stored in the Visual Basic project of the active presentation.

Run the Recorded Macro to Create a Shape on Another Slide

Switch to PowerPoint by clicking the View Microsoft PowerPoint button on the Standard toolbar in the Editor.

View Microsoft PowerPoint

On the Insert menu, click New Slide.

Select the second slide icon (Bulleted List) in the AutoLayout dialog box and then click OK.

On the Tools menu, point to Macro, and then click Macros.

In the Macro dialog box, select Macro1 from the list, and then click Run.

On the new slide, Visual Basic automatically re-creates the same shape in the same color that you previously created manually.

Object Browser

Many road maps have an index that lists all the locations on the map. To give the Visual Basic programmer a similar guide for navigating through an object model, the Visual Basic Editor provides a tool window called the Object Browser. By learning to use it effectively you'll save a lot of time.

Look at the Object Browser

Start PowerPoint. In the opening PowerPoint dialog box, click Blank Presentation and then click OK.

In the New Slide dialog box, select any slide layout and click OK.

On the Tools menu, point to Macro, and then click Visual Basic Editor on the submenu.

In the Editor, on the View menu, click Object Browser. (You can also press F2.)

The Object Browser's window contains five main elements: the Project/Library drop-down list, a Search text box with Search Results box, the Classes list, the Members Of list, and the Details pane.

Project/Library drop-down list This list displays the currently referenced libraries for the active Visual Basic project. By default, <All Libraries> is selected in the text box, which allows all of the libraries to be displayed in the Object Browser at one time.

You'll find the object model definition for each application in Office (and for any other application that supports an object model) in a file referred to as an object library. An object library's filename usually contains the extension .olb. However, sometimes an object library is contained within a dynamic-link library (.dll) file. Generally, the object library file is just an information file for whatever objects, properties, methods, events, and enumerations an application exposes. By providing an object library, Visual Basic can search for the objects an application exposes without having to load the entire application itself and then ask it for a list of exposed objects.

In Part 2 of this book, you'll see that referencing an object library is important when you program Office using objects and functionality from multiple applications. That's because you can do it all from within one Visual Basic Editor window.

Search text box with Search Results box The Search Results box displays a list of libraries, classes, or members matching the criteria you specify in the Search text box. You'll learn more about these boxes in the next example.

Classes list This list displays all the objects exposed in a given object library. When you select <All Libraries> in the Project/Library drop-down list, all objects in all libraries are listed. However, if you select a specific library in the Project/Library drop-down list, only the objects in the specified library are listed.

For example, you can start PowerPoint, display its Visual Basic Editor, and press F2 for the Object Browser. In the Project/Library drop-down list, select PowerPoint. In the Classes list, you'll see only the objects that PowerPoint exposes. The list has a number of objects, including familiar ones such as Presentation, Slide, and Shape.

Members Of list This list displays all the properties, methods, and events that are supported by a selected item in the Classes list.

If you select PowerPoint from the Project/Library drop-down list and then select Presentation from the Classes list, in the Members Of Presentation list you'll see the methods and properties that you might know from the PowerPoint menus and toolbars. Familiar methods include Close, Save, SaveAs, and PrintOut, while common properties include Name, Path, and FullName.

Details pane When you select an item in the Members Of list, the contents of the Details pane reveal detailed information about the selected member.

If you select Presentation from the Classes list and then PageSetup in the Members Of list, the Details pane displays the following information:

Note that the last word in the first line in the Details pane, "PageSetup," is underlined and green. This indicates two things: (1) the PageSetup property returns an object called PageSetup, and (2) you can navigate to the PageSetup object by clicking the link.

Search for Information About an Object

The Search Results box gives you a quick way to determine which members in an object library support certain properties, methods, or events. After searching through the specified object library or libraries, it lists members that match the criteria you specified in the Search text box. If you select <All Libraries> in the Project/Library box, the search is conducted in all the libraries in the Project/Library list; if you select a specific library, the search scans that library only.

In the Object Browser, in the Project/Library box, select PowerPoint.

Right-click anywhere in the Object Browser window. This displays the shortcut menu for the Object Browser.

Select Find Whole Word Only.

In the Search text box, type name.

Click the Search button next to the Search text box.

Search button

The first column in the Search Results list is the Library; the second column is the Class, or object; and the third is the Member property. Selecting any row in the Search Results list refreshes the contents of the Classes list and the Members Of list, so you can navigate to the exact location of the search result item in the object library.

Auto List Members

One of the Visual Basic Editor's newest and most exciting additions is the Auto List Members drop-down list, which you saw briefly in a previous example. Because of it, you never have to memorize the methods or properties of an object again. All you need to do is start typing, and once you type a period (.) after a valid object name, Auto List Members automatically displays a drop-down list of all the properties and methods supported by the object. You can scroll down the list with the mouse, or you can continue typing the method or property name. If you continue typing, Auto List Members selects an item in the list that matches your typing.

Enter Properties the Hard Way

Imagine that you want to define a name of an object within one of your procedures.

In the Visual Basic Editor, insert a code module, type Sub WithoutDeclaration, and press ENTER.

Between the lines Sub WithoutDeclaration and End Sub, add the following line of code:

MsgBox sldSlide.Name

When you start to use a term or variable that the Editor doesn't recognize, you have to do all of the work yourself. In this case, you must already know that sldSlide is the name of a slide and that a Name property applies to slides.

Enter Properties the Easy Way with Auto List Members

When you declare an object variable, you also take advantage of the Auto List Members tool. The Auto List Members drop-down list displays information that would logically complete the statement at the current insertion point.

Add the following declaration and Set statements above the line of code you've already typed:

Dim sldSlide As Slide Set sldSlide = ActivePresentation.Slides(1)

You've now declared the sldSlide variable as representing a PowerPoint Slide object, and the Visual Basic Editor now knows what type of object this variable references. Your procedure should look like this:

Sub WithoutDeclaration() Dim sldSlide As Slide Set sldSlide = ActivePresentation.Slides(1) MsgBox sldSlide.Name End Sub

Now delete the line you previously typed, MsgBox sldSlide.Name, and then type it again. (Yes, this is the same line that you typed before, but something different should happen.)

MsgBox sldSlide.Name

When you start typing the line above and get to the point where you type a period (.) after sldSlide, the Auto List Members drop-down list appears.

At this point, you can scroll down the list and select the property or method item, or you can continue to type the name of the property or method if you know it. As you continue to type, the list automatically scrolls down to find a match for the text you started to type. If the property or method name is long and the item is selected in the Auto List Members drop-down list, you can press TAB to insert the item in your line of code.

Select Name in the list and press TAB.

Press F5 to run the macro.

Exit PowerPoint without saving changes.

When Auto List Members Doesn't Appear
When you type the dot ('.') as you enter code in the Visual Basic Editor in Word, Excel, PowerPoint, Access, and Outlook, the Auto List Members drop-down list sometimes won't appear. For example, start Excel, display the Visual Basic Editor, click Module on the Insert menu, and then type in the following procedure.
Sub ParentProperty()
    Dim sh As Worksheet, shNew As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name = "Sheet3" Then
            Set shNew = sh.Parent.Worksheets.Add(Before:=sh)
            shNew.Name = "New Sheet"
            Exit For
        End If
    Next sh
End Sub
In this procedure, sh.Parent returns the Workbook object. The parent of a worksheet isn't the Worksheets collection, it's the workbook. In all cases in Word, Excel, and PowerPoint, the parent of an item in a collection isn't the collection but the parent of the collection itself. In a number of cases, the Parent property of an object returns an object defined as the generic Object type. When you type the dot ('.') after the code sh.parent in the first line in the If…Then block in the preceding procedure, the Auto List Members drop-down list doesn't appear. You can create a new variable, such as wb, declare it as a Workbook, and assign wb to sh.Parent.

The Parent property allows you to return the object from which you access another object.
Sub ParentPropertyWithAutoListMembers()
    Dim sh As Worksheet, shNew As Worksheet
    Dim wb As Workbook
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name = "Sheet3" Then
            Set wb = sh.Parent
            Set shNew = wb.Worksheets.Add(Before:=sh)
            shNew.Name = "New Sheet"
            Exit For
        End If
    Next sh
End Sub
In the new procedure, you declare the variable wb as a Workbook and assign it to the return value of sh.Parent, which is always a Workbook object. You then use the variable wb in the line that adds a new worksheet. When you type in the dot after the variable wb, the Auto List Members drop-down list appears. If you executed the procedure ParentProperty before the new procedure ParentPropertyWithAutoList-Members, make sure you switch back to Excel and delete the worksheet "New Sheet." If you don't, the above procedure will fail to rename the new worksheet.

Auto Quick Info

As you write your code, you have a number of ways to determine the exact syntax of the object, methods, and properties you use. One of the easiest is to display the Auto Quick Info window, which shows function information and parameters as you type. The Auto Quick Info window is similar to the ToolTip you see when the cursor is over a toolbar button.

In the "Pass Arguments to the SaveAs Method in PowerPoint" section of this chapter, when you pressed the spacebar after typing the SaveAs method name, the Auto Quick Info window appeared as follows:

You can see in the Auto Quick Info window that the SaveAs method of the Presentation object in PowerPoint takes three arguments. Each argument in the Auto Quick Info window is separated by a comma (,); as you type a comma, the next argument in the window becomes bold. Some arguments are encased in square brackets, which indicate that specifying the argument is optional. In the SaveAs method above, the first argument is required and the second and third are optional. If the argument is optional, the application defines a default value for it.

In the SaveAs method, the second argument, FileFormat, defaults to the current version of a PowerPoint presentation. You can also see in the case of the second argument that when you type a comma, both the Auto Quick Info window and the Auto List Members drop-down list are displayed. The Auto List Members drop-down list is displayed because the FileFormat argument is one of the enumeration values (PpSaveAsFileType) in the list.

TIP
When both the Auto Quick Info window and the Auto List Members drop-down list are displayed, you can toggle between them by clicking either one to put it in front of the other.