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