Manipulating Properties of a Chart Series in Office 2010
Programmatically Working with Office 2010 Charts: Learn how to manipulate series data in a Microsoft Excel 2010 chart.
Applies to: Microsoft Excel 2010
Published: March 2011
Provided by: Frank Rice, Microsoft Corporation
Microsoft Office 2010 includes a powerful and versatile charting engine. A series is a group of data points on a chart. You can enhance and manipulate various characteristics of the series data in a chart by using the methods and properties of the Series class. In this topic, you programmatically create a simple chart and then update it by changing the property values of the series. To complete this task, you must do the following:
Add the Code to the Visual Basic Editor
In this task, you add programming code that creates the simple chart with a small amount of data and then sets various properties of the chart’s series.
To add code to the Visual Basic Editor
Open a new Excel 2010 workbook.
Press Alt + F11 to open the Visual Basic Editor.
In the Project pane, double-click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the window that appears.
Sub TestSeriesProperties() ' Demonstrate Series. ' InvertColorIndex ' InvertColor ' InvertIfNegative (not new in 2010) Dim rng As Range ' Press Shift+F8 to step over this procedure: Set rng = FillRange(-25, 25, 20) ' Create the chart, set its source data, and turn off tick labels--they just get in the way here: Dim cht As Chart Set cht = Shapes.AddChart(XlChartType.xl3DColumn, Width:=500, Height:=300).Chart cht.SetSourceData rng cht.Axes(xlCategory).TickLabelPosition = xlNone With cht.SeriesCollection(1) ' Invert negative values: .InvertIfNegative = True ' Set a color: .InvertColor = vbRed ' Or select a color index: .InvertColorIndex = 13 End With End Sub Function FillRange(minValue As Integer, maxValue As Integer, count As Integer) As Range Dim i As Integer For i = 1 To count ' Generate random numbers between minValue and maxValue Me.Range("A" & i, "B" & i).Value = Array("Value " & i, Int((maxValue - minValue + 1) * Rnd + minValue)) Next i Set FillRange = Me.Range("A1", "B" & count) End Function
Test the Solution
In this task, you run the VBA code that creates the chart and modifies the properties of the series. The best way to see the code in action is to place the Visual Basic Editor window next to the Excel window and single-step through each line of code.
To step through the code
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust both windows until you can see them both.
Click the Visual Basic Editor window, place the cursor in the TestSeriesProperties module, and then press the F8 key to step through the code line-by-line; watch the code create the chart (see Figure 1) and then change the series data.
Figure 1. Code creates the chart and plots the series data