Export (0) Print
Expand All

Manipulating Properties of a Chart Series in Office 2010

Office 2010

Office Quick Note banner

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

  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

Community Additions

ADD
Show:
© 2014 Microsoft