Apply Chart Wizard Settings To A Chart

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

When you create a new chart within Excel, the Chart Wizard appears to step you through the process and requires that you specify numerous properties such as the chart location and the chart data values. With VBA, you can use the ChartWizard method to quickly format a chart without the need to set each individual property.

You use the ChartWizard method with a specific Chart object. This method includes eleven different optional parameters, which you can only use with this method and which enable you to set properties for the chart: Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, and ExtraTitle. You must set any additional properties individually.

You use the Source parameter to specify or modify any valid range of data that creates the chart. Keep in mind that when you work with a chart sheet, you must specify the

APPLY CHART WIZARD SETTINGS TO A CHART

name of the worksheet containing the data. See Chapter 11 for more information on defining cell ranges.

You use the Gallery parameter to specify one of the xlChartType constant values to indicate the desired chart type. Specify a value of 1 to 10 for the Format parameter to use one of the built-in formats for the selected chart type.

You use an xlRowCol constant value of xlRows or xlColumns for the PlotBy parameter, which determines whether the data series is in rows or columns within the specified range.You stipulate an integer value for the CategoryLabels and SeriesLabels parameters to indicate the number of category and series labels, respectively. You state a value of True for HasLegend parameter if you want a chart legend.

You enter the chart title as the value of the Title parameter, and use the CategoryTitle and ValueTitle parameters to stipulate category and value axis titles. For a 3-D chart, specify a series axis title for the ExtraTitle parameter.

APPLY CHART WIZARD SETTINGS TO A CHART

—D Create a new subroutine.

L0 Type Dim SelectChart As Chart, replacing SelectChart with the chart variable.

L"H Type Set SelectChart = ThisWorkbook.Charts ("Chart2"), replacing ThisWorkbook.Charts ("Chart2") with the chart reference.

—Q Type SelectChart.ChartWizard.

—D Create a new subroutine.

L0 Type Dim SelectChart As Chart, replacing SelectChart with the chart variable.

L"H Type Set SelectChart = ThisWorkbook.Charts ("Chart2"), replacing ThisWorkbook.Charts ("Chart2") with the chart reference.

—Q Type SelectChart.ChartWizard.

WORKING WITH CHARTS

Kxl

ra

CONSTANT

DESCRIPTION

xlArea

Plots individual values and colors in an area to emphasize data series.

xlBar

Displays data in horizontal columns to illustrate data relationships.

xlColumn

Displays data in vertical columns to illustrate data relationships.

xlLine

Plots individual data values on a continuous line to illustrate data trends.

xlPie

Displays each data in relationship to the entire whole.

xlRadar

Plots each data seris on a separate axis.

xlXYScatter

Plots multiple data sources across uneven time frames.

xlCombination

Creates a combination chart.

xl3DLine

Plots data on a continuous line with a 3-D representation.

xl3DPie

Displays data relationships in relation to the entire pie with a 3-D representation.

xl3DSurface

Plots data values to create a 3-D topographical-looking chart.

xlDoughnut

Displays data values as a relationship to the entire circle.

—Q Type CategoryLabels:=True.

• Switch to Excel and run L-■ Excel modifies the selected the macro. chart using the specified values ype Gallery:=xl3DLine, replacing xl3DLine with the appropriate XlChartType constant.

—0 Type Format:=2, replacing 2 with a value between 1 and 10 indicating the built-in format.

—Q Type CategoryLabels:=True.

—O Type any additional ChartWizard parameter values.

• Switch to Excel and run L-■ Excel modifies the selected the macro. chart using the specified values

Was this article helpful?

+1 0

Responses

Post a comment