Programming Excel with VBA and .NET

Add Titles and Labels

The following Chart objects can have captions that serve as titles or labels:

AxisTitle

ChartTitle

DataLabel

DisplayUnitLabel

To add a caption to one of these objects in code, follow these general steps:

1. Make sure the object exists. For example, to ensure ChartTitle exists, set the HasTitle property to True.

2. Set the Caption property of the title.

3. Refresh the chart to display the changes. This step is not always required, but since charts are not always immediately updated, adding a Refresh statement is good insurance.

Tip

Setting the Caption property and setting Characters.Text (shown earlier) are equivalent. Excel records setting chart titles using Characters.Text, but you can change that to make it shorter.

The following code adds captions to the chart and primary axes:

Sub AddTitles( ) Dim chrt As Chart, ax As Axis Set chrt = Charts("Demo Chart Types") chrt.ChartType = xl3DBarStacked chrt.Activate ' Make sure ChartTitle exists chrt.HasTitle = True ' Set caption chrt.ChartTitle.Caption = "Total Four-Year Appreciation" ' Make sure axis exists chrt.HasAxis(xlValue, xlPrimary) = True Set ax = chrt.Axes(xlValue, xlPrimary) ' Make sure AxisTitle exists ax.HasTitle = True ' Set caption ax.AxisTitle.Caption = "Primary Value Axis" ax.AxisTitle.Orientation = xlHorizontal chrt.HasAxis(xlValue, xlPrimary) = True Set ax = chrt.Axes(xlCategory, xlPrimary) ax.HasTitle = True ax.AxisTitle.Caption = "Primary Category Axis" ax.AxisTitle.Orientation = xlUpward ...