Draw line in a chart

  • Thread starter Thread starter gibasse
  • Start date Start date
G

gibasse

I have the following problem. I have a chart (date on x axis) on which I
draw a line. How can I obtain the ending points of the line in order to
compare, mathematically, their positions to corresponding values of
chart at the same date?
 
I'm sure your post makes perfect sense to you and might to other
readers, but I am lost. What kind of a line do you have? Horizontal?
Vertical? Between two arbitrary points?

What do the end points have to do with whatever other curve I assume
you have on the chart? What do you want? How to draw the line? Its
coordinates? Something else?

Maybe, a sample of the data might simplify the explanation...

--
[Posted directly to the Usenet newsgroup -- no affiliation with Excelforum]

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
I am sorry for my bad explanation of the matter. I drew a line char
starting from a table that contains the security values, for instanc
the NASDAQ index, ranging from 1985 to date. Then I display the char
for a specific date range.
At this point I draw manually, on the line chart, a trend line t
indicate the trend: imagine a line between two arbitrary points on th
chart depending on the evolution of the security.
My goal is to obtain, by some VBA procedure, the coordinates of the en
points (in the units of the chart, date and $) of the line I dre
before in order to set the relative position of the line and the char
when I select other data range.
I hope to tried to explain a little bit better my question.
Regards
Giusepp
 
This is a rather messy subject. Here are some pointers; no functional
code, sorry.

From the shape you can get the left, top, width, and height. That
means you can get both endpoints. These dimensions are relative to the
chart and are in points.

You can also get the chart's plotarea dimensions. The width
corresponds to the X-Max - X-Min and the height corresponds to Y-Max -
Y-Min. These are in an unspecified measurement system

[If you use chart events and trap the MouseDown and MouseUp events, the
X,Y coordinates are returned in the 'chart client coordinate' system --
whatever that means]

So, if you can sort out all the different measurement systems, you
could either:
Use this one conversion routine (that I can never find when I need it)
to convert the location of any point on the chart into 'Chart element',
'series-index-if-series', and 'point-index-if-series'

Or, you could write your own conversion routine that maps (Axis-Max -
Axis-Min) to PlotArea width for x values (height for y values) and then
intrapolate the position of the line to get the beginning and end
points of the line relative to the values plotted in the chart.

One example of the inconsistent measuring systems: With some chart I
used for testing: Drew a line from the top-left corner of the plotarea
to somewhere-doesn't-matter. The line's top-left coordinate were
(26.25, 40.5). However, the PlotArea Left,Top values were (8,33).
Finally, after writing a MouseUp event procedure and clicking on the
top-left of the plot area, I got x,y values of (40,61).

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
I didn't get around to answering this one, because I was trying to find
out how to tell what were the endpoints of the drawn line. If you use
the top, left, height, and width properties of the line, you find out
the coordinates of the rectangle that the line divides into two
triangles. But you don't know if the line is diagonal upwards or
downwards. I couldn't find any way (in half an hour) to identify the
actual line's endpoints.

I have posted code to convert mouse XY coordinates to chart axis
coordinates, but it isn't handy. A google search of the groups might be
helpful (http://www.google.com/advanced_group_search).

One suggestion I have is to add an XY Scatter series to the chart, maybe
a red line with large red crosses as datapoints at either end. The
points would initially be at arbitrary XY coordinates, but you can
select a point, then drag it around the chart, and Excel will update the
worksheet values accordingly. Drag the two points of this series until
you get a line where you want it, and read the new coordinates from the
worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Tushar said:
This is a rather messy subject. Here are some pointers; no functional
code, sorry.

From the shape you can get the left, top, width, and height. That
means you can get both endpoints. These dimensions are relative to the
chart and are in points.

You can also get the chart's plotarea dimensions. The width
corresponds to the X-Max - X-Min and the height corresponds to Y-Max -
Y-Min. These are in an unspecified measurement system

[If you use chart events and trap the MouseDown and MouseUp events, the
X,Y coordinates are returned in the 'chart client coordinate' system --
whatever that means]

So, if you can sort out all the different measurement systems, you
could either:
Use this one conversion routine (that I can never find when I need it)
to convert the location of any point on the chart into 'Chart element',
'series-index-if-series', and 'point-index-if-series'

Or, you could write your own conversion routine that maps (Axis-Max -
Axis-Min) to PlotArea width for x values (height for y values) and then
intrapolate the position of the line to get the beginning and end
points of the line relative to the values plotted in the chart.

One example of the inconsistent measuring systems: With some chart I
used for testing: Drew a line from the top-left corner of the plotarea
to somewhere-doesn't-matter. The line's top-left coordinate were
(26.25, 40.5). However, the PlotArea Left,Top values were (8,33).
Finally, after writing a MouseUp event procedure and clicking on the
top-left of the plot area, I got x,y values of (40,61).
Click to expand...
 
One suggestion I have is to add an XY Scatter series to the chart, maybe
a red line with large red crosses as datapoints at either end. The
points would initially be at arbitrary XY coordinates, but you can
select a point, then drag it around the chart, and Excel will update the
worksheet values accordingly. Drag the two points of this series until
you get a line where you want it, and read the new coordinates from the
worksheet.
Click to expand...
That's a neat idea!

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I didn't get around to answering this one, because I was trying to find
out how to tell what were the endpoints of the drawn line. If you use
the top, left, height, and width properties of the line, you find out
the coordinates of the rectangle that the line divides into two
triangles. But you don't know if the line is diagonal upwards or
downwards. I couldn't find any way (in half an hour) to identify the
actual line's endpoints.

I have posted code to convert mouse XY coordinates to chart axis
coordinates, but it isn't handy. A google search of the groups might be
helpful (http://www.google.com/advanced_group_search).

One suggestion I have is to add an XY Scatter series to the chart, maybe
a red line with large red crosses as datapoints at either end. The
points would initially be at arbitrary XY coordinates, but you can
select a point, then drag it around the chart, and Excel will update the
worksheet values accordingly. Drag the two points of this series until
you get a line where you want it, and read the new coordinates from the
worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Tushar said:
This is a rather messy subject. Here are some pointers; no functional
code, sorry.

From the shape you can get the left, top, width, and height. That
means you can get both endpoints. These dimensions are relative to the
chart and are in points.

You can also get the chart's plotarea dimensions. The width
corresponds to the X-Max - X-Min and the height corresponds to Y-Max -
Y-Min. These are in an unspecified measurement system

[If you use chart events and trap the MouseDown and MouseUp events, the
X,Y coordinates are returned in the 'chart client coordinate' system --
whatever that means]

So, if you can sort out all the different measurement systems, you
could either:
Use this one conversion routine (that I can never find when I need it)
to convert the location of any point on the chart into 'Chart element',
'series-index-if-series', and 'point-index-if-series'

Or, you could write your own conversion routine that maps (Axis-Max -
Axis-Min) to PlotArea width for x values (height for y values) and then
intrapolate the position of the line to get the beginning and end
points of the line relative to the values plotted in the chart.

One example of the inconsistent measuring systems: With some chart I
used for testing: Drew a line from the top-left corner of the plotarea
to somewhere-doesn't-matter. The line's top-left coordinate were
(26.25, 40.5). However, the PlotArea Left,Top values were (8,33).
Finally, after writing a MouseUp event procedure and clicking on the
top-left of the plot area, I got x,y values of (40,61).
Click to expand...
Click to expand...
 
Thanks. I've actually used it, and it isn't too awkward, once you get
used to it. You can only drag one direction at a time, not diagonally,
but it still works pretty well.

- Jon
 
Back
Top