C H A P T E R    10 Microsoft Office 97/Visual Basic Programmer's Guide

Shapes and the Drawing Layer

Contents

Visual Basic provides a common object model that represents the drawing layer in Microsoft Excel 97, Word 97, and PowerPoint 97. The top­level object in this object model is the Shapes collection, which contains all the graphic objects — such as AutoShapes, freeforms, OLE objects, and pictures — that you can add to the drawing layer. (Note that shapes you insert into the text layer in Word aren't included in the Shapes collection.)

This chapter covers the principal objects, properties, and methods used to create and modify objects in the drawing layer in Microsoft Excel, Word, and PowerPoint. For information about application­specific enhancements to the drawing layer object model in PowerPoint, see Chapter 6, "Microsoft PowerPoint Objects." For information about ActiveX controls (a special type of shape), see Chapter 12, "ActiveX Controls and Dialog Boxes."

Understanding the Shape, ShapeRange, and Shapes Objects

There are three different objects that represent shapes: the Shapes collection, which represents all the shapes in the drawing layer of a Microsoft Excel, Word, or PowerPoint document; the ShapeRange collection, which represents a subset of the shapes in the drawing layer; and the Shape object, which represents an individual shape. In general, you use the Shapes collection when you want to add shapes to the drawing layer or iterate through all the shapes in the drawing layer; you use the Shape object when you want to format or manipulate a single shape; and you use the ShapeRange collection when you want to format or manipulate multiple shapes the same way you work with multiple selected shapes in the user interface.

Note   A ShapeRange collection can have as few as one member or as many members as there are shapes in the drawing layer. A ShapeRange collection that contains a single member is essentially equivalent to a Shape object. You can use a ShapeRange collection that contains all the members in the Shapes collection to format all the shapes in the drawing layer at at the same time. Properties and methods that apply to the Shape object also apply to the ShapeRange collection . For information about how these properties and methods behave when they're applied to a ShapeRange collection that contains a single shape or to a ShapeRange collection that contains multiple shapes, see "Working with More Than One Shape" later in this chapter.

Returning the Shapes Collection

To return the entire collection of shapes in the drawing layer, use the Shapes property. The following example selects all the shapes in the drawing layer of myDocument. 

myDocument.Shapes.SelectAll

Returning the Shape Object

Use Shapes(index), where index is the shape's name or the index number, to return a Shape object that represents a shape on a slide. The following example duplicates the third shape on myDocument and places it on the Clipboard. 

myDocument.Shapes(3).Duplicate
The following example duplicates the shape named "Red Square" on myDocument. 
myDocument.Shapes("Red Square").Duplicate
Each shape is assigned a default name (for example, "Rectangle 3") when you add it to the Shapes collection. To give the shape a more useful, meaningful name, use the Name property. The following example adds a rectangle to myDocument and gives the rectangle the name "Red Square." 
myDocument.Shapes.AddShape(msoShapeRectangle,144, 144, 72, 72).Name = "Red Square"
Tip   The methods that add a shape to the drawing layer also return a reference to the added shape, so you can add a shape and apply a property or method to it in a single statement, as shown in the preceding example. For more information, see "Drawing a Shape on a Document, Worksheet, or Slide" later in this chapter.

Returning the ShapeRange Collection

Use Shapes.Range(index), where index is either the shape's name or index number or an array of shape names or shape index numbers (or both), to return a ShapeRange collection that represents a subset of the Shapes collection. The following example sets the fill for shapes one and three on myDocument. 

myDocument.Shapes.Range(Array(1, 3)).Fill.PresetGradient _
    msoGradientHorizontal, 1, msoGradientLateSunset

Use Selection.ShapeRange to return a ShapeRange collection that represents all the shapes in the selection. Use Selection.ShapeRange(index), where index is the shape's name or index number, to return a Shape object that represents one of the shapes in the selection. The following example sets the fill for the first shape in the selection. 

ActiveWindow.Selection.ShapeRange(1).Fill.PresetGradient _
    msoGradientHorizontal, 1, msoGradientLateSunset

Note   The macro recorder generates selection­based code — that is, when you work with a shape with the macro recorder turned on, it records a step for selecting the shape, records a step for accessing the ShapeRange collection in the selection, and then records the properties and methods you apply to the shape. When you write code from scratch or edit recorded code, you can create more efficient code by skipping the selection step and returning shapes directly from the Shapes collection.

Drawing a Shape on a Document, Worksheet, or Slide

Use one of the methods of the Shapes collection, listed in the following table, to add a shape to a document, worksheet, or slide. For detailed syntax information, see the Help topic for the specific method.

To add this kind of graphic Use this method
CalloutAddCallout
Sticky-note-like comment (PowerPoint only) AddComment
Line or curve that connects two other shapes (Microsoft Excel and PowerPoint) AddConnector
Bézier curve AddCurve
Native Microsoft Excel form control (Microsoft Excel only) AddFormControl
Rectangle with no line and no fill and an attached text frame AddLabel
LineAddLine
Sound or movie (PowerPoint only) AddMediaObject
ActiveX control (Word only; use AddOLEObject in Microsoft Excel and PowerPoint) AddOLEControl
Embedded or linked OLE object AddOLEObject
PictureAddPicture
Placeholder for text or for a graphic object (PowerPoint only) AddPlaceholder
Open polyline or closed polygon drawing AddPolyline
AutoShapeAddShape
Rectangle with no line and no fill and an attached text frame AddTextbox
WordArtAddTextEffect
Slide title (PowerPoint only) AddTitle
FreeformBuildFreeform and ConvertToShape

The following example adds a rectangle to myDocument. 

myDocument.Shapes.AddShape msoShapeRectangle, 50, 50, 100, 200

When you add a shape, you usually specify the dimensions of the shape and the position of the upper­left corner of the bounding box for the shape relative to the upper­left corner of the page, worksheet, or slide. Distances in the drawing layer are measured in points (72 points = 1 inch).

The methods that add shapes to the drawing layer return a reference to each added shape. You can therefore add a shape and apply properties and methods to it in a single step, as shown in the following example, which adds a shape to myDocument and sets its name it the same statement. 

myDocument.Shapes.AddShape(msoShapeIsoscelesTriangle, 10, 10, 100, 100).Name = "shpOne"
The following example adds a shape to myDocument and formats its fill. 
With myDocument.Shapes.AddShape(msoShapeRectangle, 90, 90, 90, 50).Fill
    .ForeColor.RGB = RGB(128, 0, 0)
    .BackColor.RGB = RGB(170, 170, 170)
    .TwoColorGradient msoGradientHorizontal, 1
End With
Editing a Shape

You can use properties and methods of the Shape and ShapeRange objects to move, resize, or delete a shape; change its appearance; or add text to it.

Finding the Properties and Methods You Need to Perform a Task

Properties and methods that control attributes and behavior common to all types of shapes apply directly to the Shape and ShapeRange objects. Related properties and methods that apply to specific types of shapes are encapsulated in secondary objects that you return from the Shape object.

Common Properties and Methods

Properties and methods that control and attributes and behavior common to shapes of different types apply directly to the Shape and ShapeRange objects. This group includes properties that control the size and position of the shape (such as Left, Top, Height, and Width) and methods that control generic editing behavior (such as Duplicate and ZOrder). The following example sets the size of shape one on myDocument. 

With myDocument.Shapes(1)
    .Height = 50
    .Width = 100
End With

Properties and Methods for Specific Types of Shapes

Related shape attributes that apply to a specific type of shape are grouped under secondary objects, such as the FillFormat object, which contains the properties that apply to shapes with fills, or the CalloutFormat object, which contains all the properties that are unique to callouts. To set these kinds of attributes for a shape, you must first return the object that contains them and then set properties of that object. For example, you use the Fill property to return the FillFormat object, and then you set the ForeColor property of the FillFormat object to set the fill foreground color for the specified shape. The following example sets the foreground color to red for the fill for shape one on myDocument. 

myDocument.Shapes(1).Fill.ForeColor.RGB = RGB(255, 0, 0)

The following table shows the objects accessible from the Shape object that contain functionally related properties and methods. Note that some of the properties that return these secondary objects have the same name as the returned object (for example, the PictureFormat property returns the PictureFormat object) whereas other properties have the name of the returned object minus the word "Format" (for example, the Fill property returns the FillFormat object).

Use this property of the Shape object To return this object Which contains properties and methods that apply to
CalloutCalloutFormat Callouts
ConnectorFormat (Microsoft Excel and PowerPoint only) ConnectorFormat Connectors
ControlFormat(Microsoft Excel only) ControlFormat Native form controls
FillFillFormat Shapes that can contain fills (all shapes except lines)
LineLineFormat All shapes (the LineFormat object can represent a line or a shape's border)
LinkFormat LinkFormatLinked OLE objects, linked pictures (Word only), and linked fields (Word only)
OLEFormat OLEFormatOLE objects
PictureFormat PictureFormatPictures and OLE objects
ShadowShadowFormat All shapes
TextEffect TextEffectFormatWordArt objects
ThreeDThreeDFormat Shapes that can be extruded
WrapFormat(Word only) WrapFormat Shapes that text will wrap around

Trying to return certain secondary objects (such as the CalloutFormat, ConnectorFormat, OLEFormat, PictureFormat, or TextEffectFormat object) from an inappropriate type of shape can cause an error. For example, if you apply the OLEFormat property to a shape that isn't an OLE object, you'll get an error. (Trying to return certain other secondary objects — such as the FillFormat, LineFormat, ShadowFormat, or ThreeDFormat object — from an inappropriate type of shape doesn't cause an error.)

To avoid problems, check the Type property and, when applicable, the AutoShapeType property of a shape before applying a property or method that applies only to certain types of objects, and be sure to include error handling in your code. The following example updates all linked OLE objects on myDocument. Note that you cannot change the type of an existing object; for example, you cannot change an object that's not a picture into a picture. 

For Each sh In myDocument.Shapes
    If sh.Type = msoLinkedOLEObject Then
       sh.LinkFormat.Update
    End If
Next

For information about error handling, see Chapter 14, "Debugging and Error Handling."

Working with the Shape's Fill

The FillFormat object represents a shape's fill. You use properties and methods of the FillFormat object to set the type, color, and transparency of the fill. Because there are a number of factors that determine a fill's appearance, many individually valid property settings for the FillFormat object don't make any sense in combination with other properties or without additional information being supplied. For example, the value msoPatternDarkVertical for the Pattern property doesn't make much sense in conjunction with the value msoGradientDiagonalUp for the GradientStyle property, and the value msoFillPicture for the Type property doesn't make sense if you haven't specified a picture file to use.

So that you don't inadvertently assign incompatible values to individual properties of the FillFormat object or neglect to supply a necessary piece of information when you assign a property value, most of the properties are read­only. You can set their values only by using methods that set multiple individual properties to compatible values at the same time. For example, you could not write code that would leave you with the two incompatible settings mentioned in the preceding paragraph, because using the Patterned method to set a patterned fill automatically sets the GradientStyle property to msoGradientMixed, and using the OneColorGradient, PresetGradient, or TwoColorGradient method to set a gradient fill automatically sets the value of the Pattern property to msoPatternMixed.

Use one of the following methods to set a shape's fill type: Background (PowerPoint only), OneColorGradient, Patterned, PresetGradient, PresetTextured, Solid, TwoColorGradient, UserPicture, or UserTextured. You can also use any of the following read/write properties to control the fill's appearance: BackColor, ForeColor, Transparency, or Visible.

The following example adds a rectangle to myDocument and then sets the foreground color, background color, and gradient for the rectangle's fill. 

With myDocument.Shapes.AddShape(msoShapeRectangle, 90, 90, 90, 50).Fill
    .ForeColor.RGB = RGB(128, 0, 0)
    .BackColor.RGB = RGB(170, 170, 170)
    .TwoColorGradient msoGradientHorizontal, 1
End With

Adding Shadows and 3­D Effects

Use the Shadow property of the Shape object to return the ShadowFormat object, and use the properties and methods of the ShadowFormat object to edit a shape's shadow. The following example sets the shadow for shape three on myDocument to semitransparent red. If the shape doesn't already have a shadow, this example adds one to it. 

With myDocument.Shapes(3).Shadow
    Visible = True
    .ForeColor.RGB = RGB(255, 0, 0)
    .Transparency = 0.5
End With

Use the ThreeD property of the Shape object to return the ThreeDFormat object, and use the properties and methods of the ThreeDFormat object to edit a shape's extrusion. The following example adds an oval to myDocument and then specifies that the oval be extruded to a depth of 50 points and that the extrusion be purple, orthographic, and lit from the left. 

Set myShape = myDocument.Shapes.AddShape(msoShapeOval, 90, 90, 90, 40)
With myShape.ThreeD
    .Visible = True
    .Depth = 50
    .ExtrusionColor.RGB = RGB(255, 100, 255)    ' RGB value for purple
    .Perspective = False
    .PresetLightingDirection = msoLightingLeft
End With

You cannot apply three­dimensional formatting to certain kinds of shapes. Most of the properties and methods of the ThreeDFormat object for such a shape will fail.

Note   If you don't see the shadow or extrusion you expect, make sure that the Visible property of the ShadowFormat or ThreeDFormat object is set to True.

Adding Text to a Shape

The area within a shape that can contain text is called a text frame. The TextFrame object of a given shape contains the text in the text frame as well as the properties and methods that control the alignment and anchoring of the text frame.

Note   Only built­in, two­dimensional AutoShapes have text frames; lines, connectors, freeforms, pictures, OLE objects, and media objects don't. Before applying the TextFrame property to a shape, check to see whether the shape has a text frame. In PowerPoint, you can do this by checking the value of the HasTextFrame property. In Microsoft Excel and Word, check the Type property of the shape to see whether it's a type of shape that can contain text. You should always include error handling in case the TextFrame property gets applied to a shape that doesn't have a text frame.

In Word, use the TextRange property of the TextFrame object to return a Range object that represents the range of text inside the specified text frame. The following example adds text to the text frame for shape one in the active document. 

ActiveDocument.Shapes(1).TextFrame.TextRange.Text = "My Text"

In Microsoft Excel, use the Characters property of the a TextFrame object to return a Characters object that represents the text inside the specified text frame. The following example adds text to the text frame for shape one on the active worksheet. 

ActiveWorksheet.Shapes(1).TextFrame.Characters.Text = "My Text"

In PowerPoint, use the TextRange property of the TextFrame object to return a TextRange object that represents the range of text inside the specified text frame. The following example adds text to the text frame for shape one on slide one in the active presentation. 

ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Text = "My Text"

Working with OLE Objects on a Document, Worksheet, or Slide

You use the properties and methods of the OLEFormat object — such as the Activate and DoVerb methods — to control the OLE object contained in a shape. Use the OLEFormat property of the Shape object to return the OLEFormat object. The following example performs the default verb for shape three on myDocument if this shape contains an OLE object. 

With myDocument.Shapes(3)
    If .Type = msoEmbeddedOLEObject Or _
             .Type = msoLinkedOLEObject Then
       .OLEFormat.DoVerb
    End If
End With

Use the Object property of the OLEFormat object to return the OLE object contained in the specified shape. (In Microsoft Excel, you must use the Object property twice in a row, separated by the dot operator, to return the OLE object.) The following example, run from Word or PowerPoint, adds text to cell A1 on worksheet one in the Microsoft Excel workbook contained in shape three on myDocument. 

With myDocument.Shapes(3)
    .OLEFormat.Activate
    .OLEFormat.Object.Worksheets(1).Range("A1").Value = "New text"
End With

Use the Application property of the OLE object returned by the Object property to return the top­level object of the application that created the OLE object. The following example, run from Microsoft Excel, displays the name of the application in which each embedded OLE object on the active sheet was created. Notice that you must use the Object property twice in a row to return an OLE object in Microsoft Excel. 

For Each s In ActiveSheet.Shapes
    If s.Type = msoEmbeddedOLEObject Then
       s.OLEFormat.Activate
       MsgBox s.OLEFormat.Object.Object.Application.Name
    End If
Next

For information about using ActiveX controls (a special type of interactive OLE object), see Chapter 12, "ActiveX Controls and Dialog Boxes."

Working with More Than One Shape

There are several ways to work with multiple shapes. If you want to set properties for multiple shapes individually, you can loop through a Shapes or ShapeRange collection and apply properties and methods to the individual Shape objects in the collection. If you want to apply a property or method to multiple shapes at the same time, you can construct a ShapeRange collection that contains the shapes and then apply the property or method to the ShapeRange collection. If you want to form a single shape out of multiple shapes that can then be formatted, sized, and positioned as a single entity, you can group the shapes. If you want to position shapes relative to each other, you can align and distribute them horizontally or vertically.

Constructing a Shape Range That Contains Only Certain Types of Shapes

If you want to construct a shape range that contains only the shapes in the specified collection that possess a certain attribute or attributes, use a conditional statement to test for the attributes you want, and add the names or index numbers of the shapes that satisfy your conditions to a dynamic array. You can then construct a shape range by using this array as an argument. The following example constructs a shape range that contains all the AutoShapes on myDocument and then groups them. 

With myDocument.Shapes
    numShapes = .Count
    If numShapes > 1 Then
       numAutoShapes = 0
       ReDim autoShpArray(1 To numShapes)
       For i = 1 To numShapes
            If .Item(i).Type = msoAutoShape Then
                numAutoShapes = numAutoShapes + 1
                autoShpArray(numAutoShapes) = .Item(i).Name
            End If
       Next
       If numAutoShapes > 1 Then
            ReDim Preserve autoShpArray(1 To numAutoShapes)
            Set asRange = .Range(autoShpArray)
            asRange.Group
       End If
    End If
End With

Tip   If you want to include shapes that have one of several possible values for a property in an array — for example, if you want to include all shapes that are of type msoEmbeddedOLEObject, msoLinkedOLEObject, msoLinkedPicture, or msoPicture — use a Select Case structure instead of an If…End If structure to determine which shapes to include in the shape range.

Applying a Property or Method to Several Shapes at the Same Time

In the user interface, there are some operations you can perform with several shapes selected; for example, you can select several shapes and set all their individual fills at once. There are other operations you can only perform with a single shape selected; for example, you can only edit the text in a shape if a single shape is selected.

In Visual Basic, there are two ways to apply properties and methods to a set of shapes. These two ways allow you to perform any operation that you can perform on a single shape on a range of shapes, whether or not you can perform the same operation in the user interface.

  • If the operation works on a multiple selected shapes in the user interface, you can perform the same operation in Visual Basic by constructing a ShapeRange collection that contains the shapes you want to work with, and applying the appropriate properties and methods directly to the ShapeRange collection.

  • If the operation doesn't work on multiple selected shapes in the user interface, you can still perform the operation in Visual Basic by looping through the Shapes collection or through a ShapeRange collection that contains the shapes you want to work with, and applying the appropriate properties and methods to the individual Shape objects in the collection.

Important   Many properties and methods that apply to the Shape object and the ShapeRange collection fail if they're applied to certain kinds of shapes. For example, the TextFrame property fails if it's applied to a shape that cannot contain text. If you're not positive that each of the shapes in a ShapeRange collection can have a certain property or method applied to it, don't apply the property or method to the collection. If you want to apply one of these properties or methods to a collection of shapes, you must loop through the collection and test each individual shape to make sure that it's an appropriate type of shape before applying one of these properties or methods to it.

Applying a Property or Method to a ShapeRange Collection

If you can perform an operation on multiple selected shapes in the user interface at the same time, you can do the programmatic equivalent by constructing a ShapeRange collection and then applying the appropriate properties or methods to it. The following example constructs a shape range that contains the AutoShapes named "Big Star" and "Little Star" on myDocument and applies a gradient fill to them. 

Set myRange = myDocument.Shapes.Range(Array("Big Star", "Little Star"))
myRange.Fill.PresetGradient msoGradientHorizontal, 1, msoGradientBrass

The following are general guidelines for how properties and methods behave when they're applied to a ShapeRange collection:

  • Applying a method to a the collection is equivalent to applying the method to each individual Shape object in that collection.

  • Setting the value of a property of the collection is equivalent to setting the value of the property of each individual shape in that collection.

  • A property of the collection that returns a constant returns the value of the property for an individual shape in the collection if all shapes in the collection have the same value for that property. If not all shapes in the collection have the same value for the property, it returns the "mixed" constant.

  • A property of the collection that returns a simple data type (such as Long, Single, or String) returns the value of the property for an individual shape if all shapes in the collection have the same value for that property.

  • The value of some properties can be returned or set only if there's exactly one shape in the collection. If there's more than one shape in the collection, a run­time error occurs. This is generally the case for returning or setting properties when the equivalent action in the user interface is possible only with a single shape (actions such as editing text in a shape or editing the points of a freeform).

The preceding guidelines also apply when you are setting properties of shapes that are grouped under secondary objects of the ShapeRange collection, such as the FillFormat object. If the secondary object represents operations that can be performed on multiple selected objects in the user interface, you will be able to return the object from a ShapeRange collection and set its properties. For example, you can use the Fill property to return the FillFormat object that represents the fills of all the shapes in the ShapeRange collection. Setting the properties of this FillFormat object will set the same properties for all the individual shapes in the ShapeRange collection.

Looping Through a Shapes or ShapeRange Collection

Even if you cannot perform an operation on several shapes in the user interface at the same time by selecting them and then applying a command, you can perform the equivalent action programmatically by looping through the Shapes or ShapeRange collection that contains the shapes you want to work with and applying the appropriate properties and methods to the individual Shape objects in the collection. The following example loops through all the shapes on myDocument and adds text to each shape that's an AutoShape. 

For Each sh In myDocument.Shapes
    If sh.Type = msoAutoShape Then
            sh.TextFrame.TextRange.InsertAfter " (version 1)"
    End If
Next

Grouping, Aligning, Distributing, and Layering Shapes

Use the Align and Distribute methods of the ShapeRange object to align or evenly distribute shapes horizontally or vertically. Use the ZOrder method of the Shape or ShapeRange object to change the layering order of shapes on a document relative to one another. For examples of the syntax you use to perform these operations, see the appropriate Help topics in Microsoft Excel, Word, or PowerPoint.

When you want to work with multiple shapes as a single entity, you can group a range of shapes together into single shape by using the Group method of the ShapeRange collection. The following example adds two shapes to myDocument, groups the two new shapes together, sets the fill for the group, rotates the group, and then sends it to the back of the drawing layer. 

With myDocument.Shapes
    .AddShape(msoShapeCan, 50, 10, 100, 200).Name = "shpOne"
    .AddShape(msoShapeCube, 150, 250, 100, 200).Name = "shpTwo"
    With .Range(Array("shpOne", "shpTwo")).Group
       .Fill.PresetTextured msoTextureBlueTissuePaper
       .Rotation = 45
       .ZOrder msoSendToBack
    End With
End With

Use the Ungroup method of the Shape object to ungroup a group of shapes, and use the Regroup method of the ShapeRange collection to restore a group of shapes that you've ungrouped.

If you want to work with the individual shapes in a group without ungrouping them, use the GroupItems property of the Shape object that represents the group of shapes to return the GroupShapes object, and use the Item method of the GroupShapes object to return an individual shape within the group of shapes. The following example adds three triangles to myDocument, groups the triangles, sets a color for the entire group, and then changes the color for the second triangle only. 

With myDocument.Shapes
    .AddShape(msoShapeIsoscelesTriangle, 10, 10, 100, 100).Name = "shpOne"
    .AddShape(msoShapeIsoscelesTriangle, 150, 10, 100, 100).Name = "shpTwo"
    .AddShape(msoShapeIsoscelesTriangle, 300, 10, 100, 100).Name = "shpThree"
    With .Range(Array("shpOne", "shpTwo", "shpThree")).Group
       .Fill.PresetTextured msoTextureBlueTissuePaper
       .GroupItems(2).Fill.PresetTextured msoTextureGreenMarble
    End With
End With