This post will look in a bit more detail at the methods presented in the previous post. It would be a good idea to open the previous post on another tab, or download the example file and open the code in the Visual Basic editor.

For each of the shapes in the example code the shape was added to the “shape collection” (and hence displayed on the active worksheet) with a command of the form, either:

set VariableName = ActiveSheet.Shapes.addShape(parameters)

or

with ActiveSheet.Shapes.addShape(parameters)

.property = PropertySetting

end with

or the similar:

ActiveSheet.Shapes.addShape(parameters).property = PropertySetting

For two of the methods used in the example (AddCurve and AddPolyLine) a different form is allowed; e.g.:

ActiveSheet.Shapes.AddCurve (PointArray)

will add the curve specified by PointArray to the shapes collection. Note the space between AddCurve and the parameters. The editor will add the space if necessary for those methods where this form is acceptable, otherwise it will tell you that it is expecting a “=”, and one of the forms described earlier must be used.

I have no idea what is the logic (or if there is any logic) behind the way the different methods are handled, but it seems to me best to stick with command forms that will work for all the shape methods.

It can be seen that there are a variety of methods to create simple shapes, for instance a straight line can be created using:

AddLine

AddPolyline (with one segment)

AddShape

ConverttoShape

or even AddCurve

Similarly the dodecahedron can be created with AddPolyline, ConverttoShape or AddShape, but using AddCurve with the same coordinates produces a different shape.

In general I will be using AddLine or AddPolyLine in most cases, with AddShape being used for arcs and circles (or ellipses) or if a regular polygon is required.

The final point concerns the naming of shapes. Each of the shapes except the dodoecahedron created with the AddPolyline method have been given a name at the time of their creation. Shapes not explicitly named are given a name consisting of a description followed by a sequence number, such as FreeForm44. Since the sequence number will increment as shapes are created and deleted it is impossible to know what any given shapes allocated name will be, so if you will need to select a shape using VBA code at any time the shape should be named explicitly at the time of its creation.

### Like this:

Like Loading...

*Related*

Pingback: Drawing in Excel-3 « Newton Excel Bach, not (just) an Excel Blog

Pingback: Drawing in Excel 5 - Shape List « Newton Excel Bach, not (just) an Excel Blog

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