Drawing in Excel 5 – Shape List

Previous Post

For some reason best known to Microsoft, the Excel documentation does not include a graphic list of shapes with an example of each shape, and the text list they do provide is incomplete. 

The file shapelist.zip

rectifies that omission with an automatically generated table of shapes from number 1 through to 183.

It also includes examples of how arcs (shape no. 25) work in Excel 2007 and earlier versions.

The size and position of arcs are defined with the usual width, height, left, and top properties, but note that the width and height are equal to the arc radius, and the left coordinate is equal to the X value of the arc centre.  The start and end points of the arc are defined with Adjustment(1) and Adjustment(2), and the shape thus defined may be rotated with the Rotation property.  Unfortunately there are significant differences in the way in which these properties are applied in Excel 2007 and earlier versions.

Assuming  an XY coordinate system, with origin at the centre of the arc:

Adjustment(1)  defines the angle of the start of the arc, measured from the X axis.  In Excel 2007 clockwise angles are positive, in earlier versions anti-clockwise is positive.

In both versions an Adjustment(1) value of zero defaults to a starting point on the Y axis, i.e. to -90 degrees in Excel 2007 and 90 degrees in earlier versions.

Adjustment(2) defines the angle of the end of the arc, measured from the X axis, with the same sign convention as adjustment(1).  An adjustment(2) value of zero defines an end point on the X axis, as would be expected, zero values for both adjustments therefore defines an arc between the Y and X axes.

The rotate property rotates the defined shape, with a clockwise rotation being positive in all Excel versions.

In Excel 2007 the shape is rotated about the origin, i.e. the centre of curvature of the arc.  In earlier versions the rotation appears to be about the mid-point of a radial line to the centre of the arc.

For compatibility between versions it seems best to avoid using the Rotation property, and adjust the sign of the adjustment angles according to the version number.

Code to generate an arc and a circle, using values specified in a spreadsheet range, is shown below.  The code checks for the Excel version, and for versions earlier than 2007 (version 12.0) reverses the sign of the arc adjustments, so that the arcs drawn are consistent across versions.

Sub Drawshape()

Dim shapeA As Variant, sType As MsoAutoShapeType, sLeft As Single, s_Top As Single, sWidth As Single

Dim sHeight As Single, sRotn As Single, sAdj1 As Single, sAdj2 As Single

Dim shp As Shape, i As Long

For Each shp In ActiveSheet.Shapes


Next shp

shapeA = Range("shapetab").Value2

i = 1

Do While shapeA(1, i) <> 0

sType = shapeA(1, i)

sLeft = shapeA(2, i)

s_Top = shapeA(3, i)

sWidth = shapeA(4, i)

sHeight = shapeA(5, i)

sRotn = shapeA(6, i)

sAdj1 = shapeA(7, i)

sAdj2 = shapeA(8, i)

If Application.Version < 12 Then

sAdj1 = -sAdj1

sAdj2 = -sAdj2

End If

With ActiveSheet.Shapes

Set shp = .AddShape(sType, sLeft, s_Top, sWidth, sHeight)

End With

With shp

.Rotation = sRotn

If sAdj1 <> 0 Then .Adjustments(1) = sAdj1

If sAdj1 <> 0 Then .Adjustments(2) = sAdj2

.Fill.Visible = msoFalse

End With

i = i + 1


End Sub
An example of the shapes generated by this code is shown below:

Note that “Width” and “Height” values of the circle are double that of the arc, and the “Left” value of the circle is 105 less than that of the arc.


Use of the “rotation” property in Excel 2007 and earlier versions is illustrated below:




Note that the input for these two screen shots was identical.

This entry was posted in Drawing, Excel, VBA and tagged , , , . Bookmark the permalink.

4 Responses to Drawing in Excel 5 – Shape List

  1. Pingback: Drawing in Excel 6 - getting shape properties « Newton Excel Bach, not (just) an Excel Blog

  2. Pingback: Do Not Text List | AllGraphicsOnline.com

  3. Tom Barsh says:

    Holy Smokes! I never knew we could draw those arcs, especially arrays of arcs, programmatically.

    I used to tinker around (considerably) with programmatically drawing in Excel. Drawing ASME pressure vessels, beams, columns, etc. I was always annoyed by difficulty in drawing half of an ellipse. I guess I didn’t proceed far enough.

    Now I still draw in Excel, but mostly free-hand, making illustrative sketches to go on calculation template workbooks.

    Awesome blog!


  4. Pingback: Daily Download 8: Drawing in Excel | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s