cellMatrix.net

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