O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Program with Drawing Objects

Any object that can be drawn is considered a Shape object in Excel. You get individual Shape objects from the Worksheet or Chart object's Shapes collection. The Shape object is unusual because it encapsulates the members from more specific object types. Because of that, any given Shape object may or may not support any given Shape member. For example, the following code flips simple shapes on a worksheet, doesn't affect embedded objects, and causes an error if the worksheet contains a diagram:

Sub FlipObjects( )
    Dim s As Shape
    For Each s In ActiveSheet.Shapes
        ' Doesn't affect embedded objects. Error on diagrams!
        s.Flip msoFlipHorizontal
    Next
End Sub

There are three general categories of Shape objects. You can determine the category of a Shape object by comparing its Type property to those listed in Table 18-1.

Table 18-1. Use the Type property to determine which Shape members are available

Category

Shape Type

Programming notes

Simple shapes

msoAutoShape, msoFreeForm, msoLine, msoLinkedOLEObject, msoLinkedPicture, msoPicture, msoTextBox, msoTextEffect

Most Shape members are supported.

Embedded objects

msoChart, msoComment, msoEmbeddedOLEObject, msoFormControl, msoOLEControlObject

Convert these objects to a specific type for access to their members.

Diagram shapes

msoDiagram

Accessing most Shape members causes an error.

Draw Simple Shapes

Use the Shapes collection Add methods to draw shapes from code. Table 18-2 lists the various Add

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required