O'Reilly logo

Excel® 2007 Charts by John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

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. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required