Listing Creating a New Chart Using the Chart Wizard Method

' creates a chart using the ChartWizard Method Sub CreateExamp1eChartVersionI() Dim ws As Worksheet Dim rgChartData As Range Dim chrt As Chart

Set ws = ThisWorkbook.Worksheets("Basic Chart") Set rgChartData = ws.Range("B1").CurrentRegion

' create a new empty chart Set chrt = Charts.Add

' embed chart in worksheet - this creates a new object Set chrt = chrt.Location(x1LocationAsObject, ws.Name)

With chrt

' use chart wizard to populate/format empty chart .ChartWizard _

Source:=rgChartData, _ Ga11ery:=x1Co1umn, _ Format:=1, _ P1otBy:=x1Co1umns, _ CategoryLabe1s:=1, _ SeriesLabe1s:=1, _ HasLegend:=True, _

Tit1e:="Gross Domestic Product Version I", _ CategoryTit1e:="Year", _ Va1ueTit1e: = "GDP in billions of $"

End With

Set chrt = Nothing Set rgChartData = Nothing Set ws = Nothing End Sub

You probably already know this, but a chart can assume two forms: it can be created as a chart sheet or it can be embedded within a worksheet. One thing that is kind of odd regarding the process of creating a chart is that you don't have any control over what kind of chart is created when you use Charts.Add. This statement creates an empty chart sheet. This is fine and dandy if you want a chart sheet, but what if you want to embed it into a worksheet? That is what the Location method is for.

YourChartObject.Location([Where], [Name])

The Where parameter can be either xlLocationNewSheet (creates a chart sheet), xlLocationAsObject (embeds in a worksheet), or xlLocationAutomatic. The Name parameter is the name of the new chart sheet if you use xlLocationNewSheet. If you use xlLocationAsObject, the Name parameter should be the name of the worksheet in which you want to embed the chart. Another somewhat peculiar aspect of creating a chart is that Location creates a new Chart object from a programmatic perspective. If you are using a Chart object variable to refer to a chart, you need to reset it when you use Location. That's the reason for the following statement in the CreateExampleChartVersionI procedure.

' embed chart in worksheet - this creates a new object

Set chrt = chrt.Location(x1LocationAsObject, ws.Name)

Now that you have the chart where you want it, it is time to employ the ChartWizard method to give the chart some personality. As you can see in this example, the ChartWizard method has a lot of parameters and you'll probably use most of them. Note, however, that all of the parameters are optional.

YourChartObject.ChartWizard [Source], [Gallery], [Format], _ [PlotBy], [CategoryLabels], [SeriesLabels], [HasLegend], _ [Title], [CategoryTitle], [ValueTitle], [ExtraTitle]

The parameters of the ChartWizard method are detailed in the following list.

Source This is an optional parameter that represents the range that contains the source data for the chart.

Gallery Gallery is the type of chart to create. Choose from xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, or xlDefaultAutoFormat.

Format The option number for the type of chart you specified using Gallery. This can be a number from 1 through 10 depending on the type of chart.

PlotBy Use xlRows or xlColumns to specify how the data is oriented.

CategoryLabels Use this parameter to indicate the number of rows or columns in the source data range that consist of category labels.

SeriesLabels Use this parameter to indicate the number of rows of columns in the source data range that contain series labels.

HasLegend HasLegend should be true to include a legend, false to exclude it. Title The title of the chart can be specified with this parameter. CategoryTitle The category axis title. ValueTitle The value axis title.

ExtraTitle The series title for 3-D charts or the second value axis title for 2-D charts.

Figure 10.16 shows an example of a chart created using the ChartWizard method. This chart was generated using the CreateExampleChartVersion1 procedure from Listing 10.9.

Figure 10.16

This is the chart created by Listing 10.9.

Figure 10.16 shows an example of a chart created using the ChartWizard method. This chart was generated using the CreateExampleChartVersion1 procedure from Listing 10.9.

You don't necessarily need to use the ChartWizard method to create your chart. Well, technically the ChartWizard doesn't create the chart, but it certainly does all of the heavy lifting. Another way to achieve the same result is to do it yourself using other properties and methods of the Chart object. Take a look at Listing 10.10.

0 -1

Responses

  • juuso
    How to use chartwizard method?
    6 years ago

Post a comment