Manipulating Data Points in a Chart in Office 2010

  • Article

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.

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:

  • 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 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

Test the Solution

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

Next Steps