Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-21-2023, 12:14 PM
Abrasax Abrasax is offline Draw proper Bezier curves to contruct flow shapes for Sankey chart Windows 10 Draw proper Bezier curves to contruct flow shapes for Sankey chart Office 2019
Novice
Draw proper Bezier curves to contruct flow shapes for Sankey chart
 
Join Date: Apr 2023
Posts: 3
Abrasax is on a distinguished road
Question Draw proper Bezier curves to contruct flow shapes for Sankey chart

Hello everyone.

I am programmatically trying to build a Sankey diagram from scratch as my company has a policy of not allowing add-ins or other applications available from the Web, so I can't use a readily provided relevant solution. I have managed to come close to a functioning result, but properly drawing the flows between the nodes is the tricky part and the one I need your help with.

More specifically, in the attached, by pressing the One shape button a flow shape is created between the first node (blue bar) and the node in the lower right (orange bar). In practice, the coordinates for a number of points and control points for these are estimated based on the relative positions of the two nodes and then these points are connected with each other using the shapes.Addcurve command to effectively draw a Bezier curve as a continuous freeform shape that represents the desired flow shape. I can provide the guides I have used to estimate the control points, but they link to another Excel forum and I am not sure if this is allowed by the rules of this forum.

The guide I used to draw the Bezier lines is the following:

Shapes.AddCurve method (Excel) | Microsoft Learn

Still, as you can see, once you press the button, the shape is drawn but with some bumps and the lower part of the shape is not drawn properly (possibly due to the shape inherently having an outline which messes the proper coordinates but I am not sure this is the case).

As such, I tried a slightly different method by drawing each part of the desired shape as a separate Bezier line (two curves and two straight lines). This can be achieved by pressing the Separate lines button. When this is done, a more properly shaped flow shape is created (using the exact same coordinates used by the One shape button), but the drawback in this case is that I cannot merge the 4 lines into a uniform shape which can then be filled with color and thus represent the flow shape that I am trying to achieve.

Therefore, I am looking for your help on two alternative solutions (either one will suffice):

1. Fix the resulting shape when the One shape button is pressed into a smooth one (no bumps).
2. Merge the 4 lines into a uniform shape that can properly filled with color when the Separate lines button is pressed.

The model used works as follows:



i. In cells I21 - M41 the coordinates for the existing manually drawn shapes are given. These are re-estimated each time a button is pressed (if the shapes are moved around).
ii. In cells I43 - M69 the points for the four parts of the flow shape to be drawn are estimated. Each Bezier curve is made of 4 points (therefore 4 x's and 4 y's coordinates), lines only need two points.
iii. The control points for each of the points estimated in the step above are calculated in the Sheets Bezier curve A, Bezier line A, Bezier curve B and Bezier line B.
iv. Finally, in cells A28 - G52 the points and control points calculated in the steps above are collected for each part and then drawn onto the 1st sheet.

The buttons effectively perform steps 1. and 4.

Some notes to keep in mind:
A. The green flow shape already existing is manually drawn and only serves as a benchmark for the desired result.
B. You can move around the orange connector shape and then press either of the buttons that will dynamically provide a flow shape.
C. The file provided is reproduced from the original one I use at work. Therefore, the values in column N and the button Initial Positions were only used to try to reproduce the positions of the shapes from the original file, so you can ignore these.
D. In the original file, I managed to draw a shape with no bumps or other visible flows, as shown in the image attached but I could not reach the same result in the file provided and I am not sure why. Still, I provide this picture to show that the coordinates estimated by the model are correct and should not be the issue for any flaws in the drawing of the flow shape.
E. The values in column J are calculated manually to estimate the position of the points for the first Bezier curve and can be changed. Perhaps a different choice of values will fix the bumps of the flow.

Thank you so much in advance for your help, I am sorry for the long-windedness of the thread, I tried beforehand to address any possible questions you may had, I am at your disposal of course for any clarification needed.
Attached Images
File Type: jpg IMG_20230409_170645.jpg (37.6 KB, 11 views)
Attached Files
File Type: xlsm Sankey_FINAL.xlsm (52.8 KB, 0 views)
Reply With Quote
  #2  
Old 04-22-2023, 12:57 PM
Abrasax Abrasax is offline Draw proper Bezier curves to contruct flow shapes for Sankey chart Windows 10 Draw proper Bezier curves to contruct flow shapes for Sankey chart Office 2019
Novice
Draw proper Bezier curves to contruct flow shapes for Sankey chart
 
Join Date: Apr 2023
Posts: 3
Abrasax is on a distinguished road
Default

Please note that as I have been suggested by other forum members that I have cross-posted this question in the following forums:

VBFORUMS
EXCELFORUM
CHANDOO
MSOFFICEFORUMS
VBAEXPRESS

Thank you for your interest and time.
Reply With Quote
  #3  
Old 02-09-2024, 04:26 AM
Abrasax Abrasax is offline Draw proper Bezier curves to contruct flow shapes for Sankey chart Windows 10 Draw proper Bezier curves to contruct flow shapes for Sankey chart Office 2019
Novice
Draw proper Bezier curves to contruct flow shapes for Sankey chart
 
Join Date: Apr 2023
Posts: 3
Abrasax is on a distinguished road
Default

I have posted a solution in the folloiwng forum, without using bezier curves:

Draw proper Bezier curves to contruct flow shapes for Sankey chart [ SOLVED ] | Chandoo.org Excel Forums - Become Awesome in Excel
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Flip the flow chart awa123 Visio 5 11-28-2021 12:43 PM
Draw proper Bezier curves to contruct flow shapes for Sankey chart How to draw this chart Misty PowerPoint 7 05-16-2015 06:30 AM
Flow Chart works windseaker Word 0 05-02-2014 10:20 AM
Draw proper Bezier curves to contruct flow shapes for Sankey chart 'flow chart' Kitty Tambling Drawing and Graphics 1 11-22-2013 05:42 AM
Draw proper Bezier curves to contruct flow shapes for Sankey chart Cannot enlarge text within Flow chart Sleeper Word 2 05-19-2011 08:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft