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.