Vertical line on chart - calculating X,Y coordiantes

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
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

Code:
[/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]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Can you make the chart X-Y, and add another series to the chart itself for the line?
 

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
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
 

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500

ADVERTISEMENT

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Feb 9, 2011
Messages
500

ADVERTISEMENT

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

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
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

Well-known Member
Joined
Feb 9, 2011
Messages
500
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,141,844
Messages
5,708,928
Members
421,599
Latest member
santosh234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top