Manipulating Data Points in a Chart in Office 2010
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: Microsoft Excel 2010
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
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
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 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
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 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