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)
.property = PropertySetting
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.:
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:
AddPolyline (with one segment)
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.
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