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 shp.Delete 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 Loop ```

`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.