Manipulating Properties of a Chart Series in Office 2010

  • Article

Office Quick Note banner

Programmatically Working with Office 2010 Charts: Learn how to manipulate series data in a Microsoft Excel 2010 chart.

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

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

  • Test the Solution

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

  1. Open a new Excel 2010 workbook.

  2. Press Alt + F11 to open the Visual Basic Editor.

  3. In the Project pane, double-click Sheet1.

  4. 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

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust both windows until you can see them both.

  3. 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

    Code creates the chart and plots the series data

Next Steps