Creating Charts with VBA in Word 2010

Summary: Visual Basic for Applications (VBA) gives you the power to create charts in Microsoft Word 2010 and Office Word 2007, helping you to create a look and feel that is standardized for all documents. The Chart object model is structured like an Excel chart, making it easy to add data, select your chart type, and format the chart the way that you want. In addition, you can use VBA to obtain data from external sources and add controls to make the chart in your Word document interactive.

Applies to: Office 2010 | Open XML | VBA | Visual Studio Tools for Microsoft Office | Word 2007 | Word 2007 Service Pack 2 | Word 2010

In this article
Adding a Chart
Legends
Formatting Charts
Changing Chart Size and Position
Adding Additional Series
Changing the Chart Type
More Advanced Charting
Conclusion
Additional Resources

Published: May 2010

Provided by:  Peter Gruenbaum, SDK Bridge, LLC

Contents

• Adding a Chart

• Legends

• Formatting Charts

• Changing Chart Size and Position

• Adding Additional Series

• Changing the Chart Type

• More Advanced Charting

• Conclusion

• Additional Resources

Download code

Using Visual Basic for Applications (VBA), you can create charts in Microsoft Word 2010 and Office Word 2007 Service Pack 2 (SP2) that have all the functionality of charts in Excel. VBA code can take data from various sources and create charts that are standardized across your documents.

There are a couple good reasons to create charts in Word with VBA rather than create them in Excel and paste them into a document. For one, if you are already using VBA in order to automatically generate documents, then having VBA to create the charts will be much more efficient. Also, you can use VBA to make your documents interactive by adding controls such as buttons and dropdowns into your documents that create or update charts.

The Chart object model in Word is fairly straightforward. It has properties that give access to Excel objects that contain the chart data, making the manner in which charts are controlled similar to creating charts in Excel. In addition, the Chart object model in Word is similar to the Chart object model in Excel, so you can easily take any VBA code written to create charts in Excel and port them into Word. For more information, see the Chart object in the Word 2010 developer reference.

This article contains two samples: a basic chart in Word (WordChart.docm), and a pair of files (ExcelWithChart.xlsm and WordChartLinkedToExcel.docm) that show how to have an interactive Word document that uses a chart created in Excel. You can download the code samples from the MSDN Code Gallery.

Adding a Chart

To insert a chart, add a Chart object to the document using the AddChart method of the Shapes collection.

Note

In Office Word 2007 SP2 and Word 2010, you can also get a reference to a Chart object through the InlineShapes collection. Shape objects are anchored to a range of text but are free-floating and can be positioned anywhere on the page. InlineShape objects are treated like characters and are positioned as characters within a line of text. The examples in this article use the Shapes collection to get a reference to a Chart object. For more information about using the InlineShapes collection to get a reference to a Chart object, see Working With Charts.

The Chart object automatically has an Excel spreadsheet associated with it through its Workbook property, which contains Worksheet objects. Now, create an example in Word.

Create a new Word document, and then create a new macro called "Insert Chart". To access the Excel objects, such as the Workbook and Worksheet, you must reference the Excel application programming interface (API). In the Visual Basic Editor (VBE), from the Tools menu, choose References. Select the check box next to Microsoft Excel 14.0 Object Library, as shown in Figure 1.

Figure 1. Selecting the Excel reference in the References dialog box

Create two variables, one for the chart, and the other for worksheet that will hold the data.

    Dim salesChart As Chart
    Dim chartWorkSheet As Excel.Worksheet

Now insert a new chart into the active document using the AddChart method and set it to the salesChart variable.

Set salesChart = ActiveDocument.Shapes.AddChart.Chart

Set the worksheet equal to the first worksheet in the workbook that is associated with the chart.

Set chartWorkSheet = salesChart.chartData.Workbook.WorkSheets(1)

When you add the chart, an Excel spreadsheet will be created with some sample data included. Limit the size of the data to the cell range A1 to B5. Do this with the Resize method of the ListObject class.

chartWorkSheet.ListObjects("Table1").Resize chartWorkSheet.Range("A1:B5")

Change the header for Series 1 in the sample data to be "Sales".

chartWorkSheet.Range("Table1[[#Headers],[Series 1]]").FormulaR1C1 = "Sales"

Finally, add some data.

    chartWorkSheet.Range("A2").FormulaR1C1 = "Bikes"
    chartWorkSheet.Range("A3").FormulaR1C1 = "Accessories"
    chartWorkSheet.Range("A4").FormulaR1C1 = "Repairs"
    chartWorkSheet.Range("A5").FormulaR1C1 = "Clothing"
    chartWorkSheet.Range("B2").FormulaR1C1 = "1000"
    chartWorkSheet.Range("B3").FormulaR1C1 = "2500"
    chartWorkSheet.Range("B4").FormulaR1C1 = "4000"
    chartWorkSheet.Range("B5").FormulaR1C1 = "3000"

This example simply sets the values in code. If you were automatically generating the Word document, then that data can come from anywhere. Also, the data could be in a table in the Word document, and your VBA code could extract the values and then set the worksheet cell formula to be those values.

Run the macro, and you should see a chart similar to the following.

Figure 2. Basic chart

To clean up, close the Excel application, because the worksheet was only meant to be temporary. Add the following code.

salesChart.ChartData.Workbook.Application.Quit

Legends

By default, your chart will have a legend on it. The legend indicates the type of data that is graphed by displaying the header for the column of data. Legends are especially useful if you have more than one series of data on a chart so that you can tell which parts of the chart correspond to which column in your data table. In the current example, there is only one column.

You can access the legend through the Legend property of the Chart object. If you do not want a legend, you can remove it by using the LegendDelete method.

salesChart.Legend.Delete

However, do not delete yours. Instead, put a box around it by setting the Visible property of the Legend object’s Line property to msoCTrue.

The following image shows the chart with a formatted legend.

Figure 3. Chart with formatted legend

salesChart.Legend.Format.Line.Visible = msoCTrue

Formatting Charts

This current chart is plain. Add some formatting to make it look nicer by setting the background fill to be theme color accent 1. The advantage of using an Office theme color is that the chart will follow whatever theme is selected for the Word document, and therefore match the look and feel of the rest of the document.

With salesChart.ChartArea.Format.Fill
    .Visible = msoTrue
    .Solid
    .ForeColor.ObjectThemeColor = wdThemeColorAccent1
End With

Next, add a black border around the chart.

With salesChart.ChartArea.Format.Line
    .Visible = msoCTrue
    .DashStyle = msoLineSolid
    .Weight = 3
End With

Add a title and format it.

salesChart.HasTitle = True
With salesChart.ChartTitle
    .Characters.Font.Italic = True
    .Characters.Font.Size = 18
    .Format.TextFrame2.TextRange.Font.Fill.ForeColor.ObjectThemeColor = wdThemeColorMainLight2
    .Text = "2010 Sales"
End With

Note

If you prefer not to use an Office theme color, but instead you wish to choose exactly the color you want, you can replace the line that sets the ObjectThemeColor property to wdThemeColorMainLight2 with a line that sets the font color to an RGB (red/green/blue) value, such as:

.Characters.Font.Color = RGB(0, 0, 100)

Finally, add a label to the y-axis and change the maximum value to 5000.

With salesChart.Axes(xlValue)
    .HasTitle = True
    .AxisTitle.Text = "Revenue ($)"
    .MaximumScale = 5000
End With

The following image shows the chart with a theme applied.

Figure 4. Chart with a theme applied

To see the effect of using Office theme colors, change the theme from the Page Layout tab of the Word document. You will see that the colors in your chart change as well.

Changing Chart Size and Position

To change the size and position of the chart, do not change the properties of the chart itself, but rather change the properties on the chart parent. The Parent property returns the graphical object that contains the chart, and when you place and size that graphical container, then the chart will appear the correct size and location. For example, to make the chart 300 x 150 pixels and have it appear 100 pixels from the left side of the document, do the following.

With salesChart.Parent
    .Left = 100
    .Width = 300
    .Height = 150
End With

Adding Additional Series

In addition to graphing just one column of a table, you can graph multiple columns within a chart. Each column of data is called a series. To add another series to your chart, add in data for that series and expand the data table to include them. You must do this before your line that makes Excel quit. The code below adds a forecast series and sets its type to xlLine so that it will be displayed as a line graph, rather than bars.

chartWorkSheet.ListObjects("Table1").Resize chartWorkSheet.Range("A1:C5")
chartWorkSheet.Range("Table1[[#Headers],[Series 2]]").FormulaR1C1 = "Forecast"
chartWorkSheet.Range("c2").FormulaR1C1 = "500"
chartWorkSheet.Range("c3").FormulaR1C1 = "3000"
chartWorkSheet.Range("c4").FormulaR1C1 = "2500"
chartWorkSheet.Range("c5").FormulaR1C1 = "4000"
salesChart.SeriesCollection(2).Type = xlLine

The following image shows the chart with two series added.

Figure 5. Chart with two series

Changing the Chart Type

The Chart object allows you to choose from a large number of chart types. Remove the second series and change the chart type to be a three-dimensional pie chart. This is done by changing the ChartType property on the Chart object.

salesChart.ChartType = xl3DPie

The following image shows the chart after changing its type to a three-dimensional pie chart.

Figure 6. Setting the chart type to three-dimensional pie chart

More Advanced Charting

Also included in the sample code is a more advanced charting example. It uses data from an external Excel file and shows how you can put a control in a Word document to make the document interactive. It consists of two files: ExcelWithChart.xlsm and WordChartLinkedToExcel.docm. To use them, use the following procedure.

To create the interactive Word document charting example

1. Open WordChartLinkedToExcel.docm.

2. Open ExcelWithChart.xlsm.

3. Select the chart in ExcelWithChart.xlsm.

4. On the ribbon in ExcelWithChart.xlsm, click Developer, click Macros, and then run the CopyChartToWord macro. This will copy the chart to the Word document.

5. Return to Word. Place the insertion point just after the dropdown box.

6. Use the ComboBox to show graph columns, which correspond to Q1, Q2, Q3, and Q4.

The Change event handler of the ComboBox has the following code. Be aware that in this case, because you are using external data, you must call Activate on the ChartData object to access the Workbook. After changing the series name and value, the Chart object must be refreshed to show the changes.

Private Sub ComboBox1_Change()
    Dim salesChart As Chart
    Dim chartWorkSheet As Excel.Worksheet
    
    ' The chart is the second shape. (The first is the ComboBox.)
    Set salesChart = ActiveDocument.InlineShapes(2).Chart
        
    ' Activate the data before you can access the workbook
    salesChart.ChartData.Activate

    ' Get the first worksheet
    Set chartWorkSheet = salesChart.ChartData.Workbook.Worksheets(1)
    
    ' Choose the series based on the ComboBox value
    Select Case ComboBox1.Value
        Case "Q1"
            salesChart.SeriesCollection(1).Name = chartWorkSheet.Range("B1")
            salesChart.SeriesCollection(1).Values = "='Sheet1'!$B$2:$B$5"
            
        Case "Q2"
            salesChart.SeriesCollection(1).Name = chartWorkSheet.Range("C1")
            salesChart.SeriesCollection(1).Values = "='Sheet1'!$C$2:$C$5"
            
        Case "Q3"
            salesChart.SeriesCollection(1).Name = chartWorkSheet.Range("D1")
            salesChart.SeriesCollection(1).Values = "='Sheet1'!$D$2:$D$5"
            
        Case "Q4"
            salesChart.SeriesCollection(1).Name = chartWorkSheet.Range("E1")
            salesChart.SeriesCollection(1).Values = "='Sheet1'!$E$2:$E$5"
            
    End Select
    
    ' Refresh chart so changes are visible
    salesChart.Refresh
    
    ' Minimize Excel
    chartWorkSheet.Application.WindowState = xlMinimized
End Sub

Conclusion

You can create charts in Word by using the Chart object. The Shapes.AddChart method will create a new chart. Access its Workbook property; resize the table to hold just the data that you want, and then use the Worksheet.Range property to set the values for the chart. The Chart object allows you to format many aspects of the chart, including the background, legend, title, and axes. You can also work with external Excel charts in Word, but you must use the ChartData.Activate method before you can access the Workbook with VBA code.

Additional Resources

For more information, see the following resources:

• Getting Started with VBA in Office 2010

• MSDN Code Gallery Sample: Creating Charts with VBA in Word 2010 (odcwd2010ta_creatingchartswithvba.zip)

• Microsoft Office Developer Center

• Microsoft Word Developer Center

• Getting Started with Office Development