Changing the Data Used in a Chart
The examples so far in this chapter have used the SourceData property to specify the complete data range for a chart. In many cases, you'll want to adjust the data used by a particular chart series. To do so, access the Values property of the Series object. The Series object also has an XValues property that stores the category axis values.
 |
The data used in each series in a chart is determined by its SERIES formula. When you select a data series in a chart, the SERIES formula appears in the formula bar. This is not a real formula: In other words, you can't use it in a cell , and you can't use worksheet functions within the SERIES formula. You can, however, edit the arguments in the SERIES formula.
A SERIES formula has the following syntax:
=SERIES(series_name, category_labels, values, order, sizes)
The arguments that you can use in the SERIES formula are
-
series_name : (Optional) A reference to the cell that contains the series name used in the legend. If the chart has only one series, the name argument is used as the title. This argument can also consist of text in quotation marks. If omitted, Excel creates a default series name (for example, Series 1 ).
-
category_labels : (Optional) A reference to the range that contains the labels for the category axis. If omitted, Excel uses consecutive integers beginning with 1. For XY charts , this argument specifies the — values. A noncontiguous range reference is also valid. The ranges' addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of comma-separated values (or text in quotation marks) enclosed in curly brackets.
-
values : (Required) A reference to the range that contains the values for the series. For XY charts, this argument specifies the Y values. A noncontiguous range reference is also valid. The ranges' addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of comma-separated values enclosed in curly brackets.
-
order : (Required) An integer that specifies the plotting order of the series. This argument is relevant only if the chart has more than one series. For example, in a stacked column chart, this parameter determines the stacking order. Using a reference to a cell is not allowed.
-
sizes : (Only for bubble charts) A reference to the range that contains the values for the size of the bubbles in a bubble chart. A noncontiguous range reference is also valid. The ranges' addresses are separated by a comma and enclosed in parentheses. The argument could also consist of an array of values enclosed in curly brackets.
Range references in a SERIES formula are always absolute, and they always include the sheet name. For example:
=SERIES(Sheet1!$B,,Sheet1!$B:$B,1)
A range reference can consist of a noncontiguous range. If so, each range is separated by a comma, and the argument is enclosed in parentheses. In the following SERIES formula, the values range consists of B2:B3 and B5:B7:
=SERIES(,,(Sheet1!$B:$B,Sheet1!$B:$B),1)
You can substitute range names for the range references. If you do so (and the name is a workbook-level name), Excel changes the reference in the SERIES formula to include the workbook. For example:
=SERIES(Sheet1!$B,,budget.xlsx!CurrentData,1)
| |
| Note | The Values property corresponds to the third argument of the SERIES formula, and the XValues property corresponds to the second argument of the SERIES formula. See the sidebar, "Understanding a Chart's SERIES Formula." |
Changing chart data based on the active cell
Figure 18-9 shows a chart that's based on the data in the row of the active cell. When the user moves the cell pointer, the chart is updated automatically.
Figure 18-9: This chart always displays the data from the row of the active cell.
This example uses an event handler for the Sheet1 object. The SelectionChange event occurs whenever the user changes the selection by moving the cell pointer. The event handler procedure for this event (which is located in the code module for the Sheet1 object) is as follows :
Private Sub Worksheet_SelectionChange(ByVal Target _ As Excel.Range) If CheckBox1 Then Call UpdateChart End Sub
In other words, every time the user moves the cell cursor, the Worksheet_ SelectionChange procedure is executed. If the Auto Update Chart check box (an ActiveX control on the sheet) is checked, this procedure calls the UpdateChart procedure, which follows:
Sub UpdateChart() Dim ChtObj As ChartObject Dim UserRow As Long Set ChtObj = ActiveSheet.ChartObjects(1) UserRow = ActiveCell.Row If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then ChtObj.Visible = False Else ChtObj.Chart.SeriesCollection(1).Values = _ Range(Cells(UserRow, 2), Cells(UserRow, 6)) ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text ChtObj.Visible = True End If End Sub
The UserRow variable contains the row number of the active cell. The If statement checks that the active cell is in a row that contains data. (The data starts in row 4.) If the cell cursor is in a row that doesn't have data, the ChartObject object is hidden, and the underlying text is visible ("Cannot display chart"). Otherwise, the code sets the Values property for the Series object to the range in columns 2 “6 of the active row. It also sets the ChartTitle object to correspond to the text in column A.
| CD-ROM | This example, named |
chart active cell.xlsm Using VBA to determine the ranges used in a chart
The previous example demonstrated how to use the Values property of a Series object to specify the data used by a chart series. This section discusses using VBA macros to identify the ranges used by a series in a chart. For example, you might want to increase the size of each series by adding a new cell to the range.
Following is a description of three properties that are relevant to this task:
-
Formula property: Returns or sets the SERIES formula for the Series . When you select a series in a chart, its SERIES formula is displayed in the formula bar. The Formula property returns this formula as a string.
-
Values property: Returns or sets a collection of all the values in the series. This can be a range on a worksheet or an array of constant values, but not a combination of both.
-
XValues property: Returns or sets an array of — values for a chart series. The XValues property can be set to a range on a worksheet or to an array of values, but it can't be a combination of both. The XValues property can also be empty.
If you create a VBA macro that needs to determine the data range used by a particular chart series, you might think that the Values property of the Series object is just the ticket. Similarly, the XValues property seems to be the way to get the range that contains the — values (or category labels). In theory, that certainly seems correct. But, in practice, it doesn't work.
When you set the Values property for a Series object, you can specify a Range object or an array. But when you read this property, an array is always returned. Unfortunately, the object model provides no way to get a Range object used by a Series object.
One possible solution is to write code to parse the SERIES formula and extract the range addresses. This sounds simple, but it's actually a difficult task because a SERIES formula can be very complex. Following are a few examples of valid SERIES formulas.
=SERIES(Sheet1!$B,Sheet1!$A:$A,Sheet1!$B:$B,1) =SERIES(,,Sheet1!$B:$B,1) =SERIES(,Sheet1!$A:$A,Sheet1!$B:$B,1) =SERIES("Sales Summary",,Sheet1!$B:$B,1) =SERIES(,{"Jan","Feb","Mar"},Sheet1!$B:$B,1) =SERIES(,(Sheet1!$A,Sheet1!$A),(Sheet1!$B,Sheet1!$B),1) =SERIES(Sheet1!$B,Sheet1!$A:$A,Sheet1!$B:$B,1,Sheet1!$C:$C) As you can see, a SERIES formula can have missing arguments, use arrays, and even use noncontiguous range addresses. And, to confuse the issue even more, a bubble chart has an additional argument (for example, the last SERIES formula in the preceding list). Attempting to parse the arguments is certainly not a trivial programming task.
I spent a lot of time working on this problem, and I eventually arrived at a solution. The trick involves evaluating the SERIES formula by using a dummy function. This function accepts the same arguments as a SERIES formula and returns a 2 — 5 element array that contains all the information in the SERIES formula.
I simplified the solution by creating four custom VBA functions, each of which accepts one argument (a reference to a Series object) and returns a two-element array. These functions are the following:
-
SERIESNAME_FROM_SERIES : The first array element contains a string that describes the data type of the first SERIES argument ( Range , Empty , or String ). The second array element contains a range address, an empty string, or a string.
-
XVALUES_FROM_SERIES : The first array element contains a string that describes the data type of the second SERIES argument ( Range , Array , Empty , or String ). The second array element contains a range address, an array, an empty string, or a string.
-
VALUES_FROM_SERIES : The first array element contains a string that describes the data type of the third SERIES argument ( Range or Array ). The second array element contains a range address or an array.
-
BUBBLESIZE_FROM_SERIES : The first array element contains a string that describes the data type of the fifth SERIES argument ( Range , Array , or Empty ). The second array element contains a range address, an array, or an empty string. This function is relevant only for bubble charts.
Note that I did not create a function to get the fourth SERIES argument (plot order). This argument can be obtained directly by using the PlotOrder property of the Series object.
| CD-ROM | The VBA code for these functions is too lengthy to be listed here, but the code is available on the companion CD-ROM in a file named |
The following example demonstrates the VALUES_FROM_SERIES function. It displays the address of the values range for the first series in the active chart.
Sub ShowValueRange() Dim Ser As Series Dim As Variant Set Ser = ActiveChart.SeriesCollection(1) = VALUES_FROM_SERIES(Ser) If x(1) = "Range" Then MsgBox Range(x(2)).Address End If End Sub
The variable x is defined as a variant and will hold the two-element array that's returned by the VALUES_FROM_SERIES function. The first element of the x array contains a string that describes the data type. If the string is Range , the message box displays the address of the range contained in the second element of the x array.
Figure 18-10 shows another example. The chart has three data series. Buttons on the sheet execute macros that expand and contract each of the data ranges.
Figure 18-10: This workbook demonstrates how to expand and contract the chart series by using VBA macros.
The ContractAllSeries procedure is listed below. This procedure loops through the SeriesCollection collection and uses the XVALUE_FROM_SERIES and the VALUES_ FROM_SERIES functions to retrieve the current ranges. It then uses the Resize method to decrease the size of the ranges.
Sub ContractAllSeries() Dim s As Series Dim Result As Variant Dim DRange As Range For Each s In ActiveSheet.ChartObjects(1).Chart.SeriesCollection Result = XVALUES_FROM_SERIES(s) If Result(1) = "Range" Then Set DRange = Range(Result(2)) If DRange.Rows.Count > 1 Then Set DRange = DRange.Resize(DRange.Rows.Count - 1) s.XValues = DRange End If End If Result = VALUES_FROM_SERIES(s) If Result(1) = "Range" Then Set DRange = Range(Result(2)) If DRange.Rows.Count > 1 Then Set DRange = DRange.Resize(DRange.Rows.Count - 1) s.Values = DRange End If End If Next s End Sub
The ExpandAllSeries procedure is very similar. When executed, it expands each range by one cell.
EAN: 2147483647
Pages: 319