Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

line and points in xychart

737 views
Skip to first unread message

fernando cinquegrani

unread,
Mar 1, 2002, 5:07:39 AM3/1/02
to
in a xy chart what is the position (left, top)
of a single datapoint [cells(x, y)]?
how can i connect with a line (shape line)
two points not in succession?
thanks
.f
http://www.prodomosua.it


fernando cinquegrani

unread,
Mar 1, 2002, 8:02:24 AM3/1/02
to
"fernando cinquegrani" <f.cinq...@libero.it> ha scritto nel messaggio
news:BkIf8.48524$fa5.1...@twister2.libero.it...

> in a xy chart what is the position (left, top)
> of a single datapoint [cells(x, y)]?
> how can i connect with a line (shape line)
> two points not in succession?

now i do this in connecting centers of
vertical centered and horizontal centered
datalabels (mydatalabel.position.left,
mydatalabel.position.top).
it is possible to simplify this?

> thanks
> .f
> http://www.prodomosua.it
>
>


Tushar Mehta

unread,
Mar 4, 2002, 6:22:27 PM3/4/02
to
[This followup was posted to microsoft.public.excel.charting with an
email copy to fernando cinquegrani.
Please use the newsgroup for further discussion.]

I would suggest that you not use shapes to connect data points on a
chart. Instead, create a new series with just two data points in it.
The two data points will be the data points that you want to connect.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--

In <BkIf8.48524$fa5.1...@twister2.libero.it>, fernando cinquegrani
<f.cinq...@libero.it> wrote

fernando cinquegrani

unread,
Mar 5, 2002, 3:34:24 AM3/5/02
to
Thanks, but...
i would fill the polygon defined by lines.
please, see cluster.exe or scatter.exe
in my excel page (hhtp://www.prodomosua.it/ppage02.html).
i select a range of datapoints, then compute
the convex hull (in italian: inviluppo convesso), then create
a new dataseries with lines. how can i fill this polygon?
Thanks
.f


"Tushar Mehta" <ng_p...@bigfoot.com> ha scritto nel messaggio
news:MPG.16edcbbe7...@msnews.microsoft.com...

Tushar Mehta

unread,
Mar 5, 2002, 9:42:18 AM3/5/02
to
I couldn't tell from the examples on the web page what exactly you want
to do. And, its been some time since I had to worry about constructing
a convex hull. So, if what you want to do is create an object that
closes on itself, just plot the first data point once more as the last
data point. This would have to be in a XY Scatter chart. For example,
to create a rectangle, plot (x1,y1), (x1,y2), (x2, y2), (x2, y1), and
(x1,y1).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--

In <1l%g8.4319$uN5.1...@twister1.libero.it>, fernando cinquegrani

fernando cinquegrani

unread,
Mar 5, 2002, 2:45:47 PM3/5/02
to
Thanks for your interest, but...
in my examples i create a new close polygon
(the convex hull) yet.
i want to fill this with a colour.
i think i have to draw the polygon as a shape.
i now draw this polygon as shape (not in examples),
and i fill it with a colour.
but for find the nodes of my polygon, i need the
position in chart of each point.
which is the position (left, top) of a generic point in a xychart?
the statement dataseries(a).points(b).left=...doesn't exist.
now i use the datalabel (not visible) center of this point.
this process is very slow and it is impossible to
update the chart in real time if i change the data.
any solution?
.f
(excuse me, but i dont speak english)


"Tushar Mehta" <ng_p...@bigfoot.com> ha scritto nel messaggio

news:MPG.16eea3562...@msnews.microsoft.com...

Jon Peltier

unread,
Mar 5, 2002, 2:55:38 PM3/5/02
to
Fernando -

No problem, I think I know what you want. To just draw a closed shape, you
can use Tushar's suggestion, which will connect the points which you define
for the nodes of the closed shape.

Since you want to fill in the shape with some other color, however, you really
need a shape. And the coordinates of the shape have to be converted from
chart X,Y values to screen-pixel-points (or whatever mysterious units those
are). I was intrigued, so I recorded a macro whil I drew a shape, then I
fiddled with it as shown.

This works for an XY Scatter chart (I assume it will crap out for a line
chart), and it will draw a shape using the points of series 1 in the active
chart. It converts from XY to screen coordinates, starts the shape with the
last point of the series, then draws to the first point, the second, and
around to the last (so you do not need to repeat the first/last point).

''' START THE CODE ---------------------------------------
Sub DrawAShape()
Dim myCht As Chart
Dim mySrs As Series
Dim Npts As Integer, Ipts As Integer
Dim myBuilder As FreeformBuilder
Dim myShape As Shape
Dim Xnode As Double, Ynode As Double
Dim Xmin As Double, Xmax As Double
Dim Ymin As Double, Ymax As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double

Set myCht = ActiveChart
Xleft = myCht.PlotArea.InsideLeft
Xwidth = myCht.PlotArea.InsideWidth
Ytop = myCht.PlotArea.InsideTop
Yheight = myCht.PlotArea.InsideHeight
Xmin = myCht.Axes(1).MinimumScale
Xmax = myCht.Axes(1).MaximumScale
Ymin = myCht.Axes(2).MinimumScale
Ymax = myCht.Axes(2).MaximumScale

Set mySrs = myCht.SeriesCollection(1)
Npts = mySrs.Points.Count

Xnode = Xleft + mySrs.XValues(Npts) * Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Npts)) * Yheight / (Ymax - Ymin)

Set myBuilder = myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
For Ipts = 1 To Npts
Xnode = Xleft + mySrs.XValues(Ipts) * Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) * Yheight / (Ymax - Ymin)
myBuilder.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
Next
Set myShape = myBuilder.ConvertToShape

With myShape
' USE YOUR FAVORITE COLORS HERE
.Fill.ForeColor.SchemeColor = 13 ' YELLOW
.Line.ForeColor.SchemeColor = 12 ' BLUE
End With

End Sub
''' END THE CODE -----------------------------------------

Good luck,
- Jon
_______

In article <2a9h8.6501$5z3.1...@twister2.libero.it>, f.cinq...@libero.it
says...

fernando cinquegrani

unread,
Mar 5, 2002, 7:21:47 PM3/5/02
to
perfect, jon! thanks for your help
.f


"Jon Peltier" <jonpe...@yahoo.com> ha scritto nel messaggio
news:utFn5#HxBHA.2620@tkmsftngp03...

fernando cinquegrani

unread,
Mar 5, 2002, 8:41:53 PM3/5/02
to
Xnode = Xleft + (mySrs.XValues(Ipts) <<< - Xmin >>>) * Xwidth / (Xmax -
Xmin)
because the scale doesn't start by zero (MinimumScale).
thanks again
.f


"fernando cinquegrani" <f.cinq...@libero.it> ha scritto nel messaggio
news:Icdh8.9389$uN5.2...@twister1.libero.it...

Jon Peltier

unread,
Mar 5, 2002, 10:16:01 PM3/5/02
to
Doh!!

Thanks for correcting this for me.

- Jon
_______

In article <rpeh8.8611$5z3.2...@twister2.libero.it>,
f.cinq...@libero.it says...

fernando cinquegrani

unread,
Mar 9, 2002, 9:03:15 AM3/9/02
to
the statement
Set myShape = myBuilder.ConvertToShape
is frequently in error when the
chart is very little (points quite near).
do you know why?
how can i obviate?
.f
http://www.prodomosua.it
p.s.:
1. the addin cluster.xla, where i use your suggestion, is on line
2. for a best performance of your code, define
Dim Xnode as Single, ...

"Jon Peltier" <jonpe...@yahoo.com> ha scritto nel messaggio

news:uErS$0LxBHA.1644@tkmsftngp07...

Jon Peltier

unread,
Mar 12, 2002, 5:35:18 PM3/12/02
to
Fernando -

When you draw a shape manually, if you make a point very close to another
point, the shape closes up for you. It may be that the ConvertToShape method
has been done already, and you are causing it to error by trying to repeat the
method. I would look into parameters of AddNodes (what does msoEditingAuto
really mean, for instance), and whether myBuilder is already converted to a
shape.

- Jon
_______

In article <Vxoi8.11775$tC3.3...@twister1.libero.it>,
f.cinq...@libero.it says...

Flavio Henrique da Silva

unread,
Mar 14, 2017, 11:38:12 AM3/14/17
to
Please, Jhon Peltier and Fernando Cinquentani,
have a solution for this problem for error on ConvertToShape for point very close to another point ?

j...@peltiertech.com

unread,
Mar 14, 2017, 3:50:20 PM3/14/17
to
Are you experiencing a problem? There really doesn't seem to be a problem when drawing the shape programmatically, since the points are added explicitly. When you do this manually, Excel sees you click twice close together and decides that you are finishing with your drawing.

- Jon
Message has been deleted
Message has been deleted
Message has been deleted

Flavio Henrique da Silva

unread,
Mar 14, 2017, 6:56:54 PM3/14/17
to
Hi Jhon Peltier,
same problem of Fernando Cinquengrani at last message of him in this thread.
same problem of Fernando Cinquegrani:
the statement
Set myShape = myBuilder.ConvertToShape
is frequently in error when the
chart is very little (points quite near).
do you know why?
how can i obviate?
http://www.prodomosua.it

Flavio Henrique da Silva

unread,
Mar 14, 2017, 6:59:14 PM3/14/17
to
Hi Jhon Peltier,
same problem of Fernando Cinquengrani at last message of him in this thread.
same problem of Fernando Cinquegrani:
the statement
Set myShape = myBuilder.ConvertToShape
is frequently in error when the
chart is very little (points quite near).
do you know why?
how can i obviate?
''http://www.prodomosua.it'

j...@peltiertech.com

unread,
Mar 15, 2017, 12:40:33 PM3/15/17
to
I don't know why you got this error. I have tested with points "quite near" without problems.

What version of Excel are you using?

What does your data actually look like? ("Quite near" isn't very descriptive.)

- Jon
http://peltiertech.com
_______

Flavio Henrique da Silva

unread,
Mar 15, 2017, 4:04:30 PM3/15/17
to
Jhon, I'll put the attached file in another forum if you wish, here I can not attach.
I have now sent you the file in an email.

COPY OF EMAIL
----------------------------------------------------------
Excel Vba Shapes to Connect Points Charts
j...@peltiertech.com

Hi, Jhon Peltier,
i sent to you sample file with macros in this email.
I could put it on the MR.ExcelForum if you wish

I want connect the point of chart XY to create a shape clone of chart of 2 ways, 1solid line shape and 1Outline Shape.

your code works with more simplex charts but no with my complex chart.
i need change your "Dim as Integer" to "Dim as Long" to works with more simplex.

i need change the location of "Next" before "ConverToShape" to final of code to avoid the problem of shape close before a correct moment.

https://groups.google.com/forum/#!searchin/microsoft.public.excel.charting/nodes$20points%7Csort:relevance/microsoft.public.excel.charting/AgWMOIKU4Do/w1JYa3PIt5kJ

the chart (XY Scatter dots only) have 1series with very number of points with contiguous and non-contiguous points complex design.

Excel 2013 32bit Original - Win7.

please work with sample file logo if possible.

thank you very mutch.
Flavio Henrique.
--------------------------------------------------------------
0 new messages