Label Last Point for Excel 2007
by Jon Peltier
Monday, April 6th, 2009
Peltier Technical Services, Inc., Copyright © 2012.
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
- Calculate Nice Axis Scales in Excel VBA
- Connect Two XY Series with Arrows (VBA)
- Connect Two XY Series with Arrows – 2007 Error
- Label Last Point – Updated Add-In
- Stack Columns In Order Of Size With VBA
Posted: Monday, April 6th, 2009 under Excel 2007.
Comments: 21
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.
Comment from Jeff Weir
Time: Tuesday, October 5, 2010, 11:13 pm
Hi Jon. I’ve been trying to amend your code so that if one data label obscures another, then the one is moved up and the other moved down just a little. But I’m having no luck with this. I wondered if you might be able to steer me in the right direction.
Here’s what I’ve added to your code, after your final ‘END IF’. Pretty inelegent, I know. Very wrong too. An earlier version seemed to work on every second pass, but I’ve lost the exact syntax of that one now. Any idea where I might have gone wrong, or a more elegent way of writing this code? (note that I’ve hardcoded in the point number, which I got from the macro recorder using excel 2003).
Dim label_spacing As Long
Dim label_1_top As Long
Dim label_2_top As Long
label_1_top = ActiveSheet.ChartObjects(“Chart 1″).Chart.SeriesCollection(1).Points(29).DataLabel.Top
label_2_top = ActiveSheet.ChartObjects(“Chart 1″).Chart.SeriesCollection(2).Points(29).DataLabel.Top
label_spacing = Abs(label_1_top – label_2_top)
If label_spacing label_2_top Then
ActiveSheet.ChartObjects(“Chart 1″).Chart.SeriesCollection(1).Points(29).DataLabel.Top = label_1_top + 10 – (label_spacing / 2)
ActiveSheet.ChartObjects(“Chart 1″).Chart.SeriesCollection(2).Points(29).DataLabel.Top = label_2_top – 10 – (label_spacing / 2)
Else:
ActiveSheet.ChartObjects(“Chart 1″).Chart.SeriesCollection(1).Points(29).DataLabel.Top = label_1_top + 10 – (label_spacing / 2)
ActiveSheet.ChartObjects(“Chart 1″).Chart.SeriesCollection(2).Points(29).DataLabel.Top = label_2_top – 10 – (label_spacing / 2)
End If
End If
Comment from Jon Peltier
Time: Wednesday, October 6, 2010, 7:44 am
Jeff -
This doesn’t seem right:
If label_spacing label_2_top ThenI think you want
If label_spacing < label_2_height Thenexcept that label heights and widths are a pain to compute. So a constant like LABEL_HEIGHT = 10 might be reasonable. Also the dimensions are in points, so let's not use Longs.
Here's what my first-draft pseudocode would look like:
Const LABEL_HEIGHT As Double = 10
Dim label_1_top As Double
Dim label_2_top As Double
Dim label_spacing As Double
Dim cht As Chart
Set cht = ActiveSheet.ChartObjects("Chart 1").Chart
label_1_top = cht.SeriesCollection(1).Points(29).DataLabel.Top
label_2_top = cht.SeriesCollection(2).Points(29).DataLabel.Top
label_spacing = Abs(label_1_top – label_2_top)
If label_spacing < LABEL_HEIGHT then
If label_1_top >= label_2_top Then
cht.SeriesCollection(1).Points(29).DataLabel.Top = label_1_top + (LABEL_HEIGHT - label_spacing) / 2
cht.SeriesCollection(2).Points(29).DataLabel.Top = label_2_top - (LABEL_HEIGHT - label_spacing) / 2
Else
cht.SeriesCollection(1).Points(29).DataLabel.Top = label_1_top - (LABEL_HEIGHT - label_spacing) / 2
cht.SeriesCollection(2).Points(29).DataLabel.Top = label_2_top + (LABEL_HEIGHT - label_spacing) / 2
End If
End If
Comment from Jeff Weir
Time: Wednesday, October 6, 2010, 2:46 pm
Hi again Jon. Thanks for your reply. Not sure how I got “If label_spacing label_2_top Then “. Might have been a typo when I was troubleshooting.
When I run this, I still get the “Method ‘Top’ of object ‘Datalabel’ failed” error that I was getting before. (Apologies: I see I didn’t tell you this in my original comment).
If i hit DEBUG then the line “label_1_top = cht.SeriesCollection(1).Points(29).DataLabel.Top” is highlighted. If I then hit F5 then the code seems to work.
Note that if I go back to your unaltered ‘Label last point’ sub and sneak in the line MsgBox mySrs.Points(iPts).DataLabel.Top after the line AutoText:=True, LegendKey:=False then I get the same error, and again if i hit debug and then F5 it gives me the messagebox.
Any thougts on this?
Also, thanks for showing me how to simplify things with the Dim cht as Chart bit. I never seem to spend enough time with my head in VBA to have stuff like this crystalise.
Comment from Jon Peltier
Time: Wednesday, October 6, 2010, 3:37 pm
Jeff -
You didn’t cite a version of Excel. Sometimes 2007 thinks too slowly, and you get an error, which goes away when you stop and restart (or step through) the code. the trick here is to insert the line “DoEvents” before the line that raises the error. This tells the VBA to wait until Excel and Windows have responded to everything the code has done, like redrawn or recalculated things.
Comment from Jeff Weir
Time: Wednesday, October 6, 2010, 5:37 pm
Hi Jon. I’m working in 2007. Thanks for the tip re DOEVENTS. Unfortunately in this case it doesn’t seem to help: I tried it with both the psuedo code you posted above but no joy. I also tried testing it in the original ‘Label last point’ sub where I pasted this addition before the Exit For line (and after the mySrs.Points(iPts).ApplyDataLabels lines):
DoEvents
MsgBox mySrs.Points(iPts).DataLabel.Top
But I still get the error “Method ‘Top’ of object ‘Datalabel’ failed”
Strangely if I run the psuedo code as its own sub, it works like a charm, with the exception that the series labels no longer appear to be dynamically positioned…that is, if you change the data that the series point to, the chart labels no longer float accordingly to the new position. Which is why I was using the Label Last Point macro to remove and then re-add the labels…so that they change positon when the series change.
So maybe the datalabels created by the Label Last Point macro don’t have a .top dimension? I.e. the dimension only gets created once you move them from their default positon?
Comment from Jon Peltier
Time: Wednesday, October 6, 2010, 7:14 pm
Jeff -
The data labels do have a .Top position, as evidenced by the error going away after it stops and you hit F5.
Why don’t you send me your code (jonpeltier at gmail dot com), I’ll check it out.
Comment from Jeff Weir
Time: Wednesday, October 6, 2010, 9:06 pm
Thanks Jon, will send something through. It turns out that I can run your original ‘label last point sub’ and then run your ‘move overlapped labels’ sub seperately with exactly the desired result. But I can’t run them in one procedure, or call the ‘moved overlapped labels’ sub from the ‘label last point’ sub without getting an error.
Also, a slight amendment to what I said in my last comment: I said that if I run the code on its own, the chart labels no longer appear to move dynamically in response to a change in the chart series.
That’s not actually true…they do move; albeit they still remain ‘offset’ from the series point in question by the about they moved initially due to the code. That is, they have some kind of ‘memory’ of the amount they were moved, and they retain this bias from any subsequent changes i.e. this offset is applied from any new positons that result from a change of the data. Damned if I can work out why.
This is effectively why I wanted to run the ‘label last point’ macro first…to get rid of this bias from the memory.
Comment from Jeff Weir
Time: Wednesday, October 6, 2010, 10:18 pm
Update: I’ve found a simpler way to achieve what I wanted by not running the ‘label last points’ macro but just adding in code to delete and then insert new data labels for the points concerned.
Sub move_overlapped_data_labels()
‘this bit checks for any overlap of the labels, then moves one up and one down accordingly
Const LABEL_HEIGHT As Double = 25
Dim label_spacing As Double
Dim cht As Chart
Dim datalabel_1 As DataLabel
Dim datalabel_2 As DataLabel
Set cht = ActiveSheet.ChartObjects(“Chart 1″).Chart
cht.SeriesCollection(1).DataLabels.Delete
cht.SeriesCollection(1).Points(29).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
cht.SeriesCollection(2).DataLabels.Delete
cht.SeriesCollection(2).Points(29).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
Set datalabel_1 = cht.SeriesCollection(1).Points(29).DataLabel
Set datalabel_2 = cht.SeriesCollection(2).Points(29).DataLabel
DoEvents
label_spacing = Abs(datalabel_1.Top – datalabel_2.Top)
datalabel_1.Font.ColorIndex = 5
datalabel_2.Font.ColorIndex = 3
If label_spacing = datalabel_2.Top Then
‘MsgBox cht.SeriesCollection(1).Points(29).DataLabel.Top
datalabel_1.Top = datalabel_1.Top + (LABEL_HEIGHT – label_spacing) / 2
datalabel_2.Top = datalabel_2.Top – (LABEL_HEIGHT – label_spacing) / 2
Else
datalabel_2.Top = datalabel_2.Top + (LABEL_HEIGHT – label_spacing) / 2
datalabel_1.Top = datalabel_1.Top – (LABEL_HEIGHT – label_spacing) / 2
End If
End If
Application.ScreenUpdating = True
End Sub
Comment from Jeff Weir
Time: Wednesday, October 6, 2010, 10:29 pm
Think I found the problem, but I don’t know why it should be a problem.
In the above code I accidentally left out Application.ScreenUpdating = False at the top of the procedure, with the consequence that you can see the data labels being created and then moved.
While this looks cool, I thought I’d hide all the action from the user. But as soon as I put in Application.ScreenUpdating = False then excel bombs out with the “Method ‘Top’ of object ‘Datalabel’ failed” error.
Why the heck would that be?
Comment from Jon Peltier
Time: Thursday, October 7, 2010, 12:46 pm
Jeff -
That’s because in Excel 2007 some chart/vba things don’t work if screenupdating is turned off. This has caused me a boatload of grief with programs that once ran smoothly and quickly, but which now are forced to run in a herky-jerk manner, much more slowly than ever before, despite faster computers.
Comment from Jeff Weir
Time: Sunday, October 10, 2010, 4:34 pm
Hi again Jon. If I leave the screen-updating option on, then I can call the sub from another macro or I can run it directly from the code window with F5 and it works fine.
But if I call it from an event handler or run it from the macro menu (i.e. alt – F8), then I get that pesky error again despite having DOEVENTS before the offending line.
Have you had similar issues when running a sub from an eventhandler routine that otherwise works fine?
Comment from Jon Peltier
Time: Monday, October 11, 2010, 8:53 am
Jeff -
When I’ve had this issue, it’s when the code is launched from a ribbon control in 2007 or using F5, or called from another routine which was launched the same way. Sometimes DoEvents has no effect.
Comment from jeff weir
Time: Monday, December 13, 2010, 8:56 pm
Hi Jon. Here’s a small tweak that might save a few minutes over our working lives….In the 2003 version of your labellastpoint sub I’ve added this line after the ‘applydatalabels’ bit:
mySrs.DataLabels.Font.Color = mySrs.Border.Color
…which means the text color of the data labels always matches the line color of the appropriate series.
Haven’t tested this in 2007 (only have 2003 at work).
Comment from Jeff Weir
Time: Monday, March 7, 2011, 4:32 pm
Hi Jon. Me again. Thought I’d share my other minor tweak to this macro, which is to add If ActiveSheet.ChartObjects.Count = 1 Then ActiveSheet.ChartObjects(1).Select. I got tired of dismissing the dialog box after forgetting to select the only chart in the sheet!
This macro has got to be my most used macro. It’s a keeper.
Comment from brooklynspo
Time: Tuesday, June 7, 2011, 4:20 pm
Thank you for the great macro.
Comment from M Bolton
Time: Wednesday, June 15, 2011, 10:54 am
Hi Jon, great macro! With over 30 graphs that are constantly updated, the idea of manually updating the last data label for each had me near tears…
Just one thing, when I run the macro it removes the custom legend series I have in place. Is there any any way the legend can be left alone?
Thanks!
Comment from Jon Peltier
Time: Wednesday, June 15, 2011, 11:18 am
You can delete the next to last line of code, the one that says:
cht.HasLegend = False
This line deletes the legend, so removing the line means the legend will be left intact.
Comment from M Bolton
Time: Wednesday, June 15, 2011, 12:26 pm
Thanks Jon! you’re a life-saver!
for reference, i also used ‘mySrs.DataLabels.Font.Color = mySrs.Border.Color’ on 2007 and it worked as expected.
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.