562 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 three 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.
|
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:
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.
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.
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!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
From: | Marcv |
When: | 11 Feb 21 at 22:38 |
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.
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.