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:
Function Pre2010_Position(dl As DataLabel) As String
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:
Sub test()
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
Unfortunately it does not.
In xl2007 and xl2010 ChartArea.Left returns the same value as Chartobject().left, which is the left position of the object.
In xl2003 ChartArea.Left was as few points inside the left position of the object.
Couple of other fixes required. To get the correct values you need to leave screen updating on and refresh the chart.
To avoid too much screen flashing you might want to store the labels positions and only update those which have change position or data value.
Private Sub clsChart_Mouseup(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim IDNum As Long
Dim lngSeries As Long
Dim lngPoint As Long
Dim shp As Shape
On Error Resume Next 'This is in case we are dealing with a trendline equation/r-squared box
clsChart.GetChartElement x, y, IDNum, lngSeries, lngPoint
If Err.Number = 0 Then
If IDNum = xlDataLabel Then DrawLeaderLine clsChart, lngSeries, lngPoint
If IDNum = xlPlotArea Then 'If user changed dimensions of PlotArea, then all need to be redrawn.
'' Application.ScreenUpdating = False
For Each shp In clsChart.Shapes
If Left(shp.Name, 10) = "LeaderLine" Then
DrawLeaderLine clsChart, CLng(Split(shp.Name, "_")(1)), CLng(Split(shp.Name, "_")(2))
End If
Next shp
'' Application.ScreenUpdating = True
End If
End If
End Sub
Function XLPre2010_Position(dl As DataLabel) As String
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
DoEvents
clsChart.Refresh
dlWidth = ActiveChart.ChartArea.Width - dl.Left
dl.Top = ActiveChart.ChartArea.Height
DoEvents
clsChart.Refresh
dlHeight = ActiveChart.ChartArea.Height - dl.Top
dl.Position = xlLabelPositionRight
DoEvents
clsChart.Refresh
If dl.Left + dlWidth = ActiveChart.ChartArea.Width Then
'Datalabel is too wide to fit between point and plot edge
dl.Position = xlLabelPositionLeft
DoEvents
clsChart.Refresh
ptLeft = dl.Left + dlWidth + lngPadding
Else:
ptLeft = dl.Left - lngPadding
End If
dl.Position = xlLabelPositionBelow
DoEvents
clsChart.Refresh
ptTop = dl.Top - lngPadding
dl.Position = xlLabelPositionAbove
DoEvents
clsChart.Refresh
If dl.Top + dlHeight + lngPadding > ptTop Then ptTop = dl.Top + dlHeight + lngPadding
'Return DataLabel to original position
.Top = dlTop
.Left = dlLeft
DoEvents
clsChart.Refresh
End With
XLPre2010_Position = dlWidth & "|" & dlHeight & "|" & ptLeft & "|" & ptTop
End Function
Oh my! So with those changes, does it work in XL07?
Just one thing !
In a lot of Worksheets in the web that use VBA to check the version of excel, there is a bug in French version.
When i open the Leader-lines_20140225-v10.xlsm i get this error :
Error ’13’
Incompatible data type (i think it’s the correct Translation).
The problem occurs in this line : If Application.Version < 15 Then
In French version, Application.Version is not a number but a string and for Excel 2010 it's : "14.0"
Maybe the problem comes from the fact that in french version decimal separator is "," and not "." ?
To correct this bug we need to convert Application.Version in number.
So with this line : If Val(Application.Version) < 15 Then
there is no bug anymore.
Maybe it's the case in other languages ?
Thank's
Yes, although occasionally you need to select plot area to get the lines to update/refresh.
Maybe Jon’s right in taking the algebra route rather than moving the labels around. Of course you would still need to take into account errors in the OM such as the chartarea.left property.
A few things.
1. Shouldn’t the positions and sizes be declared as doubles?
2. If you know the width and height of the data labels and use xlLabelPositionCenter, you can compute the point’s position without the arbitrary constant lngPadding.
Who knows if the constant lngPadding is really going to stay constant? It varies with marker size anyway. For circular markers, a 2-pt marker has a padding of 4 pts, a 5-pt marker has a padding of 5.5 pts, and an 8-pt marker has a padding of 7 pts, measured from the center of the marker. I thought this padding might vary with the marker’s border width, but the padding is the same for border widths of 0.75 and 5.0 pts.
3. If the label was originally at one of the built in positions, isn’t it better to restore this position rather than using dlLeft and dlTop? This requires more code, but it’s more robust and just feels right.
4. Using the algebraic approach, you don’t need lngPadding, and you don’t need to worry whether there’s room to position the label on or adjacent to the marker without butting against the edge of the chart area.
5. Using the algebraic approach, you don’t need to select or refresh anything, worry about screen updating, or risk sending your users into epileptic seizures. You don’t need to mess around with other chart elements (datalabels). You don’t need to add labels if they don’t exist, then remember to delete them after you’ve finished. You don’t need to worry about repositioning the labels properly. Keep it simple.
In a quick test, I got exactly the same point positions using label size and position and xlLabelPositionCenter as I did using algebra.
Jon: I agree with everything you said except 2. Reason I went with Left and Top is to avoid the chance that a datalabel might not center properly on account of the point being near the plot edge.
I keenly await your forthcoming blog post on how to do things properly, so I can steal your code. I was just trying to be lazy. And cheeky. And look where that got me ;-)
Thanks Fred…I’ll change the approach at the same time that I steal Jon’s code.
Fred –
It’s well known that we must use Val(Application.Version) for compatibility with all regional settings (such as comma used as the decimal separator).
Jeff –
During this whole extended conversation, it has seemed to me that using a data label is sure a funny way to determine a point’s coordinates. The algebraic approach is easier and less prone to complications.
Regarding the ChartArea properties…
If I position and resize a chart using Alt, its edges align with the cell grid. I can measure the .Left, .Width., Top, and .Height properties of the underlying range, the chart objects, and the chart area. For a given chart, I get
.Left = 192 pts for Range, ChartObject, and ChartArea
.Top = 255 pts for Range, ChartObject, and ChartArea
.Width = 240 pts for Range and ChartObject, but 235 pts for ChartArea (5 pts less)
.Height = 225 pts for Range and ChartObject, but 220 pts for ChartArea (5 pts less)
If I stretch the PlotArea as wide as it can go, I get its properties:
.Left = -4, .Top = -4 (0,0 is offset slightly from the top left corner of the chart)
.Width = 240 pts, same as for Range and ChartObject
.Height = 225 pts, same as for Range and ChartObject
So I will suggest that you use the ChartObject properties, not the ChartArea properties, since the chart area seems to be missing a mysterious 5 pts from its width and height. I say mysterious, because the chart area goes to the edges of the “larger” ChartObject, even if it returns smaller dimensions.
This great concurrence of properties here has existed since 2007 (except for the ChartArea properties, which used to be valid only for chart sheets). In 2003 and earlier, there were wild offsets and discrepancies everywhere.
If you don’t have 2013 and you need the width and height of a data label, Andy’s trick needs minor modification.
Given the discrepancy in ChartArea width and height compared to chart object and underlying range (and fully extended plot area), it makes sense to use the ChartObject (i.e., the chart’s .Parent) properties.
Also there is the offset between the (0,0) origin of chart element positions and the left and top edge. For the charts on the sheet I was playing with earlier to day, the offset is -4 in both directions. Since we don’t trust the software vendor to keep this constant, we can determine them each time by moving the label up and left as far as it will go, then keep track of this initial Xi=dl.Left and Yi=dl.Top. Then we move the label down and right as far as it will go, and the properties we need are
dl.Width = ChartObject.Width-(dl.Left-Xi)
dl.Height = ChartObject.Height-(dl.Top-Yi)
This is pretty close to the .Width and .Height properties in 2013:
Width:
31.9650393700787 (property)
31.9650393700788 (calculated)
Height:
13.9850393700787 (property)
13.9850393700788 (calculated)
PS – Whoever is moderating this thread, I have two near-duplicate posts from a few minutes ago. The first one is missing one line, so delete the first one and retain the second. Sorry and thanks.
Jon: Re your point using a data label is sure a funny way to determine a point’s coordinates. The algebraic approach is easier and less prone to complications.
My approach sure is funny. In fact, it’s shortsighted, and in hindsight it’s completely unworkable. (Of course you offerred me your foresight and said as much before I started).
But I was being purposely short-sighted, as per the line in my post So while I keenly await [Jon’s] forthcoming blog post on how to do things properly, I spent my afternoon being quick and dirty. Plus I just didn’t want to spend too much time working out the different approaches depending on chart type and series type (e.g. stacked, etc). Plus I just wanted to play with the idea and see just how hard it would be.
In the end I spend just as much time failing to do it a dumb way as I would in trying to do it the proper way. But that’s okay…I know you’re forthcoming post will have a rock-solid approach that I can cut and paste.
How exactly ‘forthcoming’ is that post? ;-)
Jeff –
But it was fun and you learned a bunch of stuff. Or we did. I dug around in corners of the chart OM where I haven’t been in years.
I don’t know when I’ll blog about this. My problem certainly isn’t lack of material, since I have a dozen half-finished articles and several dozen folders full of half-developed topics. The problem is finding time to assemble the material into something semi-coherent. And things have been pretty busy lately.
Damn, it’s not as forthcoming as I had of hoped. Maybe I’ll whip something up and run it past you. Problem I have is that I’m unlikely to know what other factors I should take into consideration, and don’t have XL07 or earlier installs at present. Maybe I’ll dig up some old disks.
It’s all hunt and peck, trial and error, bitching online and getting ideas from other people.
You’re doing all this for the leader line thing, right? The feature I wish to hell they hadn’t made the default, because moving a label a point or two is enough to draw a teeny little useless but distracting leader line.
Rant over.
For many situations, you can use a two or three point scatter series, where the first point touches the data you want to point to, the last point has a data label with the info you want to present, and an optional middle point provides the knee in the leader line. This makes positioning somewhat easier. I’ve used this and may have even blogged about it, but I’m too lazy to look it up right now.
Yeah, it was for the leader lines thing. But I hardly think it’s worth it…they are native in XL13 and my code works fine for XL10. No idea what the penetration is for XL03 and XL07 compared to XL2010 and XL2013, but I can’t really be bothered looking into it further.
Excel 2003 is essentially dead. A year ago I dropped support for it with my charting utilities; too much hassle needing two files (either separate 2003 and 2007+ versions, or the 2003 version with a 2007 file that just handled the ribbon if needed). I have had exactly one person ask if I still had the old version available for 2003. There might be more, but they’ve been silent, and even if there are 100x as many who kept quiet, that’s well under 10% of my market. It might even be less than the Mac market.
2007 is going strong, but this is the kind of thing that if it were in my utility, I’d check the version of Excel, and only make it available post-2007. Most of the other stuff, in my utility anyway, works well enough that I only need minor version-to-version adaptations.