The Chart object can either represent a separate chart sheet or a chart embedded in a worksheet (contained in the ChartObject object). Note that a chart sheet (or Chart) is a type of sheet in Excel (different than the regular worksheet). Then, the Charts collection contains all the chart sheets in a workbook, while the ChartObjects collection contains all the embedded charts on a single worksheet.
How to refer to the Chart object
We refer to all chart sheets in a workbook as follows:
ActiveWorkbook.Charts
or simply
Charts
We refer to a specific chart sheet object by either the chart sheet name or index, while ActiveChart refers to the active chart sheet.
Charts("Chart 1")
Charts(1)
ActiveChart
We refer to an embedded chart in a worksheet through the ChartObject object either using the chart name or index as indicated below.
ActiveSheet.ChartObjects("Chart 1")
Sheets("Sheet1").ChartObjects("Chart 1")
ActiveSheet.ChartObjects(1)
Create or add a new chart
We use the Charts.Add method to create or add a new chart sheet to the workbook (in the same way we add worksheets – see Worksheet object). Excel automatically gives a default name to the new chart sheet (Chart1, Chart2, etc), but that can easily be changed later.
Charts.Add
But that’s just an empty chart sheet, so we need to specify the type of chart and data source to complete the chart. In the example below, we refer to the active chart sheet to change the name to MyChartSheet, define the chart type as a clustered column chat, and display the source data, which is contained in range A2:A5 of the worksheet “data”.
To add a new chart embedded into a worksheet we have to use the ChartObjects.Add method instead. In this case, the structure works as with the Shape object, and requires the worksheet reference first, and the dimensions of the chart as attributes. The below example adds a new chart with given position/dimensions (left, top, width, height) to the active worksheet.
ActiveSheet.ChartObjects.Add 50, 50, 400, 200
In order to work with the embedded chart, we need to assign it to a ChartObject variable (MyChart in the example below). Then we can change the name, type of chart, and display source data. Note that we use the Chart property of the ChartObject object to work with the embedded chart. The code below adds a clustered column chart embedded in the worksheet “data”, and displays the data (as previously with the chart sheet).
The ChartType property accepts numerous types of charts including column, line, pie, and scatter charts, and different combinations of those and many other. Find here the full list chart types as per xlChartType enumeration.
In the next section we will see how to set or change chart properties. However, there is yet another useful method of the Chart object worth mentioning. ChartWizard is used to quickly format a chart without setting all the individual properties. The syntax is as follows:
Chart.ChartWizard (Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)
- Source is the range that contains the source data
- Gallery is the type of chart (xlChartType)
- Format is the option number for the built-in autoformats (a number from 1 through 10, depending on the gallery type)
- PlotBy specifies whether the data for each series is in rows (xlRows) or columns (xlColumns)
- CategoryLabels is the number of rows or columns within the source range that contain category labels
- SeriesLabels is the number of rows or columns within the source range that contain series labels
- HasLegend (True or False)
- Title is the chart title (none if omitted)
- CategoryTitle is the category axis title text
- ValueTitle is the value axis title text
- ExtraTitle is the series axis title for 3D charts or the second value axis title for 2D charts
The following code adds a similar chart to the one shown earlier, with the same data source, chart type, and specifically hides the legend and adds a chart title, all using the Chart.ChartWizard method.
Formatting charts
Again, we have to distinguished between chart sheets and embedded charts. We can change the position and dimensions of embedded charts with the Left, Top, Width, and Height properties of the ChartObject object (similar as we have done with the Shape object). But that’s not applicable and kind of irrelevant with a chart sheet, it actually behaves as a worksheet.
We can set many other chart properties available in Excel to both chart sheets and embedded charts. The below example adds a tittle, hides the legend, and adds the axis name to an embedded chart (the code would be slightly different for a chart sheet). Note that we refer to a previously added chart (MyEmbeddedChart) and assign it to a ChartObject variable (MyChart) to work with the embedded chart.
The Chart.SeriesCollection method allows to add and format labels for the data series, as well as changing colour and other formatting properties. Following the previous example, we can add data labels to the series (there is just one series collection) as follows:
We can hide data labels with Chart.SeriesCollection(1).HasDataLabels = False
We can also change the colour of the data series with SeriesCollection. With this type of chart, we can change the interior and border of the data columns with the Fill and Line properties respectively.
Further formatting of the chart area can be achieved through the Chart.ChartArea and Chart.PlotArea properties. The illustration below shows the scope of each of those regions in the embedded chart. The ChartArea is highlighted in greenish blue colour (with its border in red), and the PlotArea in light blue (border also red) as per the code below. The column data series appear in blue with a red border (as per code above).
Select, copy, and delete charts
We can select, copy, or delete a chart with the respective methods referring to either the Chart or ChartObject objects if it is a chart sheet or embedded chart respectively.
ActiveWorkbook.Charts("Chart1").Select 'selects the chart sheet Chart1
while
ActiveSheet.ChartObjects("Chart 1").Select 'selects the embedded chart Chart 1
We can select all chart sheets in a workbook with simply Charts.Select, but we cannot do that with embedded charts. We need to create a ShapeRange object to be able to select several embedded charts at the same time. We have already seen that with the Shape object. The code below creates a ShapeRange array to select two embedded charts in the active worksheet.
ActiveSheet.Shapes.Range(Array("Chart 1", "Chart 2")).Select
If we want to apply certain properties to all embedded charts in a worksheet, we need to loop through all of them with a For Each loop (see Lesson 6: Conditions and Loops in the training for beginners if this is new to you). The code below loops through all charts in the active worksheet and displays the chart name in a message box.
The same applies to other methods such as Copy or Delete. We can copy a chart sheet to a newly created workbook as indicated below (when no specific destination is set).
ActiveWorkbook.Charts("Chart1").Copy
We can also copy, or rather move, a chart sheet into a worksheet, somehow converting the chart sheet into an embedded chart. We do that with the Charts.Location method and setting the Where attribute to xlLocationAsObject. The code below copies the active chart sheet as an embedded chart into the worksheet “MySheet”.
ActiveChart.Location Where:=xlLocationAsObject, Name:="mySheet"
And we can copy/paste an embedded chart from Sheet1 to Sheet2 as follows:
Similarly, we can delete a chart sheet or an embedded chart with the Delete method following the same structure as earlier:
Other VBA Objects
No comments:
Post a Comment