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
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.
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?
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.
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.
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.
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…
Jon…of course! You’ll make a champion charter out of me yet ;-)
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. :)
Yep, great that Peltier is here for us noobs…just goes to show that an old dog can teach new tricks.
Can anyone confirm whether Activechart.ChartArea.Width returns anything in XL07?
Yes Activechart.ChartArea.Width returns a value in XL07.
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)