Chart Connect Points Drawing Shapes Peltier

doriangrey

Board Regular
Joined
Jan 18, 2012
Messages
57
Excel Vba Shapes to Connect Points Charts and make Drawing Clone of Complex Chart XY with Lines


Hi, Partners,
I using Jhon Peltier code to draw in charts,


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


the 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 my sample file logo if possible.

https://mega.nz/#!RxRV0QSR!uHzNTAOIbzJqyN3igXlGxxbSxlKrR2xV1XlQ_h2IxVk

Code:
[COLOR=#333333][FONT=source_sans_proregular]https://mega.nz/#!RxRV0QSR!uHzNTAOIbzJqyN3igXlGxxbSxlKrR2xV1XlQ_h2IxVk
[/FONT][/COLOR]


thank you very mutch.
DorianGrey

 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I should note that the code sample you have found is 15 years ago. I'm surprised it's still floating around, but I guess Google never forgets. I'll repeat the code at the bottom of this post.

The Integer variables would be better declared as Longs, for multiple reasons. This is the only change I've made to the code.

Also, the "Next" does in fact come before "ConvertToShape" in that ancient code.

I wrote the above while waiting for the file to download. Now for the specifics about your file.

Note that the chart on sheet SheetX1simply includes some blank cells (the data stops at row 25, but the chart series goes down to row 32). This leads to the line segment that reaches to (0,0). Excel won't plot a point there, but the VBA code still uses the data for the shape it draws. When you fix the data, the drawing of the "X" is perfect.

Your chart with the Superman logo has the same problem with blank cells. The data starts at row 2 and ends at row 2488, but the chart series uses all million-plus cells in each column.

Then, the points for some reason don't trace the outline of the shape, instead they go back and forth across the chart. It's like the points are there, but they are sorted by Y value instead of by position around the outline.

What you need to do is order the points so they go around the shape. You don't need a point every unit if they are all on a straight line, just points at the ends of each line segment. Then build it up in pieces.

Here is piece 1, the outermost outline of the logo. I've put the data into a Table, because I find it easier to work with the points that way. The cells with blue text are points on the outer edge of the figure, and the cells with orange text are on the inner edge. I ran the code, then cut the resulting shape and pasted it below the chart. The shape has an extra blue line in the bottom left corner, where I started and ended the shape. That doesn't matter, because you're going to only use the fill color of the shape, and use no border.

vwhcvLS.png


I used my original code (bottom of the post) to draw this, by the way.

Then I looked at the triangular cut out at the bottom of the logo (at the top of my chart, because that's how your data was set up). I inserted the new points (shaded blue) at the appropriate place, and you see the line connecting markers from the original outline to the outline of the cutout. The new shape has the original outline and the cutout, and it has some extra border segments connecting these regions. But again, if you hide the border and only use the fill, it'll be cool.

HSKJ3ki.png


I repeated this for the cutout at the top right of the logo (the bottom left of the inverted chart), this time shaded orange.

8uovn8t.png


Three more cutouts and you have yourself a logo, at least one of the colors. You can adjust the positions of the points, and even add more if you need a smoother arc around the "S". When it's perfect, run the code.

Repeat this protocol for each of the colors in your logo, then draw and line up the shapes.

Code:
Sub DrawAShape()
  Dim myCht As Chart
  Dim mySrs As Series
  Dim Npts As Long, Ipts As Long
  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
 
Upvote 0
Jhon,

thanks you very, very mutch.
i am now running your code of MRExcel on my chart Superman,
up to 30 minutes, it will works on my chart of my sample file?

i need the code run over my chart Superman of sample file, this chart of sample file, not new chart! ???
i crt c ctrl v the code and run.

i am not will make new chart witch new coordinates, i am will use the chart and coords of my sample file about Good Reasons.
your code works with my chart?

cheers.
 
Last edited:
Upvote 0
Jhon 2,

thanks you very, very mutch.
i am now running your code of MRExcel on my chart Superman,
up to 30 minutes, it will works on my chart of my sample file?

i need the code run over my chart Superman of sample file, this chart of sample file, not new chart! ???
i crt c ctrl v the code and run.

i am not will make new chart witch new coordinates, i am will use the chart and coords of my sample file about Good Reasons.
my chart Superman have 3points and not 1one point at "segment line" or "segment node" and i think this a problem but need to be this way.
i think the macro code need addNodes to each 1fist point of each 3points to 1fist point to 1fist point etc.
after, need addNodes to each 3fist point of each 3points to 3fist point to 3fist point etc. to make a Outline line in all chart.

but to make a solid line in all chart i am not think a solution, plot 1one node at each 1node of 3nodes? not will cause zig-zag?
or vba to "select only 1pont of each 3point and plot accord this coords of each 1point to 1point to 1point?

i am not expert, i am only advanced knowledge and poor English Language.

your code works with my chart?

cheers.

edit: My chart have only 1one Series, not possible to me break the coords to more series.
 
Last edited:
Upvote 0
The code does work on the data in your chart, just not the way you think it should. The problem is that your data does not work for your purpose.

First, your data has many more points than you need. There more points than necessary to define the straight lines. Also, there are points within the region that you want shaded. It is as if the points were not defined by the outline of the shapes, but by which points in a fine grid fell within the shaded regions. It's like it is the needlepoint pattern to make the design. You may be able to use some of your points, but you have to delete the unneeded ones. You also have to put the points in the right order to trace the outline of the shape, as I described in my previous post.

Here is the first set of data in my post, with some intermediate points added to illustrate the sorting problem with your data. My data are sorted to trace the outline of the shape I want, shown by the markers and the lines connecting the markers. The chart looks fine, and when my program makes the shape, it draws it as expected, because it follows the paths of the connecting lines, because that is the order of points in the series.

ogqvPoo.png


Here is the exact same data, except as in your data set, it is sorted first by X (left to right in the chart) and then by Y (bottom to top). The points are all in the same places, and if I had turned off the display of the lines, you would not have known the difference. However, with the connecting lines visible, you can see that the points are not ordered to trace a nice outline. When I run the program, it connects all the right points, but in the wrong order, so the nice outline is not present.

This is why your data does not work.

zXaropZ.png


You need to sit down and figure out what points you need to trace the outline of the shape, so the fill color of the shape provides the color of the shape. Your data had been generated so that one colored pixel at each point provides the color of the shape.
 
Upvote 0
I need to work the way the dots are in the chart and not the coordinates of the spreadsheet.

Work the way my superman chart is originally.
The code must run on the original chart.

The reason is that these coordinates are obtained in an automated way and represent the original image and I need to do the work automatically (automatically).


I can not change the coordinates and the points of the chart at the first moment I run the code.
Then I will want to erase some points of the chart, but not at this first moment.

i think The "line of point" of chart is not solid, not a 1one point per node, and yes 3 points to eah node, like a dashed line of example. I am need conect each 1fist point to 1fist point, to 1fist point.
After, conect each 3th point to 3th point, etc.
making a outline drawing.
Jhon, enlagrge the chart original or zoom and look , click on the "line points" of chart and look.
imagine 3 dots points (diagonal) over the line of entire chart.
i am not know way of put the image here.

please help!
 
Upvote 0
I did enlarge your chart, and I connected the points with lines, which is how I determined that your data is not compatible with my algorithm.

From my point of view, it is easier to imagine changing the data that to try to manipulate my algorithm to deal with the wrong data.

I appreciate that your data is automatically generated by some other system, but it is not the right data for my algorithm. I imagine that there must be image rendering algorithms that generate outlines instead of inside-or-outside patterns, but I wouldn't know where to go to find them.
 
Upvote 0
Jhon, please do not leave yet.

I think your code can be modified to do what I need.

Basically I think it is necessary to loop through the certain points (nodes) of the graph, and then AddNodes.

I know what the right points are, but I do not know how to do it so Macro selects them automatically.

My graphics is done only by the dots of pixels in the black color, I scan only the black pixels.

I did tests with 2 macros up20minutes and then I interrupted and see how the drawings were.

I still have not let these macros work until the end because I did not have the time and they are very slow.

For me it's okay if more shapes are created than necessary, I just need some of the shapes to be created correctly.

I would like you to teach me how you created your Superman sample files and how you chose the correct coordinates, please send it to me.

I did not understand how you did to find the correct coordinates, did you do this manually or automatically?


Your final "next" need move to final, Blue Shapes are your code and the Red Shapes Drawing are "Code Herber Forum version"

264e2a6e796dabc36bf8a642fd68f1b2.png
 
Upvote 0
Hi Partners and Jhon Peltier,


Jhon, how did you find and put in the correct coordinates?

i make mistake on select data coords in cells to chart, i select Entire Row with Blank Cells and not only cells with data and this make problem error of slow macro run and plot shapes.


I fixed the coordinates error in the cells and the shapes were plotted on the chart.
A great evolution as you can see in the worksheet screenshots.


1st chart Pertier Modified BLACK - many shapes
It is necessary to cut out the 1stChart shapes and paste them in the worksheet and then greatly increase the size of the shape and right-click and EditNodes to see the Nodes and shape drawing.
I change the location of final "Next" to final of code, and disabled the colors (yellow problem).


2Chart Red PeltierHerberForum RED - many lines
This chart is made by Diagonal lines connecting the 1oPoint to 3oPoint of each segment and thus all the way around. This shape was almost good but it would be necessary to connect 1oPoint to 1oPoint etc, and 3oPoint to 3oPoint etc.


3Chart PeltierOriginal BLUE - 1shape only
This chart is complete but has unnecessary lines that I need to delete or avoid plotting or another solution. Right-Click on the shape and EditNotes to see the SupermanLogo Nodes.


This was a great evolution.
I still think I need a code for Loop each required node (1First to First etc, 3rd to 3rd node, etc) but I do not know how to do this loop.

i need more help !

fe78a3af32e251b86c6a91b5423b938e.png
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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
Back
Top