Manipulating Data Points in a Chart in Office 2010

Office 2010

Office Quick Note banner

Programmatically Working with Office 2010 Charts: Learn how to manipulate and retrieve information about points along a series in a Microsoft Excel 2010 chart.

Last modified: April 22, 2011

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | VBA | 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 data point is the smallest individual entity on a chart. You can enhance and manipulate various characteristics of the points in a chart series by using the methods and properties of the Point class. In this topic, you programmatically create a simple chart and then update it by changing property values of the points in a series. You also use similar properties to display information about a particular point. To complete these tasks, you must do the following:

In this task, you add programming code that creates a simple chart with a small amount of data and then manipulates various properties of the points in a 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 in the window that appears.

    Sub TestPointClass()
        ' First, create a simple chart that contains points.
        Range("A1:B1").Value = Array("Region", "Sales")
        Range("A2:B2").Value = Array("North", 100)
        Range("A3:B3").Value = Array("South", 200)
        Range("A4:B4").Value = Array("East", 300)
        Range("A5:B5").Value = Array("West", 400)
        ' Next, set the point types.
        Dim cht As Chart
        Set cht = Shapes.AddChart.Chart
        cht.ChartType = xlLineMarkers
        cht.SetSourceData Source:=Range("A1:B5")
        With cht.SeriesCollection(1)
            .Points(1).MarkerStyle = xlMarkerStyleDiamond
            .Points(2).MarkerStyle = xlMarkerStyleCircle
            .Points(3).MarkerStyle = xlMarkerStyleDash
            .Points(4).MarkerStyle = xlMarkerStyleSquare
            Dim i As Integer
            For i = 1 To 4
                DisplayPointProperties .Points(i)
            Next i
        End With
    End Sub
    Sub DisplayPointProperties(pt As Point)
        ' Display information about the selected
        ' point in the Immediate window:
        Debug.Print "========"
        Debug.Print "Name:   " & pt.Name
        Debug.Print "Left:   " & pt.Left
        Debug.Print "Top :   " & pt.Top
        Debug.Print "Width:  " & pt.Width
        Debug.Print "Height: " & pt.Height
    End Sub

In this task, you run the VBA code that creates the chart and modifies some properties of the points in a series. The code also displays information about each point in the Immediate window. The best way to see the code in action is to place the Visual Basic Editor window next to the Excel window and then 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 TestPointClass 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 points. After all the code runs, look in the Immediate window to see information about each point.

    Figure 1. Code creates the chart and adds the data points

    Code creates the chart and plots the data points