Excel VBA: coords of Point on chart?

Hi all!
I met a strange problem - I can't find a way to know single Point (from SeriesCollection) position on chart (as the distances from the left edge and top of the chart area). Is it possible?
LVL 5
vboukharAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

criCommented:
I thought there was such a question, but the search engine is a bad joke.

You certainly already have searched the MSKB, nonetheless, here 2 URL which might be related in your case:

VB Code to Automatically Set Min and Max Scale for Y- Axis  http://support.microsoft.com/support/kb/articles/q141/3/63.asp

Macro to Add Labels to Points in an XY (Scatter) Chart  http://support.microsoft.com/support/kb/articles/q161/5/13.asp

Perhaps you can state what you want to make.
0
vboukharAuthor Commented:
Thanks, cri, but those articles don't contain useful information for my case (I can add DataLabel to point, but can't get XY-position neither Point, or DataLabel).
I have CD with MSKB and search it unsuccessfully.
0
tureCommented:
vboukhar,

This is not finished, it returns the position within the plot area, not the chart area as you requested. But perhaps it's a little help to get you started...

Sub Macro1()
  Dim ch As Chart
 
  Dim xaxis As Axis
  Dim xmin
  Dim xmax
  Dim xaxislength As Long
  Dim xvalues
  Dim xvalue
  Dim xpos
 
  Dim yaxis As Axis
  Dim ymin
  Dim ymax
  Dim yaxislength As Long
  Dim yvalues
  Dim yvalue
  Dim ypos
 
  Dim s
  Dim p
 
  s = 1 'Series number
  p = 2 'Point number
 
  Set ch = ActiveSheet.ChartObjects("Chart 1").Chart
  Set xaxis = ch.Axes(xlCategory)
  Set yaxis = ch.Axes(xlValue)
 
  xoffset = ch.ChartArea.Left + ch.PlotArea.Left
  yoffset = ch.ChartArea.Top + ch.PlotArea.Top
 
  xmin = xaxis.MinimumScale
  xmax = xaxis.MaximumScale
  xaxislength = xaxis.Width
 
  ymin = yaxis.MinimumScale
  ymax = yaxis.MaximumScale
  yaxislength = yaxis.Height
 
  yvalues = ch.SeriesCollection(s).Values
  yvalue = yvalues(p)
 
  xvalues = ch.SeriesCollection(s).xvalues
  xvalue = xvalues(p)
 
  ypos = (yvalue - ymin) / (ymax - ymin) * yaxislength
  xpos = (xvalue - xmin) / (xmax - xmin) * xaxislength
 
  MsgBox ypos
  MsgBox xpos
End Sub

Ture Magnusson
Karlstad, Sweden
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

criCommented:
VBoukhAr, I did not mean my URLs for your question, tried to guess what you actually want to accomplish.
0
vboukharAuthor Commented:
Ture,
Your way is the single one? I was thinking about calculation of XY position from MaximumScale etc, but it seems strange to use this way in OOP. I doubt, that Excel make something like that to add DataLabel near Data Point.
(I'd like to feel myself like whimsical customer :-)
0
tureCommented:
vboukhar,

I haven't been able to find any other way then to calculate the position 'manually, the way my code suggests.
As far as I know, it's not possible to directly find the x and y coordinates of a data point in a chart.

/Ture
0
vboukharAuthor Commented:
Thanks, ture!
Your opinion is proof of my own vision of this problem. :-(
So I think, we can close this discussion
Thanks!
0
tureCommented:
Thanks to you too! Perhaps in the next version...

/Ture
0
mistelCommented:
You might want to try the following, to get the top/left-position of a datalabel. You can use the resulting coordinates to calculate the position of the chart-point...

Function ConnLabel(chtCur As Chart)
Dim sObj        As Series

    If chtCur.SeriesCollection.Count = 0 Then
        Exit Function
    End If
    For Each sObj In chtCur.SeriesCollection
        sObj.Points(sObj.Points.Count).HasDataLabel = True
        sObj.Points(sObj.Points.Count).DataLabel.Caption = "Left: " & sObj.Points(sObj.Points.Count).DataLabel.Top & "Left: " & sObj.Points(sObj.Points.Count).DataLabel.Left
    Next sObj
   
End Function

Sub test()

    ConnLabel ActiveChart        'run this sub (PF5)

End Sub
0
criCommented:
mistel, thanks for the feedback/code. Will test it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.