Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

One of the most frequent complaints about Excel's charting is its inflexible data labeling feature. For example, consider the XY chart in Figure 18-11. It would be useful to display the associated name for each data point. However, you can search all day, and you'll never find the Excel command that lets you do this automatically. Such a command doesn't exist. Data labels are limited to the data values only - unless you want to edit each data label manually and replace it with text (or a formula) of your choice.

Figure 18-11: An XY chart with no data labels.

The DataLabelsFromRange procedure works with the first chart on the active sheet. It prompts the user for a range and then loops through the Points collection and changes the Text property to the values found in the range.

Sub DataLabelsFromRange() Dim DLRange As Range Dim Cht As Chart Dim i As Integer, Pts As Integer ' Specify chart Set Cht = ActiveSheet.ChartObjects(1).Chart ' Prompt for a range On Error Resume Next Set DLRange = Application.InputBox _ (prompt:="Range for data labels?", Type:=8) If DLRange Is Nothing Then Exit Sub On Error GoTo 0 ' Add data labels Cht.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, _ LegendKey:=False ' Loop through the Points, and set the data labels Pts = Cht.SeriesCollection(1).Points.Count For i = 1 To Pts Cht.SeriesCollection(1). _ Points(i).DataLabel.Text = DLRange(i) Next i End Sub

CD-ROM

This example, named data labels.xlsm , is available on the companion CD-ROM.

Figure 18-12 shows the chart after running the DataLabelsFromRange procedure and specifying A2:A9 as the data range.

Figure 18-12: This XY chart has data labels, thanks to a VBA procedure.

A data label in a chart can also consist of a link to a cell . To modify the DataLabelsFromRange procedure so it creates cell links, just change the statement within the For-Next loop to:

Cht.SeriesCollection(1).Points(i).DataLabel.Text = _ "=" & "'" & DLRange.Parent.Name & "'!" & _ DLRange(i).Address(ReferenceStyle:=xlR1C1)

Note	The preceding procedure is rather crude and does very little error checking. In addition, it only works with the first `Series` object. The Power Utility Pak add-in (which you can obtain by using the coupon in the back of the book) includes a much more sophisticated chart data “labeling utility.

Категории