Datalabel height and width for 2007 or earlier.

Over at Chart LeaderLines in Excel 2010 or earlier I posted some code that draws leader-lines on charts just like Excel 2013 does.

Unfortunately that title was misleading in regards to the or earlier bit: Eric said that the code isn’t working at all in XL07, and Jon Acampora advised that the DataLabel.Height and DataLabel.Width properties are not available in XL07.

Andy Pope had a crafty workaround for this:

The trick to getting datalabel width and height is to force the data label off of the chart by setting left and top to chartarea width and height. The data labels will not actually go out of the chart so by reading the new left and top properties you can calculate the differences.

So I whipped up some functions to get the datalabel height and width:

Function dlHeight_2010(dl As DataLabel)
    dlHeight_2010 = dl.Height
End Function

Function dlWidth_2010(dl As DataLabel)
    dlWidth_2010 = dl.Width
End Function

Function dlHeight_Pre2010(dl As DataLabel)
    Dim dlTop As Long
    dlTop = dl.Top
    dl.Top = ActiveChart.ChartArea.Height
    dlHeight_Pre2010 = dl.Top - ActiveChart.ChartArea.Top
    dl.Top = dlTop
End Function

Function dlwidth_Pre2010(dl As DataLabel)
Dim dlleft As Long
    dlleft = dl.Left
    dl.Left = ActiveChart.ChartArea.Width
    dlwidth_Pre2010 = dl.Left - ActiveChart.ChartArea.Left
    dl.Left = dlleft
End Function

They are all separate functions because if I lumped them together in one, it wouldn’t compile on pre-2010 machines. So I call these from the main code with this:

If Application.Version = 14 Then
  dlHeight_2010 dl
  dlWidth_2010 dl

ElseIf Application.Version < 14 Then
    dlHeight_Pre2010 dl
    dlwidth_Pre2010 dl
End If

I’ve updated my leaderlines code to use these. If I comment out the stuff relating to 2010 and force Excel to use the pre2010 functions then it seems to work perfectly. But I asked a buddy to try it in his 2007 installation, and he advises that it doesn’t work…it just deletes the chart leader lines without redrawing them.

Anyone with 2007 or earlier fancy taking this for a spin, and advising where I might have gone off the rails?

Mucho Gracious.
Leader lines_20140225 v7

13 thoughts on “Datalabel height and width for 2007 or earlier.

  1. Andy Pope

    Another couple of properties that are not available prior to 2010 are the Left and Top values of the data point.

    If you want to know the position of the data point you need to get creative. Having determined the width and height of the data label you can then position the label left/right and above/below and calculate the data point.

    It will get messy quite quickly, you will probably need to refresh the chart and leave screen updating on.
    Another problem is data labels that are too large AND too close to the edge that when you do move them you do not get a correct calculated value.

  2. Oli

    Hi Jeff. On my 64 bit Win 7 machine using 32 bit 2007 they display on opening but clicking the chart area makes them all dispear from that chart. Moving a chart label does nothing to make one or all redraw. Same machine with 32 bit 2010 all works as expected. Same machine with 32 bit 2013 (standalone) acts unexpectedly. The top chart 2 out of the 5 lines do not show or redraw (the blue and orange segments) and the bottom chart 1 of the 5 lines does not show or redraw (the blue segment). The rest act as expected. Confused?

  3. Jeff Weir Post author

    Hi Andy. So the datalabel Left and Top values are available, but the data point left and top are not? I’m not quite following this: Having determined the width and height of the data label you can then position the label left/right and above/below and calculate the data point.

    Thanks Oli.

  4. Jeff Weir Post author

    Andy: Penny has just dropped. So for instance I could use this:
    dl.Position = xlLabelPositionCenter
    …which puts the middle of the data label smack bang over the middle of the point (assuming we’re not up against the plot edge) and then calculate the point’s location from, dl.bottom, and the height and width I’d already ascertained.

  5. Andy Pope

    That’s right Jeff. You need to remember the labels position and move it around to get size and positional information before finally resetting it’s position and generating the leader lines.

  6. Jon Peltier

    Re: Andy’s note about getting the point’s position. Don’t spoil your afternoon moving datalabels around. If it’s an XY chart, then a little algebra goes a long way:

    Horiz Position = plotarea.insideleft + plotarea.insidewidth*(X – Xmin)/(Xmax-Xmin)
    Vert Position = plotarea.insidetop + plotarea.insideheight*(Ymax-Y)/(Ymax-Ymin)

    with corrections for plotting an axis in reverse order.

    If it’s a line chart, the vertical position is as above, the horizontal position uses category number, total number of categories, and a correction for whether the axis crosses on or between categories.

    If it’s a bar or column chart, you can get the length of the bar using the above (vert or horiz for column or bar chart), and if it’s stacked you need to sum them up appropriately. The width needs to take into account gap width, and if it’s clustered, how many series there are across each category.

    Good topic for a blog post of my own…

  7. Jon Acampora

    Thanks Jeff! 2007 didn’t make it easy on us. I chose to ignore this on the Chart Alignment tool, at least for the time being, because of all the checks you would have to do. Plus, it’s not as important for that tool.

    I am looking forward to Jon’s blog post though! I’m learning a lot of new stuff here. :)

  8. Pingback: Daily Dose of Excel » Blog Archive » DataPoint Top and Left for 2007 or earlier.

  9. Ross Dillon

    I believe the problem folks are having in Excel 2007 is that the mouseup event does not work at all…at least not from my research or (albeit limited) programming experience. I would love some smart guy to figure out how to to leader lines in Excel 2007 without the mouseup event. I desperately need something like that but the company won’t invest in upgrading from office 2007…(same post as I made in the original thread)

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see