Chart Wizard Method

The ChartWizard method modifies the properties of a chart. Note that, unlike the PivotTable wizard, the ChartWizard method does not create a chart. The ChartWizard method is useful for applying several formatting properties to a chart at one time. The method changes only the properties that are specified by the parameters that are included in the call to the method.

The syntax for the ChartWizard method is:

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

Note that all parameters are optional.

The Source parameter is the range that contains the source data for the chart. If Source is omitted, then Excel will use the selected embedded chart or the active chart sheet. If no embedded chart is selected and no chart sheet is active, then an error will result.

The Gallery parameter specifies a general chart type and can be one of the following

XlChartType constants: xlArea, xlBar, xlColumn , xlLine, xlPie, xlRadar, xlXYScatter, xlCombination,xl3DArea,xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, or xlDefaultAutoFormat.

The Format parameter specifies the specific chart type, given the value of Gallery. The value of Format can be a number from 1 through 10, depending on the gallery type. Note that this value corresponds to the chart types in the Chart Format dialog. If this argument is omitted, Excel will select a value based on the gallery type and data source.

The PlotBy parameter specifies whether the data for each series is in rows or columns. It can be one of the values xlRows or xlColumns.

The CategoryLabels parameter is an integer that specifies the number of rows or columns within the source range that contain category labels. It can be any value from 0 through one less than the maximum number of categories or series.

Similarly, SeriesLabels is an integer that specifies the number of rows or columns within the source range that contain series labels. It can be any value from 0 through one less than the maximum number of categories or series.

The HasLegend parameter should be set to True to include a chart legend.

The Title parameter should be set to the chart title text. Similarly, CategoryTitle is the category axis title text, ValueTitle is the value axis title text, and ExtraTitle is the series axis title for 3-D charts or the second value axis title for 2-D charts with a second value axis.

To illustrate, imagine that the chart in Figure 21-30 is the active chart The following code reformats the chart in Figure 21-30, as shown in Figure 21-31:

ActiveChart.ChartWizard Gallery:=xlLine,

Format:=1, HasLegend:=True, Title:="Averages", CategoryTitle:="Grades", ValueTitle:="Average".

Figure 21-30. Illustrating the ChartWizard method

Figure 21-30. Illustrating the ChartWizard method

Figure 21-31. Results of the ChartWizard method

21.16.2 Export Method

The Export method exports a chart in a graphic format. The syntax is:

ChartObject.Export(FileName, FilterName, Interactive)

Here FileName is the name of the graphic file to create. FilterName is the name of the graphic filter as it appears in the registry. Microsoft does not say where in the registry, but it seems likely to be the key:

HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\Graphics Filters\Export

The Interactive parameter can be set to True to display the dialog box that contains filter-specific options. If this argument is False (the default), Excel uses the default values for the filter.

To illustrate, the following code creates three graphic files from the active chart:

ActiveChart.Export "d:\excel\test.png", "PNG" ActiveChart.Export "d:\excel\test.jpg", "JPEG" ActiveChart.Export "d:\excel\test.gif", "GIF"

Note that in these cases, setting Interactive to True seems to have no effect. Note also that any file of the name FileName will be overwritten without warning.

0 0

Post a comment