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:
https://learn.microsoft.com/en-us/office/vba/api/excel.shapes.addcurve
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):
Some notes to keep in mind:
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:
https://learn.microsoft.com/en-us/office/vba/api/excel.shapes.addcurve
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):
- Fix the resulting shape when the “One shape” button is pressed into a smooth one (no bumps).
- Merge the 4 lines into a uniform shape that can properly filled with color when the “Separate lines” button is pressed.
- 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).
- 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.
- 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”.
- 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.
Some notes to keep in mind:
- The green flow shape already existing is manually drawn and only serves as a benchmark for the desired result.
- You can move around the orange connector shape and then press either of the buttons that will dynamically provide a flow shape.
- 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.
- 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.
- 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.
