Figure Axesrelated objects

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

The Chart object has an Axes collection that contains an Axis object for each axis in the chart. The Axes method returns either a single axis or the Axes collection for a chart. To return the Axes collection for a chart, use the syntax:

ChartObject.Axes

To return a specific Axis object, use the syntax:

ChartObject .Axes( Type, AxisGroup)

Here the optional Type parameter specifies the axis to return. It can be one of the following

XlAxisType constants:

Enum XlAxisType xlCategory = 1 xlValue = 2 xlSeriesAxis = 3

End Enum

Note that xlSeriesAxis is valid only for 3-D charts.

The optional AxisGroup parameter specifies the axis group. It can be one of the following

XlAxisGroup constants:

Enum XlAxisGroup xlPrimary = 1 ' The default xlSecondary = 2

End Enum

A secondary axis is a second vertical or horizontal axis upon which a second value series (vertical case) or category series (horizontal case) is plotted (see Figure 21-1). If this argument is omitted, the primary group is used. Note that 3-D charts have only one axis group.

The Chart object has a read-write property named HasAxis that determines whether or not the chart displays various types of axes. However, it is important to note that this method will fail if the chart does not yet have the corresponding data series. For instance, if you are creating a chart from scratch, it might be natural to add the code to create a category axis before adding the category data that will be plotted against that axis. This not to do (to quote Hamlet)!

In fact, referring to the CreateChart example procedure earlier in the chapter, if we reverse the data series code and the axes-related code, changing this:

' Add data series using data on the sheet itself co.Chart.SeriesCollection.Add

Source:=ActiveSheet.Range("A1:B6"), Rowcol:=xlColumns, SeriesLabels:=True, Categorylabels:=True

' Add axes With co.Chart

.HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlCategory, xlSecondary) = False .HasAxis(xlValue, xlPrimary) = True .HasAxis(xlValue, xlSecondary) = False End With to this:

' Add axes With co.Chart

.HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlCategory, xlSecondary) = False .HasAxis(xlValue, xlPrimary) = True .HasAxis(xlValue, xlSecondary) = False End With

' Add data series using data on the sheet itself co.Chart.SeriesCollection.Add

Source:=ActiveSheet.Range("A1:B6"), Rowcol:=xlColumns, SeriesLabels:=True, Categorylabels:=True

Excel will issue the completely useless error message: "Method 'HasAxis' of object '_Chart' has failed." (Unfortunately, as is all too often the case, the documentation does not discuss this issue at all.)

The syntax for the HasAxis property is:

ChartObject .HasAxis( Index1, Index2)

where the parameters, despite their generic names, correspond directly to the Type and AxisGroup parameters of the Axes method. For instance, the following code displays a primary category axis and both primary and secondary value axes for the active chart:

With ActiveChart

.HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlCategory, xlSecondary) = False .HasAxis(xlValue, xlPrimary) = True .HasAxis(xlValue, xlSecondary) = True End With

Was this article helpful?

+1 0

Post a comment