Tie Series Labels to Worksheet Cells
Depending on the version of Excel that you are using, there are two free addins available that allow you to tie the series labels in a chart to worksheet cells. Those addins are:
- The XY Chart Labeler available from Rob Bovey's Applications Professionals site.
- The J-Walk Chart Tools available from John Walkenbach's Spreadsheet Page.
A project that I worked on recently required that I tie the series data labels to one of many range references based on an input by the user. To do so, it was required that I build the same type of functionality that the addins provide directly into my project.
In a post to the Microsoft Excel Programming Newsgroup dating back to July of 1999, John Green describes how to create labels in a chart from the text in a range using VBA. The code originates from the book titled "Excel 2000 VBA Programmer's Reference" written by John Green, Stephen Bullen, and Felipe Martins.
As taken from the post, the code to tie the series labels to a worksheet range looks like this:
Sub AddDataLabels()
Dim SalesSeries As Series
Dim pts As Points
Dim pt As Point
Dim rng As Range
Dim i As Integer
Set rng = Range("B4:G4")
Set SalesSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
SalesSeries.HasDataLabels = True
Set pts = SalesSeries.Points
For Each pt In pts
i = i + 1
pt.DataLabel.Text = "=" & rng.Cells(i).Address _
(RowAbsolute:=True, _
ColumnAbsolute:=True, _
ReferenceStyle:=xlR1C1, _
External:=True)
Next pt
End Sub
After finding the example above, I wanted to see if the code could be streamlined in any way. I first looked up the "Address" property as applied to the range object in Excel's VBA help and found out that the default values of the row and column references are "true". As a result, the Address property could be shortened and rewritten as:
rng.Cells(i).Address(, , xlR1C1, True)
I remembered that in a prior post titled Declaring Line Chart Variables, Jon Peltier suggested a best-practice for declaring variables. Following those suggestions, I renamed the series variable "SalesSeries" to "Srs". I also eliminated the point variable and used a reference to Srs.Points.Count in its place. With those changes the loop could be rewritten as follows:
For i = 1 To Srs.Points.Count
Srs.Points(i).DataLabel.Text = "=" & Rng.Cells(i).Address(, , xlR1C1, True)
Next i
Finally, by making the changes above I was able to simplify the code to look like this:
Sub AddDataLabels()
Dim Srs As Series
Dim Rng As Range
Dim i As Integer
Set Rng = Range("B4:G4")
Set Srs = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
Srs.HasDataLabels = True
For i = 1 To Srs.Points.Count
Srs.Points(i).DataLabel.Text = "=" & Rng.Cells(i).Address(, , xlR1C1, True)
Next i
End Sub
