Chapter 15: Charts and Graphs

Overview

You can create charts and graphs very simply using VBA by using the Chart Wizard command. You use it in exactly the same way that you would from within the Excel application, but all the commands and properties are set using VBA. Because everything you can do as an Excel user is represented in the object model, it is quite straightforward to do this. First, set up a range of data suitable for a pie chart, as shown in Figure 15-1.

Figure 15-1: A data range suitable for a pie chart

Give the range the name North by selecting Insert Name Define from the spreadsheet menu, and click OK to close the dialog. Now insert the following code into a module:

 Sub test_chart() 
 Dim c As Excel.Chart 
 Set c = ActiveWorkbook.Charts.Add 
 c.ChartWizard Source:="north", gallery:=xl3DPie, Format:=7,  _ 
 Title:="MyChart" , categorylabels:=1 
 End Sub 

This code first creates a chart object called c and then adds a new chart to the active workbook. It then uses the Chart Wizard to add the source range, title, and chart type. Run this code, and your chart should look like Figure 15-2.

Figure 15-2: A pie chart created using the VBA example

The full syntax of the ChartWizard method is shown here:

 ChartWizard (Source, Gallery, Format, PlotBy, CategoryLabels, 
 SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle) 

‚   ‚   ‚  

Parameters are as follows :

Parameter	Description
Object	The Chart object.
Source	Specifies the range that contains source data for the chart.
Gallery	Specifies the chart type. Examples such as xlPie , xl3DColumn , and xl3DbarStacked are found under xlChartType in the Object Browser.
Format	Specifies the built-in autoformat. Values True or False designate whether to use the default chart format.
PlotBy	Specifies the orientation of the data ( xlRows or xlColumns ).
CategoryLabels	The number of rows or columns containing category labels.
SeriesLabels	The number of rows or columns containing series labels.
HasLegend	Specifies if the chart has a legend (True or False).
Title	The title text of the chart.
CategoryTitle	The Category axis title text.
ValueTitle	The Value axis title text.
ExtraTitle	Additional axis title text for some charts.

The parameters that are most important to the ChartWizard method are as follows:

• PlotBy

• CategoryLabels

• SeriesLabels

These determine how the data source will be used to provide label information and data and whether it will be shown in columns or rows. Try running the code in the previous example without the PlotBy parameter (this defaults to columns). You can see the importance of this because your chart will read the data range incorrectly and you will see a pie chart with only one color in it. Try removing the CategoryLabels parameter that currently shows that Row 1 contains the labels, and you will find that no labels are shown on the segments of the pie chart.

In terms of the Chart Type (Gallery), Excel versions 97 and onward have built-in parameters to define the various types of charts. These all correspond to the gallery in the Chart Wizard that you see if you insert a chart into a spreadsheet:

Constant	Description	Value
xlArea	Area Chart	1
xlBar	Bar Chart	2
xlColumn	Column Chart	3
xlLine	Line Chart	4
xlPie	Pie Chart	5
xlRadar	Radar Chart	‚ 4151
xlXYScatter	XY Scatter Chart	‚ 4169
xlCombination	Combination Chart	‚ 4111
xl3DArea	3-D Area Chart	‚ 4098
xl3DBar	3-D Bar Chart	‚ 4099
xl3DColumn	3-D Column Chart	‚ 4100
xl3DLine	3-D Line Chart	‚ 4101
xl3DPie	3-D Pie Chart	‚ 4102
xl3DSurface	3-D Surface Chart	‚ 4103
xlDoughnut	Doughnut Chart	‚ 4120

For the PlotBy property, you use the following parameters:

 xlRows = 1 
 xlColumns = 2 

With some of these charts, it is probably best to use the Chart Wizard inside the spreadsheet to see what the options are and what the results look like before you write the VBA code to do this.

So far I have only dealt with creating a chart sheet to display the chart on its own, but you can also create the chart on your spreadsheet, although it gets more complicated:

 Sub test_chart() 
 Dim c As Excel.Chart, a As Worksheet, co As ChartObjects 
 Set a = worksheets("sheet1") 
 Set co = a.ChartObjects 
 Set c = co.Add(60, 60, 300, 300).Chart 
 co.Select 
 c.ChartWizard PlotBy:=xlRows, Source:="north", gallery:=xl3DPie,  _ 
 Format:=7,  Title:="MyChart", categorylabels:=1 
 
 End Sub 

This code is similar to the previous example, and the ChartWizard method is still used to create the chart, but it uses more objects.

On the first line of code, you create an object for a worksheet. You then create an object to define a ChartObjects collection. Next, you set the Worksheet object to point at the worksheet where you want the chart to appear. You set the ChartObjects collection to point to the ChartObjects collection for that worksheet. You then add a new chart, but you must specify coordinates that define where you want it to appear on the spreadsheet, as well as its height and width. These coordinates are not in rows and columns but in chart coordinate numbers .

The Chart object is then selected; otherwise , it is not visible until the user moves the cursor. The ChartWizard method then creates the chart in the same manner as before. Run this code, and you will get the result shown in Figure 15-3.

Figure 15-3: A chart incorporated into a spreadsheet using VBA