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 (this blog)
- 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.
Naming, referring to and positioning shapes
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.
Adding a shape without getting a reference to it
Consider the following block of code, which adds a square to the current worksheet:
Sub AddSquare()
Dim ws As Worksheet
'add a square
Set ws = ActiveSheet
ws.Shapes.AddShape 1, 50, 50, 100, 100
End Sub
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
ws.Shapes(1).Left = 200
However, referring to a shape by its index number within the collection of shapes clearly isn't a reliable method.
Adding a shape while at the same time getting a reference to it
A much better way to add a shape is by setting an object variable to refer to it immediately after adding it:
Sub AddSquare()
Dim ws As Worksheet
Dim sq As Shape
'add a square
Set ws = ActiveSheet
Set sq = ws.Shapes.AddShape(1, 50, 50, 100, 100)
'give this shape a unique name
sq.Name = "WOL_0001"
End Sub
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 ...
sq.Left = 200
Or by its name:
'... or by name later
ws.Shapes("WOL_0001").Left = 300
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.
Positioning shapes relative to cells
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:
Sub AddShapeToCell()
Dim c As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set c = Range("B4")
'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, _
Height:=c.Height / 2
End Sub
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!
- Working with shapes in VBA
- Working with shapes - getting started
- Naming, referring to and positioning shapes (this blog)
- 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 post has been very helpful.
I've used the sq.Name = WOL_0001 in my script and it works great for referencing the objects in the code. Once the macro completes is there a command to clear the assigned names from the objects on the sheet so that they can be used again for the next iteration?
My specific code is grouping the objects with user-defined info to be used in a visual layout. When the code is run on the next set of information it works fine until it goes to group the objects; at this point it errors because it's selecting all the already named objects from the first pass, and these are already grouped, causing of the error. The underlying issue is the code is selecting the wrong objects after the first pass. Once they are grouped I have no need for the assigned name reference anymore and need to start the code over.