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
How to create autoshapes, lines and connectors in VBA macros
Part five 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 blog doesn't claim to be exhaustive - there are a LOT of things you can do with shapes:
Just some of the things you could experiment with!
Here we'll just look at two additional types of shape: lines and connectors.
To add a line, specify its start and end point (logical, really):
The arguments when adding a line.
For example, suppose that you wanted to add this red line:
This line starts at (30,10) and ends at (100,50).
Here's the code to add the line above:
Dim ws As Worksheet
Dim s As Shape
Set ws = ActiveSheet
Set s = ws.Shapes.AddLine(30, 10, 100, 50)
s.Line.ForeColor.RGB = RGB(255, 0, 0)
When adding connectors, it helps to know that there are 3 types:
As you move each owl around, the connector will stay ... connected to it!
To add a connector:
To draw the connector on the left above (the curved one), first create the two shapes (the textbox and the owl logo):
'text box and owl logo, plus connector
Dim rect As Shape
Dim Logo As Shape
Dim conn As Shape
'set reference to a worksheet
Set w = ActiveSheet
'add the text box
Set rect = w.Shapes.AddShape(1, 10, 10, 80, 20)
rect.TextFrame.Characters.Text = "Curve"
rect.Fill.ForeColor.RGB = RGB(227, 214, 213)
rect.TextFrame.Characters.Font.ColorIndex = 1
'now add the owl logo (initially a rectangle)
Set Logo = w.Shapes.AddShape(1, 30, 80, 50, 50)
Logo.Fill.UserPicture "C:\ajb files\wise-owl-logo.jpg"
You'll need to use a different picture, of course, if you want to reproduce this. Next, add the connector:
'add the connector linking together (doesn't matter where)
Set conn = w.Shapes.AddConnector(msoConnectorCurve, 1, 1, 1, 1)
Note that it's not worth thinking about its position or size, as when you reroute it VBA will redraw it. Now say what the connector is connecting!
conn.ConnectorFormat.BeginConnect rect, 1
conn.ConnectorFormat.EndConnect Logo, 1
The second argument above specifies whether you're connecting the top, left, right or bottom of the given shape, but it doesn't seem to make much difference what value you use.
Finally, you should update the position of the connector:
'redraw the connector
The cool thing now is that as you drag either shape around the connector will follow it!
|Parts of this blog|
25 Aytoun Street