Over at Datalabel height and width for 2007 or earlier 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.
Then Jon Peltier says:
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.
All those potential Select Case statements that Jon will have to use give me the heebie-jeebies. So while I keenly await his forthcoming blog post on how to do things properly, I spent my afternoon being quick and dirty:
Dim ptTop As Long
Dim ptLeft As Long
Dim dlLeft As Long
Dim dlTop As Long
Dim dlHeight As Long
Dim dlWidth As Long
Const lngPadding = 7
With dl
dlTop = .Top
dlLeft = .Left
‘Determine DL width and height
dl.Left = ActiveChart.ChartArea.Width
dlWidth = ActiveChart.ChartArea.Width – dl.Left
dl.Top = ActiveChart.ChartArea.Height
dlHeight = ActiveChart.ChartArea.Height – dl.Top
dl.Position = xlLabelPositionRight
If dl.Left + dlWidth = ActiveChart.ChartArea.Left + ActiveChart.ChartArea.Width Then
‘Datalabel is too wide to fit between point and plot edge
dl.Position = xlLabelPositionLeft
ptLeft = dl.Left + dlWidth + lngPadding
Else:
ptLeft = dl.Left – lngPadding
End If
dl.Position = xlLabelPositionBelow
ptTop = dl.Top – lngPadding
DoEvents
dl.Position = xlLabelPositionAbove
DoEvents
If dl.Top + dlHeight + lngPadding > ptTop Then ptTop = dl.Top + dlHeight + lngPadding
‘Return DataLabel to original position
.Top = dlTop
.Left = dlLeft
End With
Pre2010_Position = dlWidth & "|" & dlHeight & "|" & ptLeft & "|" & ptTop
End Function
To test this, just select a DataLabel and run this:
Dim strPosition As String
Dim dl As DataLabel
Set dl = Selection
strPosition = Pre2010_Position(dl)
Debug.Print "dlWidth: " & Split(strPosition, "|")(0)
Debug.Print "dlHeight: " & Split(strPosition, "|")(1)
Debug.Print "ptLeft: " & Split(strPosition, "|")(2)
Debug.Print "ptTop: " & Split(strPosition, "|")(3)
End Sub
Note that I’ve got a couple of DoEvents in the Pre2010_Position routine. Without them, on my 2013 install it just doesn’t seem to work properly unless you step through the code one line at a time. Tedious, and annoying because you can see everything moving on the graph. But unavoidable, it seems. And tracking this down was what took the most time. Very frustrating.
For instance, without the DoEvents I get this:
dlWidth: 102
dlHeight: 51
ptLeft: 83
ptTop: 97
…whereas with them, I get this:
dlWidth: 102
dlHeight: 51
ptLeft: 83
ptTop: 64
Here’s my revamped LeaderLines file. Anyone with 2007 or earlier fancy taking this for a spin, and advising if it works?
Leader-lines_20140225-v10
