How to create autoshapes, lines and connectors in VBA macros
Part three of an eight-part series of blogs

You can use Visual Basic within Excel, PowerPoint or Word to draw shapes, format them and even assign macros to run - this long blog gives lots of ideas of how to proceed!

  1. Working with shapes in VBA
  2. Working with shapes - getting started
  3. Naming, referring to and positioning shapes (this blog)
  4. Formatting shapes and adding text
  5. Adding lines and connectors to join shapes together
  6. Working with multiple shapes
  7. Assigning macros to shapes
  8. Exercises on shapes

This is one small part of our free online Excel VBA tutorial.  To find out how to learn in a more structured way, have a look at our training courses in VBA.

Posted by Andy Brown on 25 January 2014

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Naming, referring to and positioning shapes

Once you've added a shape, you'll want to be able to refer to it in the future - and you'll also need to know how to position it exactly where you want on the screen.

Adding a shape without getting a reference to it

Consider the following block of code, which adds a square to the current worksheet:

Sub AddSquare()

Dim ws As Worksheet

'add a square

Set ws = ActiveSheet

ws.Shapes.AddShape 1, 50, 50, 100, 100

End Sub

The problem with this method is that there's no easy way to refer to the shape after adding it.  If it's the only shape added to date, this would work:

'move the shape just added

ws.Shapes(1).Left = 200

However, referring to a shape by its index number within the collection of shapes clearly isn't a reliable method. 

Adding a shape while at the same time getting a reference to it

A much better way to add a shape is by setting an object variable to refer to it immediately after adding it:

Sub AddSquare()

Dim ws As Worksheet

Dim sq As Shape

'add a square

Set ws = ActiveSheet

Set sq = ws.Shapes.AddShape(1, 50, 50, 100, 100)

'give this shape a unique name

sq.Name = "WOL_0001"

End Sub

Note that (as always in VBA) if you're capturing a reference to something,
you need to include brackets round the argument list.  The advantage of the above approach is that you can now refer to the shape that's been added either by the object variable used:

'can then refer to this by variable now ...

sq.Left = 200

Or by its name:

'... or by name later

ws.Shapes("WOL_0001").Left = 300

Shape names are a bit strange in VBA, and are not necessarily unique.  Rather than trying to understand the rules, the simplest thing is to generate and assign unique names yourself for shapes that you've created programmatically.

Positioning shapes relative to cells

You've already seen that when you position a shape you do it relative to the top left corner of a worksheet.  However, it's easy enough to change this to position a shape relative to a cell:

Shape position

To position a shape in the shaded cell, we just need to know the two distances shown.


For example, supposing that we want to add a shape within the cell like this:


Shape within cell

The shape is half the width of the cell, and half its height.


Here's some sample code to do this:

Sub AddShapeToCell()

Dim c As Range

Dim ws As Worksheet

Set ws = ActiveSheet

Set c = Range("B4")

'add shape to cell, centre-aligned vertically and horizontally

ws.Shapes.AddShape Type:=msoShape10pointStar, _

Left:=c.Left + (c.Width / 4), _

Top:=c.Top + (c.Height / 4), _

Width:=c.Width / 2, _

Height:=c.Height / 2

End Sub

This code will position the shape relative to the top left corner of the cell, rather than of the worksheet.

One thing to watch is that the units for column widths/row heights aren't the same as for shapes!

Now that we know how to refer to our shapes (and how to position them where we want on screen), it's time to make them look pretty!

This blog has 0 threads Add post