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?
Who is Participating?
tureConnect With a Mentor Commented:

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
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.
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

VBoukhAr, I did not mean my URLs for your question, tried to guess what you actually want to accomplish.
vboukharAuthor Commented:
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 :-)

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.

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

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
mistel, thanks for the feedback/code. Will test it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.