Drawing in Excel-4

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)


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:

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.

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

3 Responses to Drawing in Excel-4

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

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

  3. 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s