Modifying Chart Styles in Office 2010

Article
07/24/2014

Office Quick Note banner

Programmatically Working with Office 2010 Charts: Learn how to update a Microsoft PowerPoint 2010 chart by changing its style or layout.

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 and then modify various properties to change its style. 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, 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 with a specific style and then modifies various style settings.

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 ModifyChartStyle()
    Dim shp As Shape
    Set shp = ActivePresentation.Slides(1).Shapes.AddChart(xl3DColumn)

    Dim cht As Chart
    Set cht = shp.Chart

    ' Now, modify the various properties of the chart.
    Dim i As Integer

    ' Apply numbered layouts, from 1 to 10.
    For i = 1 To 10
        cht.ApplyLayout i
        ' You can't set the ChartTitle.Text property
        ' unless the chart has a title. Not all layouts
        ' include a title. You could force the layout to
        ' include a title by setting the HasTitle property
        ' to True (it's read/write), but it's interesting
        ' to see which layouts include titles and which don't.
        If cht.HasTitle Then
            cht.ChartTitle.Text = "Layout " & i
        End If
    Next i

    ' Applying all these data labels will make the chart unusable.
    ' You can use one of the enumerated values to set simple data labels:
    cht.SeriesCollection(1).ApplyDataLabels xlDataLabelsShowLabel
    cht.SeriesCollection(2).ApplyDataLabels xlDataLabelsShowValue

    ' You can supply other values individually to have more control over the data labels:
    cht.SeriesCollection(3).ApplyDataLabels ShowCategoryName:=True, ShowValue:=True, Separator:=": "

    ' Adjust the thickness of the back wall.
    cht.BackWall.Thickness = 25

    ' Change the chart type:
    cht.ChartType = xl3DBarStacked

    ' Change the 3D rotation. This property applies only to 3D charts:
    cht.Rotation = 30

    ' Modify the chart fill, using a theme color:
    shp.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1

    ' Add a gradient:
    shp.Fill.PresetGradient msoGradientHorizontal, 1, msoGradientLateSunset

    ' Modify other shape settings:
    shp.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2
    shp.ThreeD.BevelTopType = msoBevelRelaxedInset
    shp.Glow.Color.ObjectThemeColor = msoThemeColorAccent3
    shp.Shadow.Type = msoShadow32
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, select Microsoft Excel 14.0 Object Library, and then click OK.
Close the Visual Basic Editor.

Test the Solution

In this task, you run the VBA code that creates the chart and updates the various properties of the chart. The code also opens a Microsoft Excel workbook that contains the chart data. Note that the code shows you how to set several chart properties and results in a chart that is not very usable. You can create a more useful chart by deliberately targeting a specific set of properties.

To run the code and create the chart

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

Figure 1. Running the code creates a new chart and updates the styles

The best way to see the code in action is to place the Visual Basic Editor next to the PowerPoint window and then single-step through each line of code.

To step through the code

Open a new PowerPoint 2010 presentation.
Open the Visual Basic Editor, insert a module, and then paste the code described previously into the module.
Add a reference to the Microsoft Excel 14.0 Object Library to the project.
Add a breakpoint to the code. To add the breakpoint, right-click the following line of code, select Toggle, and then click Breakpoint.

For I = 1 to 10
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the PowerPoint window to the left side of your monitor and adjust both windows until you can see them both.
Click the Visual Basic Editor window and then press the F5 key to run the code. The code starts and then pauses at the breakpoint.
Press F8 to step through the code line-by-line; watch the code create the chart and update various style properties in the PowerPoint window.