For a long time I've always shied away from this subject, but it turns out that I shouldn't have: shapes in VBA are surprisingly easy to create and control!
The code to create these two shapes is included later on in this tutorial.
This tutorial goes into the minutiae of how to create autoshapes in VBA. All of the examples are for Excel, but they'll work just as well for PowerPoint VBA or Word VBA.
First, however, a tip to make your shape-life easier (it sounds like something out of a bad science fiction novel . ).
For some reason, VBA doesn't support autocompletion that well when you're working with worksheets. For example, as you type in the following lines of code you won't see any Intellisense to help you:
'neither of the following autocompletes
Worksheets(1).Shapes.AddShape 1, 1, 1, 1, 1
ActiveSheet.Shapes.AddShape 1, 1, 1, 1, 1
Websites will tell you that this is because Excel doesn't know whether you're working with a worksheet or a chart, but I can't see how this can be true for the first line of code above. Whatever .
By contrast, if you use an object variable to refer to a worksheet life will be MUCH easier:
If you use a variable of type Worksheet, VBA knows what you're talking about and can help you.
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. The following macro would delete any shapes which have been added to a worksheet:
'delete any previous shapes added
Dim w As Worksheet
'refer to a given worksheet
Set w = ActiveSheet
'delete all of the shapes on it
For Each s In w.Shapes
The macro works by looking at each of the shapes on the worksheet in turn, applying the Delete method to remove it.
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:
Type
Integer or enumeration
The shape that you're adding (see below for more on this).
Left
The position of th e shape from the left edge of the worksheet.
Top
The position of th e shape from the top edge of the worksheet.
Width
The width of t he shape.
Height
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!
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!
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:
Dim ws As Worksheet
'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
'get rid of any old contents
'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.Offset(0, 1).Value = "Shape"
'set column widths
topcell.Offset(0, 1).ColumnWidth = 9
'add separator column
topcell.Offset(0, 2).ColumnWidth = 2
.Interior.Color = RGB(220, 220, 220)
Dim rowNumber As Integer
Dim colNumber As Integer
Dim shapeNumber As Integer
For shapeNumber = 1 To 137
'(when go above 35, start new column)
rowNumber = rowNumber + 1
If rowNumber > 29 Then
colNumber = colNumber + 3
'put shape number in left cell
Set c = Cells(rowNumber, colNumber)
'position shape in right column
l = c.Offset(0, 1).Left + 10
t = c.Offset(0, 1).Top + 5
Set s = ws.Shapes.AddShape(shapeNumber, l, t, w, h)
'format the number
'add separator to right
c.Offset(0, 2).Interior.Color = RGB(220, 220, 220)
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. Consider the following block of code, which adds a square to the current worksheet:
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Shapes.AddShape 1, 50, 50, 100, 100
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
However, referring to a shape by its index number within the collection of shapes clearly isn't a reliable method.
A much better way to add a shape is by setting an object variable to refer to it immediately after adding it:
Dim ws As Worksheet
Dim sq As Shape
Set ws = ActiveSheet
Set sq = ws.Shapes.AddShape(1, 50, 50, 100, 100)
'give this shape a unique name
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 .
'. or by name later
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.
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:
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:
The shape is half the width of the cell, and half its height.
Here's some sample code to do this:
Dim ws As Worksheet
Set ws = ActiveSheet
'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, _
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!
Formatting shapes is easy provided that you understand that there are two main properties that you can play about with:
What it allows you to change
Fill
The background colour or pattern for a shape
Line
The border line for a shape
One other oddity: it's the ForeColor of a shape's fill that you'll usually want to change:
It's the foreground colour of a shape's fill pattern that'll usually be of most interest.
Here's some code to create a pretty circle, for example:
Set s = ws.Shapes.AddShape(9, 10, 10, 50, 50)
'format this circle
'give the shape a pinkish colour
.ForeColor.RGB = RGB(255, 240, 255)
'transparency goes from 0 (opaque) to 1
This would create a circle which is 20% transparent and has a different background colour:
The circle created by the above code.
You could extend the macro above to change the border colour and style of the shape:
'now make the border purple and dotted
.ForeColor.RGB = RGB(100, 0, 100)
The circle is now beginning to look a bit silly:
Perhaps our choices weren't so good after all .
To do detailed formatting of shapes, a good way forward is to record a macro and then play about with the code generated.
Any shape added has a TextFrame property, which gives you access to the text within it. This in turn has a collection of Characters!
'add and format text in call-out
CalloutBalloon.TextFrame.Characters.Text = "Wise Owl blog on shapes"
Thus the code above sets the text in the relevant shape called CalloutBalloon, then changes its font and alignment. You can format only part of the text in a shape, as this example shows:
Here we've forwarded only the characters from 6 to 9.
Here's some code to achieve the above:
Dim ws As Worksheet
Set ws = ActiveSheet
Set s = ws.Shapes.AddShape(9, 10, 10, 140, 30)
'make it nearly white
s.Fill.ForeColor.RGB = RGB(245, 245, 255)
'show text within it
s.TextFrame.Characters.Text = "Wise Owl training"
With s.TextFrame.Characters(6, 3)
'colour the owl differently
.Font.Color = RGB(100, 200, 0)
This changes the colour of the 3 characters from position 6 onwards.
Note that this is one of those occasions when (to keep you on your toes) VBA numbers things in a collection from 1, not 0.
Here's the example from the beginning of this tutorial:
As promised, below is the code to create these two shapes!
The code to produce it could be as follows:
Dim w As Worksheet
'delete any previous shapes added
Dim w As Worksheet
'refer to a given worksheet
Set w = ActiveSheet
'delete all of the shapes on it
For Each s In w.Shapes
'the call-out containing speech text
Dim CalloutBalloon As Shape
'the Wise Owl logo
Dim Logo As Shape
'set reference to a worksheet
Set w = ActiveSheet
'delete any shapes added, to start with blank sheet
'add the callout balloon
Set CalloutBalloon = w.Shapes.AddShape(108, 50, 10, 150, 120)
'format it to look better: fill and border colours
CalloutBalloon.Fill.ForeColor.RGB = RGB(227, 214, 213)
CalloutBalloon.Line.ForeColor.RGB = RGB(0, 0, 0)
'add and format text in call-out
CalloutBalloon.TextFrame.Characters.Text = "Wise Owl blog on shapes"
'now add the owl logo (initially a rectangle)
Set Logo = w.Shapes.AddShape(1, 30, 160, 80, 80)
Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"
Running the CreateWiseOwlBalloon routine should create the two shapes shown (although you'll need to substitute your own picture file path to get it to work).
Time now to look at some specialist shapes: lines and connectors.
This tutorial doesn't claim to be exhaustive - there are a LOT of things you can do with shapes:
Just some of the things you could experiment with!
To add a line, specify its start and end point (logical, really):
The arguments when adding a line.
For example, suppose that you wanted to add this red line:
This line starts at (30,10) and ends at (100,50).
Here's the code to add the line above:
Dim ws As Worksheet
Set ws = ActiveSheet
Set s = ws.Shapes.AddLine(30, 10, 100, 50)
s.Line.ForeColor.RGB = RGB(255, 0, 0)
When adding connectors, it helps to know that there are 3 types:
As you move each owl around, the connector will stay . connected to it!
To add a connector:
To draw the connector on the left above (the curved one), first create the two shapes (the textbox and the owl logo):
'text box and owl logo, plus connector
Dim rect As Shape
Dim Logo As Shape
Dim conn As Shape
'set reference to a worksheet
Set w = ActiveSheet
'add the text box
Set rect = w.Shapes.AddShape(1, 10, 10, 80, 20)
rect.Fill.ForeColor.RGB = RGB(227, 214, 213)
'now add the owl logo (initially a rectangle)
Set Logo = w.Shapes.AddShape(1, 30, 80, 50, 50)
Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"
You'll need to use a different picture, of course, if you want to reproduce this. Next, add the connector:
'add the connector linking together (doesn't matter where)
Set conn = w.Shapes.AddConnector(msoConnectorCurve, 1, 1, 1, 1)
Note that it's not worth thinking about its position or size, as when you reroute it VBA will redraw it. Now say what the connector is connecting!
conn.ConnectorFormat.BeginConnect rect, 1
conn.ConnectorFormat.EndConnect Logo, 1
The second argument above specifies whether you're connecting the top, left, right or bottom of the given shape, but it doesn't seem to make much difference what value you use.
Finally, you should update the position of the connector:
'redraw the connector
The cool thing now is that as you drag either shape around the connector will follow it!
There are two main ways to work with a number of shapes at the same time: by using the ShapeRange object, or by looping over a collection of shapes.
Most Microsoft examples use the first method (the ShapeRange object); I find the second much easier to work with, however. Examples of both are shown below.
You can use an object of type ShapeRange to get access to a set of shapes, allowing you to set properties and apply methods to a set of shapes simultaneously. Here's an example of its use:
We want to colour these circles pink, and give them a red border.
One way to do this is to draw the shapes, then select them:
Dim ws As Worksheet
Dim c1 As Shape
Dim c2 As Shape
Set ws = ActiveSheet
'add two circles
Set c1 = ws.Shapes.AddShape(msoShapeOval, 10, 10, 50, 50)
Set c2 = ws.Shapes.AddShape(msoShapeOval, 70, 10, 50, 50)
'now format them both
Now that the shapes are selected, we can apply the ShapeRange method to the current selection to return a set of shapes (I did warn you that I preferred the other method!):
'get a reference to this set of shapes
Dim sr As ShapeRange
Set sr = Selection.ShapeRange
'colour these shapes pink with red border
sr.Fill.ForeColor.RGB = RGB(250, 220, 240)
sr.Line.ForeColor.RGB = RGB(250, 100, 150)
You can abbreviate this as follows:
'now format them both
'colour these shapes pink with red border
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(250, 220, 240)
Selection.ShapeRange.Line.ForeColor.RGB = RGB(250, 100, 150)
The problem with this approach is that it doesn't allow for autocompletion:
Commands beginning with Selection don't support Intellisense.
Because of the above limitation, it's easier to create an intermediate ShapeRange variable and use this to refer to a set of shapes.
It's just so much easier (I humbly submit) to loop over all of the shapes, colouring each in turn:
Dim ws As Worksheet
Dim c1 As Shape
Dim c2 As Shape
Set ws = ActiveSheet
'add two circles
Set c1 = ws.Shapes.AddShape(msoShapeOval, 10, 10, 50, 50)
Set c2 = ws.Shapes.AddShape(msoShapeOval, 70, 10, 50, 50)
'format each of these shapes
For Each s In ws.Shapes
s.Fill.ForeColor.RGB = RGB(250, 220, 240)
s.Line.ForeColor.RGB = RGB(250, 100, 150)
I accept that the above code probably runs more slowly, but you would have to have a serious number of shapes on a worksheet for this to matter!
One useful thing to be able to do is to check what type each shape is as you loop over it. You can do this by testing a shape's Type, and then more specifically its AutoShapeType:
We'll write code to colour the ovals, but not the rectangle.
Here's some code to achieve the above:
Dim ws As Worksheet
Dim c1 As Shape
Dim c2 As Shape
Dim r1 As Shape
Set ws = ActiveSheet
'add two circles .
Set c1 = ws.Shapes.AddShape(msoShapeOval, 10, 10, 50, 50)
Set c2 = ws.Shapes.AddShape(msoShapeOval, 70, 10, 50, 50)
'. and a rectangle
Set r1 = ws.Shapes.AddShape(msoShapeRectangle, 10, 70, 110, 50)
'format just the circles
For Each s In ws.Shapes
'first check for autoshapes
If s.Type = msoAutoShape Then
'now check this autoshape is a "circle"
If s.AutoShapeType = msoShapeOval Then
s.Fill.ForeColor.RGB = RGB(250, 220, 240)
s.Line.ForeColor.RGB = RGB(250, 100, 150)
Although this tutorial has concerned itself almost exclusively with autoshapes, there are lots of other shapes that you can add!
Just some of the other shape types that you can add to a workbook!
Having spent all of this time looking at how to create shapes, let's now take a quick look at how to assign macros to them.
Perhaps the most remarkable thing that you can do with shapes is get them to run macros:
We'll arrange it so that when you click on the shape, you'll see a message box appear!
Here's the message that clicking on the owl will show:
The message our macro will show.
To make this work you need to create a macro first, then assign it to a shape.
Here's a modest macro which displays the hoot message on screen (and reads it out for good measure, although it sounds a bit strange!):
'make the owl hoot!
MsgBox "Tu-whit, tu-whoo"
'just in case you missed it, say it out loud
Application.Speech.Speak "Tu-whit, tu-whoo"
The macro you create must be contained in the same workbook as the shape.
To do this, set the shape's OnAction property:
'text box and owl logo, plus connector
Dim Logo As Shape
'set reference to a worksheet
Set w = ActiveSheet
'now add the owl logo (initially a rectangle)
Set Logo = w.Shapes.AddShape(1, 10, 10, 50, 50)
Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"
'assign a macro to it
That's all that you need to do! Clicking on the shape will now run the Hoot macro.
Creating and using classes
