563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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!
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.
|
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:
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.
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!
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:
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.
Parts of this blog |
---|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.