Manipulating Charts

You can create several different types of charts in Excel, including the common column and pie charts and the not-so-common doughnut and radar charts. Table 9.2 summarizes the more commonly used chart types available in Excel and their function.

Table 9.2 Common Excel Chart Types r

Chart Type

Column

Line

Pie Area

Scatter Bubble

Function

Compares categorized values by charting the data as vertical columns running from 0 to the charted value. There is one column for each value and all columns in the same category have the same color.

The same as a column chart, except that the columns now run in a horizontal direction and are called bars.

Similar to column and bar charts, except that the values are charted as points connected by a line.

Charts each value in a data series as its percent contribution to the whole.

Combines a line chart with a pie chart. Shows the contribution to the whole for several data series over time or categories.

Plots x,y coordinate pairs as a series of points.

Same as a scatter, except that a third variable is included and represented by the size of the data marker.

There are several objects subordinate to the Chart object that represent various components of an Excel chart. The properties and methods of all these objects can be used in your VBA code to alter the appearance and behavior of an Excel chart. Some of the objects that are common to most charts are shown in Figure 9.6 and the Excel application file ChartDemos.xls (found on the book's CD-ROM) contains several examples of manipulating charts using VBA programs. One worksheet from this file (named Chart Type) is shown in Figure 9.7.

AxisTitle object»

A line chart illustrating the components represented by some of the objects in Excel's Chart object model.

AxisTitle object»

A line chart illustrating the components represented by some of the objects in Excel's Chart object model.

ChartTitle object

SeriesCollection object

^^Legend object

Series object ChartArea object

Axis object (xl category)

PlotArea object

PlotArea object

ChartTitle object

SeriesCollection object

^^Legend object

Series object ChartArea object

Axis object (xl category)

The Chart Type worksheet with a column chart.

The Chart Type worksheet with a column chart.

Manipulating Changing The Chart Type

The Chart Type worksheet contains a column of arbitrary data charted in a column chart. Several ActiveX controls are used to change the properties of the embedded chart. Option Button controls are used to select one of four chart types (Column, Bar, Area, or Line). Another set of Option Button controls and a Scroll Bar control are used to change the color of the chart area, plot area, and data series.

To learn how to manipulate properties of a chart using VBA, record a macro while changing the desired properties from the Excel application.

To change the type of chart, the integer constant representing the chart type is passed to the sub procedure SetChartType() where the ChartType property of the Chart object is set. I found the constants used to specify the chart type in the online help by looking up the ChartType property. I found the ChartType property in the list of Chart object members in Object Browser.

Private Sub optArea_Click()

SetChartType (xlArea) End Sub

Private Sub optBar_Click()

SetChartType (xlBarClustered) End Sub

Private Sub optColumn_Click()

SetChartType (xlColumnClustered) End Sub

Private Sub optLine_Click()

SetChartType (xlLine) End Sub

Private Sub SetChartType(myType As Integer) Dim myChart As Chart

Set myChart = ActiveSheet.ChartObjects(1).Chart myChart.ChartType = myType End Sub

For example, selecting the Option Button labeled Bar in Figure 9.7 changes the chart type to a bar chart.

The path to the chart traverses the Worksheet object, the ChartObjects collection object, and the ChartObject object before finally reaching the destination Chart object. An index value of one is used to return the specific ChartObject object from the ChartObjects collection object. This works because there is only one chart embedded on the worksheet. If subsequent charts are added to the worksheet, their index values will proceed in the order they are added (2, 3, 4, and so on). As with any collection object, be careful when using index values to return specific objects to ensure that the desired object is returned.

Option Buttons and a Scroll Bar are used to set the color of various components of the chart. The action occurs in the sub procedure ChangeColor() which is called when the Change() event of the Scroll Bar control or the Click() event of one of the Option Button controls is triggered by the user.

Private Sub optChartArea_Click()

ChangeColor End Sub

Private Sub optPlotArea_Click()

ChangeColor End Sub

Private Sub optSeries_Click()

ChangeColor End Sub

Private Sub scrColor_Change()

ChangeColor End Sub

In the ChangeColor() sub procedure, a reference to the chart is set with the variable myChart using the same object path in the SetChartType() sub procedure. A simple test for the value of the Option Button controls (optChartArea, optPlotArea, and optSeries) sets the variable used as the conditional in a Select/Case decision structure. In the Select/Case structure, the Colorlndex property of the ChartArea, PlotArea, and Series objects is assigned to the Value property of the Scroll Bar control (scrColor). The ChartArea object generally represents the background, axes, titles and legend in a chart; but this depends on the chart type. The PlotArea object represents the area on a chart where the data is plotted (data markers, data labels, gridlines, and so on). The Series object represents an individual data series and is returned from the SeriesCollection collection object.

Private Sub ChangeColor()

Dim component As Integer Dim myChart As Chart

Set myChart = ActiveSheet.ChartObjects(1).Chart If optChartArea.Value = True Then component = 1 If optPlotArea.Value = True Then component = 2 If optSeries.Value = True Then component = 3 Select Case component Case 1

myChart.ChartArea.Interior.ColorIndex = scrColor.Value Case 2

myChart.PlotArea.Interior.ColorIndex = scrColor.Value Case 3

If optLine.Value <> True Then myChart.SeriesCollection(1).Interior.ColorIndex = scrColor.Value End If Case Else

MsgBox ("Please select a chart component")

End Select

End Sub

The available objects and properties of a Chart object will vary somewhat with chart type; therefore, it is very important that you have a good understanding of the type of chart you are trying to manipulate.

For example, unlike the area, column, and bar charts, a line chart does not have an Interior object subordinate to its Series object. As a result, you cannot set the ColorIndex property of the Interior object of the Series object for a line chart. Attempting to do so will result in a runtime error.

Although the Chart Type worksheet illustrates the manipulation of a few properties of the Chart object, it is not a practical example of a good VBA application because it is just as easy for the user to manipulate these properties from the Excel application.

Typically, properties of a Chart object are set from VBA code when the chart must be added to the workbook or worksheet programmatically.

See the PieClock.xls workbook for another example of chart manipulation where an analog clock is simulated using a pie chart.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

Post a comment