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
- Naming, referring to and positioning shapes
- Formatting shapes and adding text
- Adding lines and connectors to join shapes together
- Working with multiple shapes (this blog)
- 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 multiple shapes
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.
Using the ShapeRange object
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:
Sub AddCircles()
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
ws.Shapes.SelectAll
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)
End Sub
You can abbreviate this as follows:
'now format them both
ws.Shapes.SelectAll
'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.
Looping over a collection of shapes
It's just so much easier (I humbly submit) to loop over all of the shapes, colouring each in turn:
Sub AddCircles()
Dim ws As Worksheet
Dim c1 As Shape
Dim c2 As Shape
Dim s 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)
Next s
End Sub
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!
Checking the type of shapes
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:
Sub FormatOnlyCircles()
Dim ws As Worksheet
Dim c1 As Shape
Dim c2 As Shape
Dim r1 As Shape
Dim s 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)
End If
End If
Next s
End Sub
Although this blog 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.