Excel 2003 VBA Programmer's Reference

24

Generating Charts

In this chapter we will see how you can use the macro recorder to discover what objects, methods, and properties are required to manipulate charts. We will then improve and extend that code to make it more flexible and efficient. This chapter is designed to show you how to gain access to Chart objects in VBA code so that you can start to program the vast number of objects that Excel charts contain. You can find more information on these objects in Appendix A. We will look specifically at:

• Creating Chart objects on separate sheets
• Creating Chart objects embedded in a worksheet
• Editing data series in charts
• Defining series with arrays
• Defining chart labels linked to worksheet cells

You can create two types of charts in Excel: charts that occupy their own chart sheets and charts that are embedded in a worksheet. They can be manipulated in code in much the same way. The only difference is that, while the chart sheet is a Chart object in its own right, the chart embedded in a worksheet is contained by a ChartObject object. Each ChartObject on a worksheet is a member of the worksheet's ChartObjects collection. Chart sheets are members of the workbook's Charts collection.

Each ChartObject is a member of the Shapes collection, as well as a member of the ChartObjects collection. The Shapes collection provides you with an alternative way to refer to embedded charts. The macro recorder generates code that uses the Shapes collection rather than the ChartObjects collection ...