Excel VBA and Embedded Charts

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 in the previous section.

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 (one line of code):

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 (one line of code):

ActiveSheet.Shapes.AddChart(xlXYScatter).Chart

(To add a different type of chart, see the Chart Appendix at the end of the book)

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 (one line of code):

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 (single line):

.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 (single line).

.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 (single line):

MyChart.SeriesCollection.Add Source:=ActiveSheet.Range("A2:A11")

You can add as many data series as you need with the above code.