Modifying Chart Axes Text in Office 2010
Programmatically Working with Office 2010 Charts: Learn how to update a Microsoft PowerPoint 2010 chart by changing various settings of the axes text.
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 update it by changing property values of the axes text. 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 and then sets various properties of the chart axes text.
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 ModifyAxisTitles() Dim shp As Shape ' Create a chart. Set shp = ActivePresentation.Slides(1).Shapes.AddChart(xlBarClustered) Dim cht As Chart ' You can check the shp.HasChart property to determine if ' the shape has a chart before continuing, but you can ' be sure this particular shape has a chart because you ' just created it. Set cht = shp.Chart ' Warning: The HasAxis method is strange, and requires ' you to read the documentation in order to call it. cht.HasAxis(xlCategory) = True ' Warning: The Axes method returns an Object. ' If you want to make use of IntelliSense in the ' VBA editor, you must create an Axis variable, and ' assign it to the result of calling the Axes method. Dim ax As Axis ' Work with the category axis: Set ax = cht.Axes(xlCategory) With ax .CategoryType = xlAutomaticScale .MajorTickMark = xlInside .TickLabelPosition = xlTickLabelPositionNextToAxis End With SetTitleProperties ax, "Categories" ' Work with the value axis: cht.HasAxis(xlValue) = True Set ax = cht.Axes(xlValue) With ax .HasDisplayUnitLabel = False .DisplayUnit = xlCustom .DisplayUnitCustom = 500 .HasTitle = True .AxisTitle.Caption = "Milligrams" End With SetTitleProperties ax, "Values" End Sub Sub SetTitleProperties(ax As Axis, title As String) With ax .HasTitle = True With .AxisTitle .Text = title With .Characters.Font .Size = 14 .Color = xlRed End With End With End With End Sub 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, 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 modifies properties of the axes text. The code also opens an Excel 2010 workbook that contains the chart data. To see the code in action, you step through it line-by-line.
To run the code and create the chart
On the Developers tab, click Macros, select ModifyAxisTitles, 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 axes text
The best way to see the code in action is to place the Visual Basic Editor window 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.
With cht.Legend
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 modify the axes text in the PowerPoint window.