Vertical line on chart - calculating X,Y coordiantes

[Rasm]

I have a chart plotted of the type '.ChartType = xlLine. - I want the line to be at a specific values on my X axis (it seems the addline function uses pixels or something) - so say my X axis goes from 9 to 15 - I now want the line drawn at the value 12.35 on the X axis - starting at the start of the Y axis and end at the end of the Y axis - how can I calculate my X coordinates. I also need to calculate the Y coordiante where the two axis meet on my chart as well as the Y coordinate where my Y axis ends.

I hope this makes sense

[/SIZE]
[SIZE=3] With .Shapes.AddLine(200, 0, 200, 250) '(BeginX, BeginY, EndX, EndY)
            .Line.ForeColor.SchemeColor = 10
            .Line.Visible = msoTrue
            .Line.Weight = 1.5
            .Line.Visible = msoTrue
            .Line.Style = msoLineSingle
            .Line.DashStyle = msoLineSquareDot
 End With[/SIZE]
[SIZE=3]

 

[GlennUK]

Can you make the chart X-Y, and add another series to the chart itself for the line?

 

[Rasm]

Glen
I think thats what I have to do - I have the vertical line drawn - but when I resize my chart - the vertical line does not properly resize - it is on wrong place on X axis. Even thus I have option to move line with chart set to True. Hmmmm - these X,Y coordinates looks like trouble - thanks anyway

 

[Jon Peltier]

Here's a page that links to several options:
http://peltiertech.com/Excel/Charts/AddLine.html

 

[Rasm]

Jon
Thanks - I will definitely study that - I have to understand what happens when you resize the chart - It seems my vertical line does not move correctly - in addition to me having to calculate the X,Y coordinates. I hope there is an event when you resize the chart so that I can recompute the X,Y coordinates - but for now I will study the link.

PS I did find this article http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=190

very helpful

 

[Jon Peltier]

Did you find the article I provided a direct link to? It has what you want.

P.S. You DO NOT want to waste time with a drawn line which moves every time you inhale.

 

[Rasm]

Jon
LOL - yes I got the link - but have to read it after hours - but you are right about the inhale.

 

[Rasm]

Jon
yes - I read the link - that is what I will do - this X,Y coordinate bussiness is insane - adding another series and hidding the secondary axis is the ticket - Thanks - I have it working - I can resize my chart with no ill effects.

 

[Rasm]

Jon
Thanks for the help - My charts are working great.

I do however have a question for you - MS states you can have 32K datapoints in a chart - that works fine if you use a Range for the .values and/or .Xvalues - however if I use an array then only 16384 data points are charted (I get no error - no nothing from MS) - I know that by using .SeriesCollection(PlotCounter).Points.Count - it comes out at 16384 or 2^14 - so that is the max I can chart using an array - when I then click on a point of my chart - I get the MS Excel error - Text string too long - This may just be a MS bug - I dont know - anyway - any commenst would be appreciated. Right now I make multiple charts if over 16384 datapoints are charted - I have this many points as I make SPC charts and one weeks production represents around 17K worth of data. The data however have a lot of breaks (no product was een by the device) - so I chart only valid data - so that is why I use array data rather than ranges (MS will not allow a range string > 255 characters)

 

[Jon Peltier]

You cannot use a range string OR a literal array (i.,.e., a comma-separated list surrounded by curly braces) in excess of around 250 points (it's not 255, but it's close, just under). So you should use ranges, but you should make sure the ranges are contiguous, to keep the addresses short.