Label Last Point for Excel 2007
by Jon Peltier
Monday, April 6th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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
Posted: Monday, April 6th, 2009 under Excel 2007.
Comments: 3
Comments
Comment from Mike Woodhouse
Time: Monday, April 6, 2009, 5:35 am
It’s just so typical of Microsoft that they should virtually hamstring themselves in an attempt to provide permanent backwards compatibility for, well, about everything, and then still fail to do so.
There’s a part of me that wishes they’d just announce from time to time that the new version of something is not guaranteed to be 100% compatible with files saved from older versions. Maybe on a less-than-strategic product (Publisher?) at first. Then we (and more importantly MS) could see how much the purchasing universe actually cares. And maybe, just maybe, we’d move forward a little faster.
In the meantime, as you rightly point out, all these shenanigans are a continuing guarantee of income to the Excel professional…
Comment from Matt Healy
Time: Monday, April 6, 2009, 10:07 pm
Slick code, although I tend to pick which point I want to label manually because it’s hard to automate all the relevant considerations, like trying not to obscure data or labels from another series, trying to accentuate high/low/inflection/otherwise interesting points, etc, etc.
One of my favorite tricks is to add a small data series and then label one of its points merely to draw a callout — the big advantage over manually putting a shape there is by using an added data series it will automatically get scaled and positioned appropriately.
Comment from Jon Peltier
Time: Tuesday, April 7, 2009, 5:59 am
Matt -
This code is for relatively simple examples that are amenable to labeling of just the last point. This approach is suitable for many charts. A lot of charts of real world data become too complex for this simple approach, and you have to resort to more complicated techniques. I like the callout method you cite which uses a two-point series, I’ve used it and variations for a long time.



















Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.