Hi guys,
I've been working on this for quite a while now.
It's a tool that allows to set up formulas between shapes from a dialog.
A parser is built in so you can write freely scripts to modify your drawings.
Let me know if the presentation I prepared is somehow understandable and what you think about the tool.
Cheers,
Yacine
The first example shows a tabular arrangement of shapes using the modulus function.
The second shows the built-in random function. [?] translates in a random number between -0.5 and +0.5. So you can add a bit of chaos to your drawings.
Here I tried to get radial arrangement of shapes. But the result is not yet as expected.
And then the magical Junichi chain. I coloured it and made the width of the next shape each time smaller. Move the first shape and enjoy playing.
Keeping Junichi's idea in mind, I modified the formulas so, that several shapes can follow one parent.
Drag the end of a connector to modify the drawing.
In the second example, I injected a formula that sums values of the child shapes and translates the result in a line weight. So basically the beginning of a Sankey tool.
This one is a neat example that shows how to format a complete graph with only one script.
If I had used guard functions on the formulas, it would have been the adaptation of V2010's containers and lists for V2007.
And here are the visio files.
I would be glad if the tool could get mature and be available as stable tool to download from somewhere. Github?
I welcome everybody to colaborate.
Cheers,
Y.
Looks very interesting. Will have to "play" with it to fully appreciate your effort and the capabilities that you've captured.
Looking forward to exploring...
Wapperdude
I took the time to better present the tool.
.
.
.
.
And the nightly build version of the stencil with macros.
Got the metro script to work.
Step 1: setting up the background.
IF prop.hierarchydepth = 0 THEN
width:=thepage!pagewidth-20mm;
height:=ThePage!PageHeight-20 mm;
pinx:=thepage!pagewidth/2;
piny:=thepage!pageheight/2;
LeftMargin:=10pt;
TopMargin:=10pt;
ENDIF
Step 2: Define size and position of the shapes at hierarchyDepth=1
IF prop.hierarchydepth = 1 THEN
prop.sort := [L]!prop.sort+1;
height:=if(modulus(prop.sort,3)=0,150mm,75mm);
width:=if(modulus(prop.sort,3)=0,60mm,70mm);
locPinX:=width*0;
locPinY:=height*0;
pinX:=if(modulus(prop.sort,3)=0,if(len(prop.leftsibling)>0,[L]!pinX+[L]!width,10mm),if(modulus(prop.sort,3)=1,[L]!PinX+[L]!width,[L]!PinX));
pinY:=if(modulus(prop.sort,3)=2,[L]!PinY+height,if(modulus(prop.sort,3)=1,[L]!PinY,[L]!PinY-[L]!height));
ENDIF
Step 3: Added some cosmetic features to color the shapes.
Don't be surprised by getting white shapes, when you run the script. I did not handle the condition where the leftsibling is empty.
So re-colour the first shape to see the colours of its siblings change.
The gap between the shapes is simply given by a thicker line weight with the same colour as the parent.
IF prop.hierarchydepth = 1 THEN
LineWeight:=width/20;
LineColor:=[P]!fillforegnd;
LeftMargin:=LineWeight;
RightMargin:=LineWeight;
Fillforegnd:=HSL(HUE([L]!fillforegnd)+35,SAT([L]!fillforegnd),LUM([L]!fillforegnd));
ENDIF
I introduced new placeholders:
[PrSel] for the previous shape in the selection,
[NxSel] for the next shape,
[NumSel] for the number of shapes selected,
[Sel1] for the first selected shape and
[lastSel] for the last selected shape.
With these new placeholders arbitrary and non prepared shapes can be handled by the tool.
PinX:=[PrSel]!PinX+[PreSel]!width/2+width/2+10mm;
Would arrange the shapes in the selection with a gap of 10 mm in between.
Here are the visio files.
I'm speechless. Incredible.
Hi Yacine - I just came across this thread now, and I can see that you put an awful lot of time, talent, and creativity into this.
I'm sure it will take some time for me to look at it, play with it, and fully appreciate what you've accomplished! :o
You do nice work - you're quite an asset to your company and this forum. <highfive>
Thank you very much.
I actually had much more ideas to implement in the tool and make it easier to use (Specially a possibility to save and load scripts).
But I lost my motivation due to the lack of response from the community.
Hopefully, I'll get again bored enough, during the next holidays, to finish the job. ;)
Regards,
Y.
PS: I'll be busy with other projects in the upcoming months, but if you're willing to play, you may check some of the scripts I collected. The attached file contains also some development notes, that show where the project is aimed to go.
UNBELIEVABLE !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Great Work! Inspiring!
A big step toward a usable tool: the tool has now a storage feature, allowing to re-use scripts, without having to retype them in the tool.
As fan of Access I opted for an Access-Database to store the scripts. For those of you who don't have this software, a runtime dll can be loaded from here: https://www.microsoft.com/de-de/download/details.aspx?id=39358 (https://www.microsoft.com/de-de/download/details.aspx?id=39358)
I probable should have used a faster and lighter way - but you know me as lazy. ;)
The second upload is the DB itself with a collection of scripts I made.
Many scripts don't work properly and need editing. Well now you can do the editing by yourselves and keep the work.
Others scripts (like the text formatting, or the gap scripts) work perfectly and are very useful.
The tool is in the attached stencil. As usual: drag the square on the drawing to start the tool.
Make sure to save the DB in the same directory as the stencil.
Enjoy,
Yacine
The DB
Hello Yacine,
Thank you so much for this interesting thread. Please note that I am new to VBA and Visio, and I am trying to automatically generate a container/box view from an imported Excel in Visio.
Attached is a template of the Excel, and template of the output view in Visio, and the code that i did so far (which is not generating the correct outcome)
First part of the code available under folder Modules (Module1):
Option Explicit
Sub main()
Dim xlApp As New Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsh As Excel.Worksheet
Dim r As Long
Dim m As Long
Set xlWbk = xlApp.Workbooks.Open(("D:\OneDrive - Murex\Documents\Barometer IT\Temp\Template Excel.xlsx"))
Set xlWsh = xlWbk.Worksheets(1)
' Last filled row in column A
'm = xlWsh.Range("A65536").End(xlUp).Row
m = 4
For r = 2 To m
Dim shape As Visio.shape
Dim cont As Container
Set cont = New Container
cont.InitializeClass xlWsh.Cells(r, 2).Value
Next r
xlWbk.Close SaveChanges:=False
xlApp.Quit
End Sub
Second part of the code available under folder Class Modules(class "Container"):
Option Explicit
Public mName As String
Public mContainerShape As Visio.shape
Public Sub Class_Initialize()
Set mContainerShape = createcontainer()
'Set mSubcontainers = CreateObject("Scripting.Dictionary")
End Sub
Public Sub InitializeClass(pName As String)
'Initialize with Setter so that container text title will be set too
mName = pName
mContainerShape.Text = mName
End Sub
Public Function createcontainer(Optional shape As Visio.shape = Nothing) As Visio.shape
Dim Container As Visio.shape
Dim doc As Visio.Document
Set doc = Application.Documents.OpenEx(Application.GetBuiltInStencilFile(visBuiltInStencilContainers, visMSUS), visOpenHidden)
Set createcontainer = Application.ActivePage.DropContainer(doc.Masters.ItemFromID(2), shape)
doc.Close
End Function
I really appreciate your help and your assistance.
Please let me know for any additional information needed.
Many thanks
So I managed to solve the task. But it was harder than expected and I'm hesitant about how adequate the solution will be for you. It just requires so much know-how.
I wrote the tool in the deepest possible imaginable level of concentration and 2 years later am barely capable of reminding the details of its implementation.
One most important point is that I did not complete all the import possibilities the tool should have deserved. It had a routine in which you can select the parent/child branch and set it up to get the dependency fields filled, but all the imports from trees, files, drawings have never been implemented.
So I could have left you with the fact that you'd need to set dependencies manually (with the tool), but the task was to generate the drawing from an Excel file. So I wrote a routine that lets you choose a file, then generates automatically the dependencies.
I had however to modify the Excel file in so far as I added a column "direction" and I transformed the data range in a table. The "ParentID" column had to be renamed in "ParentID_" as my tool is already using it based on shape IDs.
The drawing itself "should" have been automated, but I opted to use the Sharem tool instead. Why? Because setting up the drawing on the basis of scripts is so much easier than hard coding them. Having scripts - and maybe several of them - gives you so much more flexibility.
So far as introduction. Now on working with the solution.
Run the macro "load_excel". It will ask you for a file to load, then it will draw rectangles and assign them both the properties of the excel file and additionaly those required by the tool.
Now you can apply the positioning (and other) scripts.
For the boxes in boxes task, I set up the script "BoxInBox3".
As for the labeling you can start from the script "insert Text2" to insert the texts that suit you the best.
The coloring was made with the "TINT2" script.
So far for my work.
I thank you for the opportunity to check and debug my work and would be very pleased if my tool will be used in usefull manner.
Cheers,
Y.
PS: I uploaded a new version of the "relations.vss" stencil. It got some minor bug fixes.
The Access-DB contains the a.m. scripts and the Box-in-Box.vsdx contains the macro to import and process the Excel data.
Don't hesitate to ask, if you need more help.
The drawing with import macro and the modified excel file ...
The updated Stencil for the main tool ...
And finally the DB containing the scripts.
@Yacine,
Amazing! thank you so much for your time and help!
Just a small question, in your comment you told me to run the macro "load_excel". I tried to look for it in the list of macros within Relations stencil, but couldn't find where it is exactly (attached screenshot of the list i have)
Additionally, in the Excel file I would like to load, i have to add manually a column "direction" before i load it? if yes, what should i include in this column?
After running the above mentioned macro, i am supposed to also run some of the scripts you mentioned? ("BoxInBox3", "insert Text2", "TINT2", etc.)
Thanks again for your assistance, much appreciated
1) Silly me! VSDX doesn't store macros. I'm attaching now a VSDM.
2) The direction column will help you to automatically set the desired arrangement direction for the item's children. 1 = columns / 0 = rows.
3) BoxInBox3 is the actual macro to arrange the shapes. Insert text and Tint are "embellishment" scripts - not really needed.
BTW, I found a small bug in the BoxInBox script.
Here's the corrected version
IF PinX exists THEN
LocPinX := 0;
LocPinY := Height;
width := 45mm;
ENDIF
IF prop.ParentID > 0 THEN
prop.ownDirection := [P]!prop.Direction;
ENDIF
IF prop.ownDirection = 0 THEN
PinX := [P]!PinX+2mm;
PinY := [L]!PinY - [L]!Height-2mm;
ENDIF
IF prop.ownDirection = 1 THEN
PinX := [L]!PinX+[L]!width+2mm;
PinY := [L]!PinY;
ENDIF
IF prop.direction = 0 THEN
Height := [SUMDESC](Height)+ TEXTHEIGHT(TheText,Width) + (prop.numDescendents + 1) * 3mm + 1mm;
Width := [MAXDESC](Width)+ 5mm;
ENDIF
IF prop.direction = 1 THEN
Height := [MAXDESC](Height)+ TEXTHEIGHT(TheText,Width) + 5mm;
Width := [SUMDESC](Width)+prop.numDescendents*5mm;
ENDIF
IF prop.LeftSibling = 0 THEN
PinX := [P]!PinX + 2 mm;
PinY := [P]!PinY - TEXTHEIGHT([P]!TheText, [P]!Width) - 1 mm;
ENDIF
@Yacine, noted
Thanks for your quick reply!
I will check it and keep you updated
Cheers!
@Yacine,
Hope all is good!
Please note that i downloaded Box-in-box.vsdm and i was able to run the macro load_excel, many thanks :)
- When i loaded an excel with around 800 rows, i received Run-time error '6' Overflow (attached screenshot)
- So for now, i only included 3 rows in the Excel to test the output. I ran the macro load_excel, and the boxes were properly created (but not included within each other as parent/child which is expected). Next i need to run the macro BoxinBox3 as you explained right? since this macro will be responsible of placing the shapes correctly within each other based on their relations.
I searched for this macro, i didn't find it? can you help me locate it?
- Additionally, should i update the script BoxinBox3 (in the access file i have) with the updated code you shared with me?
- One last question: i see that the data displayed in each box is listing all the info within the Excel columns, if i only want to display the info contained within column "ID", "Label" and "Type" without the other columns, how can i do so?
Thanks!!
Right,
you're now at the step where you should use the actual Sharem tool.
To do so, drag the yellow square of the stencil "Relations" onto the drawing. The tool dialogue should open.
Select the "scripts" tab and push the "load scripts" button. (The access DB must be in the same folder as the stencil).
You should now see the lists of scripts.
Play with them and try to understand them. You may also want to read the posts above.
@Yacine
Thank you so much for the provided tips! I managed to run the script BoxinBox2 after loading the Excel file (i checked the different scripts, and noticed that BoxinBox2 is the closest to what we want)
Just one thing, the text of the parent box is hidden by the child boxes (example of the attached screenshot)
Do you think anything can be done about this? If not, it's okay, i will check if this can be done manually
Many thanks!
@J_M,
Sorry that BoxesInBoxes 3 did not meet your expectations. I thought it would be better fit than the buggy version 2.
Anyway, you can adjust the text with the following script - it makes the text top and left aligned and sets the text margins.
IF PinX exists THEN
Para.SpLine := -100%;
Para.HorzAlign := 0;
VerticalAlign := 0;
LeftMargin := Char.Size * 0.5;
RightMargin := Char.Size * 0.5;
TopMargin := Char.Size * 0.5;
BottomMargin := Char.Size * 0.5;
Height := TEXTHEIGHT(TheText,Width);
ENDIF
You may want to:
1) delete the "Height" line to get shapes of equal height.
2) you may need the adjust the PinY property of the child shapes by increasing its value relatively to their parent's.
Cheers, Y. ;)
@Yacine,
Thanks for your reply!
I added the part you shared with me to the code (and removed the previous If/else statement), but apparently I am still missing something because the boxes are not yet properly set.
Below is the current code i have in BoxinBox2:
IF PinX exists THEN
Para.SpLine := -100%;
Para.HorzAlign := 0;
VerticalAlign := 0;
LeftMargin := Char.Size * 0.5;
RightMargin := Char.Size * 0.5;
TopMargin := Char.Size * 0.5;
BottomMargin := Char.Size * 0.5;
Height := TEXTHEIGHT(TheText,Width);
ENDIF
IF prop.LeftSibling = 0 THEN
PinX := [P]!PinX + 2 mm;
PinY := [P]!PinY - 7 mm;
ENDIF
IF prop.LeftSibling > 0 THEN
PinY := [L]!PinY;
ENDIF
IF prop.HierarchyDepth = 0 THEN
Height := [MAXDESC](Height) + TEXTHEIGHT(TheText,Width)+ 5mm;
ENDIF
Any advise on what else should I update?
Thank you so much for your time
Your code:
The following is for formating the text. Put it in a separate script, in order to concentrate on the layout task:
IF PinX exists THENPara.SpLine := -100%;
Para.HorzAlign := 0;
VerticalAlign := 0;
LeftMargin := Char.Size * 0.5;
RightMargin := Char.Size * 0.5;
TopMargin := Char.Size * 0.5;
BottomMargin := Char.Size * 0.5;
Height := TEXTHEIGHT(TheText,Width);
ENDIF
This block means that the first child should follow its parent in both vertical and horizontal direction.
It does not consider the text of the parent
IF prop.LeftSibling = 0 THEN
PinX := [P]!PinX + 2 mm;
PinY := [P]!PinY - 7 mm;
PinY := [P]!PinY - TEXTHEIGHT([P]!TheText,[P]!Width) - 2 mm;
ENDIF
This block means that all children except the first one, shall follow the first sibling in vertical direction.
PinX is missing
IF prop.LeftSibling > 0 THEN
PinY := [L]!PinY;
PinX:= [L]!PinX + [L]!Width + 5mm;
ENDIF
This block sets the height of the very first item in the hierarchy.
The height of the children is missing
IF prop.HierarchyDepth = 0 THEN
IF PinX exists THEN (just a trivial test that applies to any 2D shape)
Height := TEXTHEIGHT(TheText,Width)+ 5mm; (this formula will always work)
Height := [MAXDESC](Height) + TEXTHEIGHT(TheText,Width)+ 5mm; (The formula will only work if the item has children and will overwrite the line above.)
ENDIF
What about the width? -->
IF PinX exists THEN
Width := [SUMDESC](Width) + (prop.numdescendents+ 1)*2mm
ENDIF
final script:
IF prop.LeftSibling = 0 THEN
PinX := [P]!PinX + 2 mm;
PinY := [P]!PinY - TEXTHEIGHT([P]!TheText,[P]!Width) - 2 mm;
ENDIF
IF prop.LeftSibling > 0 THEN
PinY := [L]!PinY;
PinX:= [L]!PinX + [L]!Width + 2mm;
ENDIF
IF PinX exists THEN
Height := TEXTHEIGHT(TheText,Width)+ 5mm;
Height := [MAXDESC](Height) + TEXTHEIGHT(TheText,Width)+ 5mm;
Width := [SUMDESC](Width) + (prop.numdescendents + 1) * 2mm+5mm;
ENDIF
@Yacine , Works great! thank you so much for all your assistance and for this useful thread !!