BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
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
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.