10.5 Drawing Objects (Shapes)

10.5 Drawing Objects ( Shapes )

Overview

The Shape object serves primarily to represent autoshapes (lines, rectangles, arrows, stars, etc.; see the "Drawing" toolbar).

These objects take the place of the various drawing objects in Excel 5/7. However, the large number of related objects can be a source of confusion.

The Shapes enumeration enables access to all Shape objects of a worksheet or chart sheet. For the insertion of new drawing objects there is a long list of methods available, such as AddShape for autoshapes and AddLine for lines.

ShapeRange enables the simultaneous editing of several Shape objects (as if these objects were selected with Shift and the mouse).

Freehand shapes (that is, freely drawn line segments) represent a particular form of Shape objects. In this case, the property ShapeNodes refers to a like-named enumeration of ShapeNode objects. These objects contain, among other attributes, coordinate points of the individual line segments.

A Shape object is also used for managing a so-called group (in interactive mode: pop-up menu command Grouping). In this case the property GroupItems leads to a GroupShape object, which, in turn , takes over the management of the group elements. Group elements can include not only Shape objects, but also charts and OLE objects, among others.

Finally, Shape is used to manage completely foreign objects, such as for MS Forms control objects ( Type=msoOLEControlObject ). In this case, Shape stands between the worksheet or chart sheet and the actual object. Shape is then concerned , among other things, with the positioning of the control. For communication between the sheet and the control the ControlFormat object is employed, which is addressed via the like-named property of Shape . ControlFormat is generally transparent, because its properties appear in the properties window of the control and can be used like control properties.

Shape Properties

AutoShapeType : The two most important properties are surely Type and AutoShapeType . If Type=msoAutoShape is set, then with AutoShapeType one of countless autoshape types can be specified (there are more than 130). On the other hand, if no autoshape is represented by the Shape object, then the object type is specified by the msoShapeType constants. Elements such as msoChart , msoComment , msoEmbeddedOLEObject , msoFreeForm , msoGroup , msoOLEControlObject , and msoTextBox prove that internally to Excel every object that is located outside of a cell is controlled by Shape objects.

Positioning : For each object is saved the upper left corner ( Left and Top ) as well as the width and height ( Width and Height ). These coordinates are figured from the upper left-hand corner of the form or worksheet. TopLeftCell and BottomRightCell specify the cells under the upper left-hand corner and lower right-hand corner. Placement determines how the control should behave when the worksheet is changed ( xlMoveAndSize , xlMove , or xlFreeFloating ).

Format : The possibilities for visual appearance are practically without bound. Each of the following properties leads to a particular object (whose name is given in parentheses if it is different from that of the property): Adjustments , Callout (CalloutFormat) , Fill (FillFormat) , Hyperlink , Line (LineFormat) , PictureFormat , Shadow (ShadowFormat) , TextEffect (TextEffectFormat) , TextFrame , and ThreeD (ThreeDFormat) . Perhaps this superfluity of objects is too much of a good thing.

Other : Depending on which objects are represented by Shape , there are further properties available: ConnectorFormat (if the object is bound to other objects), ControlFormat (for controls), GroupItems (for object groups), Nodes (for freehand objects), as well as LinkFormat and OLEFormat (for OLE objects).

Pointer  

Note that the Shape objects are defined in the Excel library, but the associated constants in the Office library.When old Excel 5/7 files are opened, the Office library is not activated under normal circumstances. This must be accomplished with ToolsReferences .

Example

The drawing objects in Figure 10-8 were created with the loop in btnShowAllAutoShapes_Click . And now a word about the syntax of AddShape : The first parameter specifies the autoshape type (1 through 37), while the following four parameters determine the location ( Left/Top ) and size ( Width/Height ) of the object. The coordinate system begins in the upper left-hand corner of the worksheet.

 ' Shapes.xls, Sheet1 Private Sub  btnShowAllAutoShapes_Click  ()   Dim i&   For i = 0 To 136     ActiveSheet.Shapes.AddShape i + 1, _       40 + 50 * (i Mod 12), 50 + 50 * (i \ 12), 40, 40   Next End Sub 

Figure 10-8: Some of the predefined autoshapes

To delete the drawing objects the following procedure can be used. The crucial step is the Type test: Without it the buttons in the worksheet would be deleted as well!

 Private Sub  btnDeleteShapes_Click  ()   Dim s As Shape   For Each s In ActiveSheet.Shapes     If s.Type = msoAutoShape Or s.Type = msoLine Then s.Delete   Next End Sub 

The procedure btnStar_Click draws a star made up of colored arrows (Figure 10-9). Note that arrows are not among the autoshapes, but form their own category of Shape . For this reason AddLine must be used instead of AddShape . ForeColor refers to a ColorFormat object, with which the color of an object can be set.

Figure 10-9: A star of colored arrows

Pointer  

The program code may lead you to believe that Excel offers infinitely many colors for your use. Unfortunately, that is not the case. Rather, there is available a palette of only 56 colors (apparently a relic of earlier versions of Excel). Therefore, a reference to an RGB color means only that the closest matching color from this palette is used.

 Private Sub  btnStar_Click  ()   Dim degree#   Dim s As Shape   Const Pi = 3.1415927   Randomize   For degree = 0 To 2 * Pi Step Pi / 12     Set s = ActiveSheet.Shapes.AddLine(200, 200, _       200 + 100 * Sin(degree), 200 + 100 * Cos(degree))     s.Line.EndArrowheadStyle = msoArrowheadTriangle     s.Line.EndArrowheadLength = msoArrowheadLengthMedium     s.Line.EndArrowheadWidth = msoArrowheadWidthMedium     s.Line.ForeColor.RGB = RGB(Rnd * 255, Rnd * 255, Rnd * 255)   Next End Sub