Page 1 of 3 123 LastLast
Results 1 to 10 of 29
Like Tree2Likes

Chart Connect Points Drawing Shapes Peltier

This is a discussion on Chart Connect Points Drawing Shapes Peltier within the Excel Questions forums, part of the Question Forums category; Excel Vba Shapes to Connect Points Charts and make Drawing Clone of Complex Chart XY with Lines Hi, Partners, I ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    23

    Default Chart Connect Points Drawing Shapes Peltier

    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!uHzNTAOIb...2xV1XlQ_h2IxVk

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


    thank you very mutch.
    DorianGrey


  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,357

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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.



    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.



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



    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
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    23

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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 by doriangrey; Mar 15th, 2017 at 10:34 PM.

  4. #4
    New Member
    Join Date
    Jan 2012
    Posts
    23

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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 by doriangrey; Mar 15th, 2017 at 10:50 PM.

  5. #5
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,357

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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.



    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.



    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  6. #6
    New Member
    Join Date
    Jan 2012
    Posts
    23

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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!

  7. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,357

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  8. #8
    New Member
    Join Date
    Jan 2012
    Posts
    23

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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"


  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    23

    Default Re: Chart Connect Points Drawing Shapes Peltier

    ERRATE :
    The words "graph" is wrote wrong, the correct is "chart" . Translation problem error.

  10. #10
    New Member
    Join Date
    Jan 2012
    Posts
    23

    Default Re: Chart Connect Points Drawing Shapes Peltier

    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 !

    Last edited by doriangrey; Mar 16th, 2017 at 05:42 PM.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com