Excel VBA Macro Programming

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 :

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:

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