Creating a Chart Sheet

The sub procedure AddChartSheet() creates a new chart sheet and a column chart of sample data selected from a worksheet by the user.

The worksheet range that contains the data is selected via a custom dialog box using methods discussed in Chapter 6. The Add() method of the Charts collection object is used to create a column chart on a new chart sheet. Remember, the Charts collection object represents a collection of chart sheets in a workbook (refer to Table 9.1). After the chart sheet is added, the chart it contains is automatically active because it is the only component of the sheet. Next, a With/End With structure is used to modify the properties of the Chart object. Many of these subordinate objects and properties have common sense names, so their function is intuitive.

Public Sub AddChartSheet() Dim dataRange As Range

Set dataRange = Range(frmDataRange.txtDataRange.Text) frmDataRange.Hide

Charts.Add With ActiveChart

.ChartType = xlColumnClustered .HasLegend = True .Legend.Position = xlRight

Axes(xlCategory).MinorTickMark = xlOutside Axes(xlValue).MinorTickMark = xlOutside

Use Excel worksheet function to set the maximum scale on the value axis.

.Axes(xlValue).MaximumScale = Application.WorksheetFunction. _ RoundUp(Application.WorksheetFunction. _ Max(dataRange), -1) .Axes(xlCategory).HasTitle = True

.Axes(xlCategory).AxisTitle.Characters.Text = "X-axis Labels" .Axes(xlValue).HasTitle = True

.Axes(xlValue).AxisTitle.Characters.Text = "Y-axis"

.SeriesCollection(1).Name = "Sample Data" .SeriesCollection(1).Values = dataRange End With End Sub

In the AddChartSheet() sub procedure, a specific Axis object is returned from the Axes collection object by passing a defined constant with the Axes() method. The Axes() method returns an Axis object and takes up to two parameters: one for the axis type (xlCategory, xlSeries, or xlValue), and another for the axis group (xlPrimary or xlSecondary). The axis type xlCategory represents the x-axis on the chart, and xlValue represents the y-axis. The axis type xlSeries applies only to 3D charts and represents the z-axis. The axis group is either xlPrimary (default) or xlSecondary (applies to charts containing multiple Series objects).

The rest of the objects and properties set via the Axis object are fairly straightforward and include setting tick marks and chart labels. The upper limit of the y-axis scale is set using Excel worksheet functions that return the maximum value from the variable dataRange (defined at the beginning of the procedure) rounded up to single-digit precision.

The data is finally added to the chart by setting the Values property of the Series object (returned from the SeriesCollection collection object) with the range variable dataRange.

Figure 9.8 shows the components specifically added to the chart by the preceding code. The chart also contains components created from default properties of the various chart related objects. For example, the gridlines in the figure are the major gridlines on the y-axis and are displayed by default. To prevent them from being displayed, I could have added a statement such as ActiveChart.Axes(xlValue).MajorGridlines = False.

MaximumScale

ChartType xlColumnClustered

MinorTickMark = xlOutside

The column chart created by the

AddChartSheet() sub procedure.

MaximumScale

ChartType xlColumnClustered

MinorTickMark = xlOutside

The column chart created by the

AddChartSheet() sub procedure.

SeriesCollection(l) Values

HasLegend = True

Legend.Position = xlRight

SeriesCollection(l).

Name="Sample

Data"

HasTitle = True

AxisTitle.Characters. Text = "X-axis Labels"

SeriesCollection(l) Values

HasLegend = True

Legend.Position = xlRight

SeriesCollection(l).

Name="Sample

Data"

HasTitle = True

AxisTitle.Characters. Text = "X-axis Labels"

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

  • laila
    What is xlcategory in axes funtion of vba?
    4 years ago

Post a comment