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

12 Comments

  1. Andy Pope says:

    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 says:

    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 says:

    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 says:

    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.top, dl.bottom, and the height and width I’d already ascertained.

  5. Andy Pope says:

    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 says:

    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. Jeff Weir says:

    Jon…of course! You’ll make a champion charter out of me yet ;-)

  8. Jon Acampora says:

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

  9. Jeff Weir says:

    Yep, great that Peltier is here for us noobs…just goes to show that an old dog can teach new tricks.

  10. Jeff Weir says:

    Can anyone confirm whether Activechart.ChartArea.Width returns anything in XL07?

  11. Oli says:

    Yes Activechart.ChartArea.Width returns a value in XL07.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: