564 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
|VBA code to create a chart and textbox, and copy them to Word|
|Charts in VBA are a bit weird: this blogs highlights some of their foibles, by showing how charts are really just special shapes, which contain shapes of their own!|
Inspired by a recent course question (thanks, Jen) I thought I'd share my thoughts on how to create a chart, add a textbox to it and copy the whole caboodle to Word - all using VBA.
I don't like liver (who does?), but I wanted to compare this dislike scientifically against my dislike of other devil foods. The macro takes this worksheet:
A worksheet comparing 3 devil foods.
And uses this to create the following chart in Word:
The clever thing is that the textbox at the bottom right is part of the chart, and so is copied with it to Word.
The first thing that you need to do is to create a reference to Word, so that you can copy your Excel chart to Word at the end:
If you just want to create the chart in Excel, you can skip this step. You can see more on referencing other applications in VBA here.
The first thing that the macro does is to get rid of any old charts on the worksheet:
'loop over all shapes, deleting the ones which are charts
Dim s As Shape
For Each s In ActiveSheet.Shapes
If s.Type = msoChart Then
This works because embedded charts on a worksheet are actually shapes - but shapes with a particular charty type!
We can now write code to create our chart:
'make chart variable public
Dim c As Chart
Dim s As Shape
Dim r As Range
Dim tb As Shape
'first delete any old charts
'get the data to be charted
Set r = Range("A1").CurrentRegion
'create the new chart
Set s = ActiveSheet.Shapes.AddChart
Set c = s.Chart
I've made the variable c a public variable here, so that I can use it in more than one procedure.
What this does is to get a reference to the range to be charted (if you want to see what CurrentRegion does, click on cell A1 in the worksheet and press CTRL + A), then:
If you think the above doesn't make much sense, you're not the only one!
The first thing we'll do now is to say what data we're charting:
'set what its charting, and make it a column chart
c.ChartType = xlColumnClustered
'add a title
c.ChartTitle.Text = "Foods compared"
Here we make this a column chart, to chart the block of cells containing the devil foods, and give it a suitable title. Next, we'll give the chart a name so that we can refer to it later:
'give it a name (could also use s.Name = "DevilFoods")
'note that the "parent" is the shape the chart sits on
c.Parent.Name = "DevilFoods"
This is equivalent to typing a name in here:
This shows that the chart has been successfully named.
I then moved the chart so that it "began" at the top left corner of cell C5:
'move the chart to align with the top edge of cell C5
s.Left = Range("C5").Left
s.Top = Range("C5").Top
'make it 4 times wider and 10 times higher than C5
s.Width = Range("C5").Width * 4
s.Height = Range("C5").Height * 10
The chart is four times wider than this cell, and ten times higher:
This diagram shows where the chart will end up.
I know want to add the text box shown above. The critiical thing is to add it into the collection of shapes contained within the chart, rather than as a separate shape on the worksheet.
The reason for this is that when you copy the chart you will also copy (obviously) any shapes that it contains - including your textbox.
Here's the code to add the textbox:
'now we need to add a text box to it at the bottom
Set tb = c.Shapes.AddTextbox( _
Left:=2 * s.Width / 3, _
Top:=4 * s.Height / 5, _
Width:=s.Width / 3, _
Height:=s.Height / 5)
tb.TextFrame.Characters.Text = "Devil"
The width and height are set to make it align with the bottom right corner of the chart. Just for fun, I formatted it a bit:
tb.Fill.BackColor.RGB = RGB(255, 200, 255)
tb.Line.Weight = 1
tb.Line.ForeColor.RGB = RGB(255, 0, 0)
tb.TextFrame.VerticalAlignment = xlVAlignCenter
tb.TextFrame.HorizontalAlignment = xlHAlignCenter
Hopefully it's reasonably obvious what the lines above do.
The final part of the macro calls a separate routine to copy the chart to Word:
'now copy this to Word
Here is that routine (as I mentioned above, you'll need to have referenced Word for this to work):
'copy this chart
'paste in new Word document
Dim wd As New Word.Application
Dim doc As Word.Document
Set doc = wd.Documents.Add
wd.Visible = True
Note that the VBA code copies the chart's parent shape, not the chart itself. And that's it!
25 Aytoun Street