Microsoft Office Tutorials and References
In Depth Information
Changing the Data Used in a Chart
Note that I did not create a function to get the fourth SERIES argument (plot order). You can
obtain this argument directly by using the PlotOrder property of the Series object.
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 get series ranges.xlsm . These
functions are documented in such a way that they can be easily adapted to other
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 x As Variant
Set Ser = ActiveChart.SeriesCollection(1)
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-8 shows another example. The chart has three data series. Buttons on the sheet
execute macros that expand and contract each of the data ranges.
The ContractAllSeries procedure follows. This procedure loops through the Series
Collection 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
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
Search JabSto ::

Custom Search