The Chart AxesVBA Name Axis

Each axis in a chart is a member of the Axes collection. The sample chart has two axes (X and Y), known respectively as the category axis and the value axis. The simplest VBA syntax for specifying a given axis is

Cht.Axes(Type)

where Type is an Excel VBA constant specifying the axis. For our chart, the value of Type can be xlCategory (for the X axis) or xlValue (for the Y axis).

Using Cht.Axes without the Type argument returns the entire Axes collection for the chart.

The following code demonstrates how you can use VBA to add axis titles to the chart and set the number format of the Y axis (value axis) to 2 decimals:

Sub AxisDemo()

Dim Axs As Axis

Set Axs = Charts("Chart1").Axes(xlValue) With Axs

.HasTitle = True

.AxisTitle.Caption = "Performance index" .TickLabels.NumberFormat = "0.00" End With

Set Axs = Charts("Chart1").Axes(xlCategory) With Axs

.HasTitle = True

.AxisTitle.Caption = "Year of production" End With End Sub

The resulting chart is shown in Figure 10.12.

Figure 10.12

The number format for the Y axis has been changed and axis titles have been added.

Figure 10.12

The number format for the Y axis has been changed and axis titles have been added.

Note that the plot area is automatically resized to accommodate the axis titles. You cannot set the axis caption unless the HasTitle property for the axis is set to True.

Secondary Axes

If the data series have very different scales, you need to use a secondary axis. This can be done for either or both of the horizontal and vertical axes. In this context, the VBA syntax for specifying a given axis is Cht.Axes(Type, AxisGroup)

0 0

Post a comment