Programmatically Create a Chart in Office 2010

Article
07/24/2014

Office Quick Note banner

Programmatically Working with Office 2010 Charts: Learn how to create a chart in Microsoft PowerPoint 2010.

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

In this article
Add a Standard Module to a PowerPoint Presentation
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. You can chose from many common business and technical chart types. You can also enhance and manipulate the appearance of your charts programmatically with various classes and objects. In this topic, you programmatically create a simple chart. To complete this task, you must do the following:

Add a Standard Module to a PowerPoint Presentation
Add the Code to the Visual Basic Editor
Test the Solution

Add a Standard Module to a PowerPoint Presentation

In this task, you open a PowerPoint 2010 presentation, open the Visual Basic Editor, and then insert a standard module.

To add a standard module to a PowerPoint presentation

Start PowerPoint 2010.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.

Note

If you do not see the Developer tab in PowerPoint 2010 (or Word 2010), click the File tab, and then click Options. In the categories pane, click Popular, select Show Developer tab in the Ribbon, and then click OK.
On the Insert menu, click Module. This adds Module1 to the Projects pane on the left side of the Visual Basic Editor.

Add the Code to the Visual Basic Editor

In this task, you add programming code that creates the simple chart. Note that the procedures in this topic work with a PowerPoint 2010 presentation. You can also work with a Word 2010 program by replacing ActivePresentation.Slides(1) with ActiveDocument. All of the remaining code works exactly the same in Word.

To add code to the Visual Basic Editor

In the Projects pane, click Module1.

Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

Sub CreateChart()
    ' Create a very simple chart.
    Dim cht As Chart
    Dim chtData As ChartData
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    ' Create the chart and set a reference to the chart data.
    Dim shp As Shape
    Set shp = ActivePresentation.Slides(1).Shapes.AddChart(xlBarClustered, 10, 10, 500, 200)
    ' Retrieve the chart contained within the shape. Although you know the
    ' shape contains a chart in this example, you can always
    ' use the Type property to verify that the shape you're working
    ' with is indeed a chart before you try to retrieve its Chart property:
    If shp.Type <> msoChart Then
        Exit Sub
    End If
    Set cht = shp.Chart

    ' Every new chart has an Excel workbook that contains its data.
    Set wb = cht.ChartData.Workbook
    Set ws = wb.Worksheets(1)

     ' Add the data to the workbook.
    ' Resize the table, which is always called Table 1:
    ws.ListObjects("Table1").Resize ws.Range("A1:B5")

    ' Set the title for the series:
    ws.Range("B1").Value = "Regional Sales"

    ' Put the data in the rows of the worksheet:
    ws.Range("A2:B2").Value = Array("North", 125)
    ws.Range("A3:B3").Value = Array("South", 12)
    ws.Range("A4:B4").Value = Array("East", 97)
    ws.Range("A5:B5").Value = Array("West", 150)

    ' Force the chart to retrieve its data and redraw itself:
    cht.ApplyDataLabels xlDataLabelsShowValue

    ' If you want to quit Excel, uncomment this line:
    ' wb.Application.Quit
End Sub

The data for the chart is contained in an Microsoft Excel 2010 workbook. To create the workbook in code, you must add a reference to the Microsoft Excel 14.0 Object Library. On the Visual Basic Editor menu, click Tools, and then click References.
In the References dialog box, scroll until you see Microsoft Excel 14.0 Object Library, select it, and then click OK.
Close the Visual Basic Editor.

Test the Solution

In this task, you run the VBA code that creates the chart. The code also opens an Excel 2010 workbook that contains the chart data.

To run the code and create the chart

On the Developers tab, click Macros, select CreateChart, and then click Run. Examine the new chart; it is similar to the one shown in Figure 1.

Figure 1. Running the code creates a new chart