Label Last Point for Excel 2007

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.

Peltier Tech Chart Utility

Comments

  1. 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…

  2. 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.

  3. 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.

  4. 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

  5. Jeff –

    This doesn’t seem right:

    If label_spacing label_2_top Then

    I think you want

    If label_spacing < label_2_height Then

    except 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
  6. 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.

  7. 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.

  8. 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?

  9. 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.

  10. 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.

  11. 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

  12. 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?

  13. 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.

  14. 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?

  15. 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.

  16. 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).

  17. 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.

  18. Thank you for the great macro.

  19. 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!

  20. 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.

  21. 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.

  22. If you select each line and then click on the very end datapoint, you will get the white cross which allows you to select only the end datapoint. Then right-click and select “Format Data Label”. No macro, no fuss. :)

  23. Sure, no macro no fuss. And it takes fifteen seconds per series, times M series per chart, times N charts per sheet. The macro is one click for as many charts as you have selected. So maybe 15 seconds total to select the charts and click the button.

  24. …not to mention the fact that this macro can be automated so that it takes just seconds to process a large dashboard with hundreds of series when the file opens. Macros, no fuss.

Subscribe without commenting

Trackbacks

  1. […] modified a version of Jon Peltier’s great Label Last Point routine to refresh the placement of the data labels. (Thanks, Jon). Here’s the template, so […]

  2. […] modified a version of Jon Peltier’s great Label Last Point routine to refresh the placement of the data labels. (Thanks, Jon). Here’s the template, so […]

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.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.