How to create autoshapes, lines and connectors in VBA macros
Part two 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 (this blog)
  3. Naming, referring to and positioning shapes
  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.

Working with shapes - getting started

Any worksheet contains a collection of shapes, so often a good place to start is by deleting any shapes that you've already added to a worksheet so that you can start with a blank canvas.

Looping over shapes

The following macro would delete any shapes which have been added to a worksheet:

Sub DeleteShapesOnSheet()

'delete any previous shapes added

Dim w As Worksheet

Dim s As Shape

'refer to a given worksheet

Set w = ActiveSheet

'delete all of the shapes on it

For Each s In w.Shapes

s.Delete

Next s

End Sub

The macro works by looking at each of the shapes on the worksheet in turn, applying the Delete method to remove it.

Adding new shapes

The easiest way to add a shape in VBA is to apply the AddShape method to the existing collection of shapes:

Adding a shape in VBA

Some of the arguments to the AddShape method (the full list is shown below).

The full list of arguments that you need to specify when adding a shape like this are as follows:

No. Argument Type Notes
1 Type Integer or enumeration The shape that you're adding (see below for more on this).
2 Left Single The position of the shape from the left edge of the worksheet.
3 Top Single The position of the shape from the top edge of the worksheet.
4 Width Single The width of the shape.
5 Height Single The height of the shape.

All units are in points, which is the typical unit for font size.  When you read a book or magazine article, the font size is probably between 10 and 14 points high.

You can add a shape either by specifying its enumeration or by using the integer equivalent.  So both of these commands will add the same rectangle:

'get a reference to a worksheet

Dim w As Worksheet

Set w = ActiveSheet

'add two rectangles, side by side

w.Shapes.AddShape msoShapeRectangle, 10, 10, 30, 20

w.Shapes.AddShape 1, 50, 10, 30, 20

Here are the shapes added by this code:

Two rectangles side by side

The only difference is that one shape is added 10 points in from the left-hand side, and one is added 50 points in from the left.

 

Whether you choose to specify the shape type by its number or by its enumeration is up to you!

Listing out the types of shapes

A list of the first 137 autoshape types is shown below (for versions of Excel up to 2003, that's all that there is available):

First 137 autoshapes

The main autoshapes in VBA!

Generating the list of shapes

It's neither particularly well-written or well-commented, but for the sake of completion (and in case anyone finds it useful for reference), here's the code I wrote to generate the above list:

Sub ListShapes()

Dim ws As Worksheet

Dim s As Shape

Dim c As Range

'dimensions of shape

Dim l As Single

Dim t As Single

Dim w As Single

Dim h As Single

'delete any previous shapes added

Set ws = ActiveSheet

For Each s In ws.Shapes

s.Delete

Next s

'get rid of any old contents

Cells.Clear

'put titles in across top

Dim col As Integer

Dim topcell As Range

For col = 1 To 5

'add text at top of columns

Set topcell = Cells(1, 3 * col - 2)

topcell.Value = "No."

topcell.Offset(0, 1).Value = "Shape"

'set column widths

topcell.EntireColumn.ColumnWidth = 5

topcell.Offset(0, 1).ColumnWidth = 9

'add separator column

topcell.Offset(0, 2).ColumnWidth = 2

Next col

'format titles

With Range("A1:N1")

.Font.Bold = True

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.Interior.Color = RGB(220, 220, 220)

End With

'now add shapes

Dim rowNumber As Integer

Dim colNumber As Integer

Dim shapeNumber As Integer

colNumber = 1

rowNumber = 1

For shapeNumber = 1 To 137

'(when go above 35, start new column)

rowNumber = rowNumber + 1

If rowNumber > 29 Then

rowNumber = 2

colNumber = colNumber + 3

End If

'put shape number in left cell

Set c = Cells(rowNumber, colNumber)

c.Value = shapeNumber

'position shape in right column

l = c.Offset(0, 1).Left + 10

t = c.Offset(0, 1).Top + 5

w = 35

h = 12

Set s = ws.Shapes.AddShape(shapeNumber, l, t, w, h)

'format the number

c.HorizontalAlignment = xlCenter

c.VerticalAlignment = xlCenter

c.RowHeight = 20

'add separator to right

If colNumber < 13="">Then

c.Offset(0, 2).Interior.Color = RGB(220, 220, 220)

End If

Next shapeNumber

MsgBox "Done!"

End Sub

If you're new to shapes, you'll need to read on in this blog to learn more about how they work to understand the above code!

 

Having learnt the basics of adding shapes, let's now look at how to name and position them.

This blog has 0 threads Add post