David Parker from over at bVisual stole the Viso Freak Crown with this newsgroup post a few days ago. His answer solves the problem of how to count the number Geometry Sections in the ShapeSheet, and it uses a really loooooooong ShapeSheet formula!
David’s solution is to add the following formula to a User-cell. Get ready now, hold on to your hats, ’cause this one’s a whopper:
User.GeoSectionCt =
ABS(NOT(ISERR(INT(INDEX(0,”Geometry1.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry2.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry3.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry4.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry5.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry6.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry7.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry8.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry9.NoFill”))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry10.NoFill”)))))
It’s not so bad if you break it down. If your web browser is just the right width, you’ll get nice line-wrapping that exposes the “simplicity” of the formula. It should then be clear that the formula simply repeats this element:
ABS(NOT(ISERR(INT(INDEX(0,”Geometry1.NoFill”)))))
with changes to the index after “Geometry”, incemented for each piece of the formula. The maximum number of Geometry Sections for a shape is 230, so you can extend this formula that far, if you want(!)
My suspicion is that there was a need to count the number of legs in a Dynamic Connector, but that’s purely speculation on my part 🙂
Hi
Thanks – good site.
I attempted to use a variation of the above formula to count the number of legs in my dynamic connector & pasted into the user section as recommended.
=2+ABS(NOT(ISERR(INT(INDEX(0,”Geometry1.X3″))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry1.X4″))))) + ABS(NOT(ISERR(INT(INDEX(0,”Geometry1.X5″)))))
The formula works fine if I click on it & edit it, but doesn’t update. I haven’t had this problem before with Visio shapesheet cells. Do you have any idea why it isn’t working?
Thanks
As you modify the line the line geometry section can add or subtract rows based on the number of vertices the line has. But your formula won’t recalculate because the formula only recaclulates when the data changes. Your formula does not detect this change. To fix you need to add something to your formula that creates a change but not interfere with your desired results. I used the second row of the Geometry1.X2 to create a change at the end of your formula. e.g.:
=SETF(GetRef(User.Keep2),2+ABS(NOT(ISERR(INT(INDEX(0,â€Geometry1.X3″))))) + ABS(NOT(ISERR(INT(INDEX(0,â€Geometry1.X4″))))) + ABS(NOT(ISERR(INT(INDEX(0,â€Geometry1.X5″))))) + (Abs(Geometry1.X2)-(Abs(Geometry1.X2)
when Geometry1.X2 changes the formula will be recalculate. The SETF(GetRef(User.Keep2)…… pushes the recalculated Geometry row count into the user defined parameter User.Keep2 for use by other functions that use the row count.
I have tried using this, with the additional part using the Geometry1.X2 changes, but it still doesn’t seem to fully update with shape changes. Also, you have used SETF to “push” the data into the cell, whereas the person with the 1st post was putting the calculation into the actual “User” cell itself. Does it have to be “pushed” there with SETF in order to update? In which shapesheet cell would you recommend putting your formula with “SETF”?
Hi guys,
I see the problem about the formula not updating. For counting the geometry rows in the Dynamic Connector, I was able to add this term to the beginning of the formula:
This works because just about any change in the connector changes the position of the text block. DEPENDSON doesn’t return a value, it just triggers an update if the argument-cells change. So a complete formula would look like this:
If you’re not testing the connector, I’m still not sure what the best way is to get the formula to recalculate.
– Chris
I’ve tried this, but if say geometry1.x5 doesn’t exist it removes the reference and so the calc stops working. Any thoughts?
hi there
i think i got an other way with a bit less code for that.
what do you think about that workaround:
=CEILING((((SEGMENTCOUNT(Geometry1.Path))+2)/2))
Best Regards, Novski
Hi Novski,
Thanks for that! The SEGMENTCOUNT function was added for Visio 2013, and I think I wrote this article when Visio 2007 was just coming out(!)