Creating a Chart Sheet

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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"

Was this article helpful?

0 0
Ultimate Blackjack System

Ultimate Blackjack System

Discover the 100 Legal Secrets Las Vegas Professional Blackjack Gamblers Use to Make Literally Thousands of Dollars Every Week... I'll Teach You the Ultimate Step-by-Step Blackjack System, Casino's Don't Want You to Know About! That's Right, I'll show you step-by-step how to have the blackjack odds in your favor, guaranteed!

Get My Free Ebook


Responses

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

Post a comment