This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! Working with shapes in VBA Working with shapes - getting started (this blog) Naming, referring to and positioning shapes Formatting shapes and adding text Adding lines and connectors to join shapes together Working with multiple shapes Assigning macros to shapes 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.

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!

Working with shapes in VBA
Working with shapes - getting started (this blog)
Naming, referring to and positioning shapes
Formatting shapes and adding text
Adding lines and connectors to join shapes together
Working with multiple shapes
Assigning macros to shapes
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

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

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:

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:

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):

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

'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.

Parts of this blog
Working with shapes in VBA Working with shapes - getting started (this blog) Naming, referring to and positioning shapes Formatting shapes and adding text Adding lines and connectors to join shapes together Working with multiple shapes Assigning macros to shapes Exercises on shapes

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR