You are watching: What is an embedded chart in excel
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:
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:
(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
.SetSourceData Source:=Sheets("Sheet1").Range("B2:B11") .ChartType = xlXYScatterLines
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:
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:
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.)
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
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.
See more: Pizarro & The Conquistador Who Conquered And Looted The Inca Empire In 1531 Was
In the next section, you"ll learn how to add a chart to a user form.