I’ve been working on a new multi-shape that you will hopefully find useful for presentations and process documentation. The Automatic Chevron Process Shape uses a new ShapeSheet technique for counting items in a list that I’ll share with you here.
As I was building it, I was faced with the challenge of calculating how many items are in a list.
It’s easy enough to enter “One;Two;Three;Four” into a shape’s text or into a ShapeSheet cell, but how do you know that there are four items in the list?
Manipulating Lists and Text in the ShapeSheet
Any time you start messing around with lists in the ShapeSheet, you’ll employ the list functions: INDEX and LOOKUP to help you out. INDEX will help you find a specific item in a list and LOOKUP will tell you the position of an item in a list.
User.index = INDEX( 2, "A;B;C" ) = "C" ...0-based! User.lookup = LOOKUP( "B", "A;B;C" ) = 1 ...0-based!
But if you are turning the visibility of geometry or sub-shapes on and off in response to the number of items in a list, the task gets trickier.
The Visio ShapeSheet has a number of text-manipulation functions. If you haven’t discovered them yet, here’s a list to tempt your curiosity:
- BLOB
- CHAR
- INDEX
- LEFT
- LEN
- LISTSEP
- LOOKUP
- LOWER
- MID
- REPLACE
- REPT
- REWIDEN
- RIGHT
- SHAPETEXT
- STRSAME
- STRSAMEEX
- SUBSTITUTE
- TEXTHEIGHT
- TEXTWIDTH
- TRIM
- UNICHAR
- UPPER
That’s a good set of tools, but unfortunately there isn’t a function for counting the numbers of a list, so it is difficult to build a SmartShape that can automatically accommodate a varying number of list items.
How NOT to Count Items
When I first thought about tackling this problem, I thought; “Oh no, I’m going to have to do some sort of brute-force hack to figure this out.” Perhaps a block of user cells that test for the existence of an “ith” item.
- If there’s no 2nd item in the list, then there is 1 item
- …
- If there’s no 5th item in the list, then there are 4 items
- If there’s no 6th item, then there are 5 items
- …
- If there’s no 20th item, then there are 19
Rather ugly and inelegant, but it would work. And since the ShapeSheet is a fixed matrix that can’t really do loops, there are a lot of cases where you have to pre-build a number of elements, cells, or formulas so this isn’t totally out of the question.
But you’d end up with a less-than-beautiful bank of “what-if” user-cells that did something like this:
User.items = "A;B;C" User.isItem0 = IF( ISERR( 0 + INDEX( 0, User.items, ";", 0 ) ), 1, 0 ) User.isItem1 = IF( ISERR( 0 + INDEX( 1, User.items, ";", 0 ) ), 1, 0 ) User.isItem2 = IF( ISERR( 0 + INDEX( 2, User.items, ";", 0 ) ), 1, 0 ) User... User.total = User.isItem0 + User.isItem1 + User.isItem2 + ...
These formulas are a bit convoluted, to say the least. The extended form of INDEX specifies the list separator “;” and a return value if no item is found (the zero). So if an item is found, a string is returned, if not then zero is returned. We then check for the error of adding 0 to a string using ISERR. If we get an error, that means we found an item. What a hack!
And the whole thing breaks if the items list contains actual numbers, a-la: “1;2;3” because Visio tries to make numerical sense of strings if at all possible, so the whole ISERR bit will fail.
Bottom line: don’t use the above method!
Count Smarter!
Then I thought about the length of the list. The number of characters in the list is the number of characters in all the items, plus “items minus one” times the length of the list-separator. The length of “A;B;C” is three-plus-two. Then length of “One;Two;Three;Four” is fifteen-plus-three.
Well, we know the length of our list-separator, since we define it ourselves. And we can get the length of the entire list using the LEN function. Then we can substitute “” for our list-separator to get the length of the list without the list-separators in it.
Here’s how we would calculate the difference in lengths:
User.items = "A;B;C" User.noSep= SUBSTITUTE( User.items, ";", "") = "ABC" User.diff = LEN( User.items ) - LEN( User.noSep) = 2
So the number of items is User.diff + 1 = 3.
We can streamline this:
User.items = "A;B;C" User.itemCt = LEN( User.items )-LEN( SUBSTITUTE( User.items, ";", "" ) ) + 1
This can be generalized to allow for any type of list-separator, of any length:
User.items = "A;B;C" User.sep = ";" User.itemCt = ( LEN( User.items )-LEN( SUBSTITUTE( User.items, User.sep, "" ) ) ) / LEN( User.sep ) + 1
You could replace User.sep with Prop.Sep so that users of the shape could enter their own list-separator via a Shape Data field (custom property field). And User.items could also come from a Shape Data field or even the shape’s text!
Related Articles
If you are interested in shapes that “grow” according to user-input, you might be interested in the following articles. The free downloads in these posts don’t use the list-counting method I’ve outlined here, but they could be easily adapted to use it, if a designer wanted to manipulate the shapes with data lists.
Have a look at:
I’m working on a similar shape and found your article very interesting.
I guess that you never released this shape?
Is it possible to get this from you if you still have it?
Thank you
Christoph
Servus Christoph,
There was no shape to release. The article tells how to implement a feature in Visio’s ShapeSheet…in whatever shape you would like!
– Chris
I was referring to
“It should be on offer within then next week or two.”
I’m currently thinking of drawing sub-shapes (without visio) based on an user cell entry of a shape.
Let’s assume I have a field of hyperlinks in a user cell separated by semicolons.
What I want do to is do have this hyperlinks as a list vertically aligned left or right beside the shape and the user should be able to click each hyperlink even after I exported the graphic as html.
Do you think this is possible without vba?
Thank you
Christoph
Heheh. Forgot about that. I’m not even sure anymore which shape I was talking about!
So, you’ve forgotten this shape
That’s live.
Anyway do you think that it is possible to solve the other part of my mail?
Regards
Christoph
Hi Christoph,
Actually I did post it on August 4th, 2009. It’s this article + download: Automatic Chevron Process Shape
Cool.
I came to same solution more or less. That’s wonderful.
Danke & Fürti
Christoph