Excel® 2007 Charts

Chapter 15. Advanced VBA Examples

In This Chapter

• Macros to create and format

• Macros to apply data labels to a series

• Macros to export charts as GIF files

• Macros to identify and modify ranges used by a chart

• A macro to create a word cloud in a shape

• Macros that are triggered by events

This chapter presents a wide variety of macros that manipulate charts in various ways. These examples are intended to demonstrate how to work with the objects that comprise a chart. You may be able to use some of these as written, but others may require some modification to make them more useful to you.

Compatibility note

The VBA code in this book uses many new chart–related properties and methods that were introduced in Excel 2007. As a result, some of the code presented here will not work with previous versions of Excel.

Creating Charts with VBA

The following sections describe how to use VBA to create an embedded chart and a chart on a chart sheet.

Creating an Embedded Chart

In Excel 2007, a ChartObject is a special type of Shape object. Therefore, it's a member of the Shapes collection. To create a new chart, use the AddChart method of the Shapes collection. The following statement creates an empty embedded chart:

ActiveSheet.Shapes.AddChart

The AddChart method can use five arguments (all are optional):

• Type: The type of chart. If omitted, the default chart type is used. Constants for all the chart types are provided (for example, xlArea, xlColumnClustered, and so on).

• Left: The left position of the chart, in points. ...