For years I’ve been using a handy routine to label the last point of each series in a chart. Not very fancy, but it detects a point that can’t be labeled because the point itself isn’t plotted, for example, if the series values contain a blank or #N/A error. Trying to add a data label to a non-plotted point raises an error, and the program catches the error and tries the next to last point, then the point before that, until the label is added without error.

Anyway, a blog reader emailed me with a problem. The LastPointLabel procedure seemed to run fine, but her chart didn’t show the labels. Could I look at it?

I discovered another of those little inconsistencies which will keep us programmers gainfully employed for years to come. These glitches are frustrating, but I guess if I sell myself as an Excel 2007 Incompatibility Expert, I could probably double my hourly rate. They also gives me plenty to blog about.

What happens in Excel 2007 is that, even though a point is not plotted because of a blank cell or #N/A error, the label is applied without an error. You can’t see it, because the point isn’t plotted. But if you put a valid number into the cell, the point and its label appear. This is actually a nice way to handle labels for unplottable points, but the fact that the behavior is different than that of earlier versions leads to loss of functionality of existing code.

The way around this is to examine the values of the series. In 2007 and 2003, if a cell is blank then the corresponding value of the array of series data is blank. In 2007, an #N/A error also leads to a blank in the data array, whereas in 2003, an #N/A error is represented by “Error 2042″, VBA-speak for the error.

In the corrected code below, the series data is inspected before an attempt is made to label the point. It’s actually more elegant in a way, because the code doesn’t rely on a failure before it does the right thing.

Sub LastPointLabel()
  Dim mySrs As Series
  Dim iPts As Long
  Dim vYVals As Variant
  Dim vXVals As Variant

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation
  Else
    Application.ScreenUpdating = False
    For Each mySrs In ActiveChart.SeriesCollection
      With mySrs
        vYVals = .Values
        vXVals = .XValues
        ' clear existing labels
        .HasDataLabels = False
        For iPts = .Points.Count To 1 Step -1
          If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
              And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then
            ' add label
            mySrs.Points(iPts).ApplyDataLabels _
                ShowSeriesName:=True, _
                ShowCategoryName:=False, ShowValue:=False, _
                AutoText:=True, LegendKey:=False
            Exit For
          End If
        Next
      End With
    Next
    ' legend is now unnecessary
    ActiveChart.HasLegend = False
    Application.ScreenUpdating = True
  End If
End Sub
 

If you’re not sure what to do with this code, please refer to my tutorial, How To Use Someone Else’s Macro.

Related Posts:

• Label Each Series in a Chart
• Connect Two XY Series with Arrows (VBA)
• Connect Two XY Series with Arrows – 2007 Error
• Stack Columns In Order Of Size With VBA
• VBA to Split Data Range into Multiple Chart Series
• Quick VBA Routine: XY Chart with Axis Titles
• Label Last Point – Updated Add-In
• Order of Points in XY and Line Charts
• Mind the Gap – Charting Empty Cells
• Enhanced Export Chart Procedure