Calculating with Visio - 2

[Yacine]

This post was first thought to be only an answer to a question, but I think that it can be used for other purposes as well and gave it it's own post.

The shape provides a field in which you can write freely formulas referencing other shapes, without the need of opening the shapesheet.

the syntax is relatively simple to understand:
1) [shapeName] references ShapeName!prop.value
2) if you want to access a different prop, you need to write it in the shapesheet syntax ie: ShapeName!prop.RowName

It comes with 3 sub-shapes containing the name, the value and the formula.

The formula can be hidden by dragging its control under the value sub-shape.

As the controls can glue to the controls of other shapes, you can easily make calculation blocks, that show or hide the formulas.

[calkevicius]

Lets say I had 5 shapes on a page and they all had a shape data category called "Weight" could I use these blocks to sum the weight of all the shapes?

[vojo]

Yacine, I don't think that works since the shape would have convert the text
 "sheet.1!prop.weight" to sheet.1!prop.weight    // no quotes and double or triple quotes did not work circa visio 2003.
maybe visio 2019 or teams visio solves this issue.

Unless there is some new functions, years ago I tried this trick and could not get it to work since the text to formula conversion could not be accomplished.  Also, if the page is the "accumulator", then adding shapes would need to add rows and deleting shapes would mean at least a way to 0 out a row.  as I recall, even
setf(sheet.1!prop.sum, sheet.1!prop.sum+sheet.2!prop.weight) is risky since bypass visio cell database ==>pick wrong shape cell because reference picks a different shape.

if user formula function then the shape id piece would have to be identified via some discovery and place in the shape such that its a reference to other shape not a text as input into user formula.

If you, indeed, found a way, please share the details.

[vojo]

but if you could do this, it would indeed up a whole new frontier for visio.
- shape awareness (discovery, even with some manual help, of other shapes)
- hidden comm links between shapes (a hidden pipe to share all kinds of shape data or shape row values)
- assign shapes a page specific or app specific handle (e.g. sort of like DHCP for visio).
- various checks like are the right shapes on the page to do op XXX
- visiable shape replacement (could do relative position of shape A to shape B...user view of setting a group).
- visiable format of shape (if my value is X, then shape Y is half size or green because I update width and height and fill color).
-etc.

[wapperdude]

Slightly different approach.  Using SETATREF.  See attached.
Example uses a "target" shape, Sheet.1.  Could be the ThePage.  Target shape has User Defined entry, User.Wt, as accumulator.

The "weighted" shapes have shape data entry, Prop.Wt.  The value cell has SETATREF function.  The function calls target shapes current value and adds new value.  The user must enter a new value, otherwise, the SETATREF function will add the existing target value to itself, effectively doubling the value.

This approach has a major suffering...it doesnot eliminate a deleted shape from the total value in target shape.

[wapperdude]

Fixed the data entry.  No longer doubles the value. 

Work-around for eliminating a shape...enter it's negative value 1st to subtract from total.  Then, delete the shape. 
Note, dbl click shape brings up its shapedata form. 
Note 2:  if you need to change a shapes value, you need to 1st subtract existing value, then enter new value.

[vojo]

oh...so this ignores the whole text to function translation.

Thus still static set of shapes and relationships.

More a convenient user friendly way to pick which canned function.

Misunderstood, apologizes....I thought you found a variable way to find/pick a shape to and do something with it

[wapperdude]

@Vojo:  Yep. This struck me as reasonable work-around for this specific scenario and avoid VBA.  The more appropriate solution is VBA.

[Yacine]

Lets say I had 5 shapes on a page and they all had a shape data category called "Weight" could I use these blocks to sum the weight of all the shapes?

The answer is yes, but it depends on your intention.

The shape in this post is intended to show the formula behind the calculation and to allow for editing the formula without opening the shapesheet.
It is suited for summing up a fixed amount of shapes.

If your intend is to sum up all the shapes on the drawing - Vojo's and Wapperdude's interpretation - then you better go with some VBA or the report I demonstrate in the attachment.