1.8 Resize
Home and Learn - Free Excel VBA Course
11.2 Excel VBA and Embedded Charts
This tutorial follows on from the previous one on Chart Sheets
An embedded chart is one that is on the same spreadsheet as your data, rather than in a sheet all of its own. In this lesson, you'll see how to add an XY Scatter chart. We'll use the same data as for the Chart Sheet.
Create a new Sub in your coding window. Call it EmbeddedChartExample.
For embedded sheets, you need a slightly different reference. For a Chart Sheet, the referencing is like this:
Application.Workbook.Chart
Because an embedded chart goes onto a worksheet, you need a reference to the Worksheet object:
Application.Workbook.Worksheet.ChartObject
Notice that an embedded sheet is a ChartObject rather than just a chart. When you add embedded charts, you are adding them to a collection of Chart Objects. Chart sheets don't need to get added to a collection.
Another complication is that embedded sheets are now shapes (Chart Objects are a special kind of shape). So rather than having a Chart Object collection, embedded charts get added to the shapes collection.
You start as before though, by setting up a Chart type:
Dim MyEmbeddedChart As Chart
However, examine this line of code to add an embedded chart:
Set MyEmbeddedChart = ActiveSheet.Shapes.AddChart.Chart
Again, we use Set to set up an object, this time called MyEmbeddedChart. Notice what comes after the equals sign, though:
ActiveSheet.Shapes.AddChart.Chart
We start by referencing a worksheet, which can be the ActiveSheet. Next, we need the Shapes collection. After Shapes, we use the method AddChart. You can have round brackets after AddChart. Between the round brackets, you can specify the type of chart you want to add. Like this:
ActiveSheet.Shapes.AddChart(xlXYScatter).Chart
(To add a different type of chart, see the referencing section here:
VBA Chart Types and their Constants
We'll use a With Statement to add a chart type.)
You can also specify a location and size for your chart between the round brackets of AddChart:
AddChart(Left:=200, Width:=300, Top:=50, Height:=300)
All these five parameters are optional, however.
If you miss out the round brackets, Excel gives you the default chart, which is usually a column chart. If you want to choose a chart type, you can add it on a new line:
MyEmbeddedChart.ChartType = xlXYScatter
Or use a With Statement, as we're going to do now. Add the following code for you Sub:
Dim MyEmbeddedChart As Chart
Set MyEmbeddedChart = ActiveSheet.Shapes.AddChart.Chart
With MyEmbeddedChart
.SetSourceData Source:=Sheets("Sheet1").Range("B2:B11")
.ChartType = xlXYScatterLines
End With
Your coding window should look like this:
This is more or less the same as you did for a Chart Sheet. This time, the ChartType is xlXYScatterLines. An XY Scatter Line chart, by the way, is one that looks like this:
You could run your code right now. But let's add some formatting, first.
You can add formatting for the data points (the round dots above), and for the lines joining the dots. The dots and the lines are all part of something called a SeriesCollection. In the chart above, you can see we have only one series - Series 1. You can have more than one series, and they can be formatted separately. To do so, you add an index number between round brackets:
SeriesCollection(1)
SeriesCollection(2)
SeriesCollection(3)
Etc
After a dot, you then type the formatting you need. The series dots are called Markers. To set a background colour for your markers, the code is this:
.SeriesCollection(1).MarkerBackgroundColor = RGB(255, 255, 255)
So the properties is MarkerBackgroundColor. After an equal sign, you type a colour. Here, we're using an RGB colour value. If you're meeting these for the first time then the three numbers between the round brackets are for Red, Green, and Blue values. The numbers go from 0 to 255. Values of 255 in all three positions means white, and a 0 in all three position is black. For our background colour, we went for white. You can have a mix of colours by doing something like this:
RGB(14, 125, 66)
As well as setting a colour for your markers, you can set a style and a size. The size is just a number:
. SeriesCollection(1).MarkerSize = 7
The style can any one of 12 values. To get round markers you need xlMarkerStyleCircle.
.SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle
Here are all 12 marker styles. (Play around with them later.)
xlMarkerStyleAutomatic. Automatic markers
xlMarkerStyleCircle. Circular markers
xlMarkerStyleDash. Long bar markers
xlMarkerStyleDiamond. Diamond-shaped markers
xlMarkerStyleDot. Short bar markers
xlMarkerStyleNone. No markers
xlMarkerStylePicture. Picture markers
xlMarkerStylePlus. Square markers with a plus sign
xlMarkerStyleSquare. Square markers
xlMarkerStyleStar. Square markers with an asterisk
xlMarkerStyleTriangle. Triangular markers
xlMarkerStyleX. Square markers with an X
If you want to add a second data series to your chart, you use the Add method of the SeriesCollection. Like this:
MyChart.SeriesCollection.Add Source:=ActiveSheet.Range("A2:A11")
You can add as many data series as you need with the above code.
Formatting the Chart
Two formatting options we can add are for the plot area (the area behind the lines and markers), and the corners of the chart. To set a colour for the plot area, the code is this:
.PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 200)
Again, we have an RGB value after the equal sign. Before the equal sign, we have this:
PlotArea.Format.Fill.ForeColor.RGB
This is self-explanatory: you're formatting the foreground fill colour of the plot area.
To set rounded corners for your chart, the code is this:
ChartArea.RoundedCorners = True
Here's the adapted With Statement. Change your code to match:
You can run your programme, now. Click Run > Run Sub/User Form from the menu at the top of the Visual Basic window, or press F5 on your keyboard. You should see a Scatter Lines chart appear on your spreadsheet. (You can add a button to your spreadsheet, if you want. Then attached the Sub when you see the dialogue box appear.)
Deleting Charts
You can delete all the charts on your spreadsheet quite easily. Because embedded charts are part of a collection (the ChartObjects) collection, you can use a For … Each loop.
Add a new Sub to you coding window. Call it DeleteCharts. Now add the following code for your new sub:
Dim MyCharts As ChartObject
For Each MyCharts In ActiveSheet.ChartObjects
MyCharts.Delete
Next
The first line sets up a variable of type ChartObject. The For … Each loop then accesses each object in the ChartObjects collection. The code for the loop uses the Delete method to delete each object that is in the collection.
And that's it! Run the code, or attach it to a button on your spreadsheet. Any chart you have on the spreadsheet will be deleted.
Exercise
Play around with the code for embedded charts. Try a different MarkerStyle,
change the MarkerSize, and amend the numbers for the RGB colours.
In the next section, you'll learn how to add a chart to a user form.
Next Lesson: 11.3 Charts and Forms >