Home » Shapes, ShapeSheet

How to Count the Number of Items in a List

Submitted by on May 25, 2009 – 9:23 am | | 26332 views 8 Comments

« »

count-items-in-listI’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
  • LEFT
  • LEN
  • MID
  • REPT
  • TRIM

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.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:


  • Christoph says:

    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


  • Visio Guy says:

    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

  • Christoph says:

    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

  • Visio Guy says:

    Heheh. Forgot about that. I’m not even sure anymore which shape I was talking about!

  • Christoph says:

    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?



  • Visio Guy says:

    Hi Christoph,

    Actually I did post it on August 4th, 2009. It’s this article + download: Automatic Chevron Process Shape

  • Christoph says:


    I came to same solution more or less. That’s wonderful.

    Danke & Fürti


  • […] you should really use the LOOKUP and INDEX ShapeSheet functions (for example, here and here) to do something like this. Nesting IFs ad-naseum like this is a poor way to get this particular […]

Leave a comment!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code lang=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" extra="">

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.