Home » Code, ShapeSheet

Stress Test: Nesting “IF” Functions in the ShapeSheet

A Visio Guy forum member asked the question: “How many nested IF statements can I stuff into a ShapeSheet formula?”

“Good question!”, I thought and went off to investigate!

Rather than get all scientific about it, I thought I’d just create a big, ugly test case and see if Visio could handle it.

If you want to push it further, there is code further below in the article that you can modify to create even larger nestings of IF statements.

My test document nests 50 IF statements together to create a sort of look-up function for all 50 U.S. states, in the order that they were admitted to the Union. I am pleased to announce that the ShapeSheet can handle 50 nested IF statements. If you need more than that, then you are either The Man! or you need to find a better algorithm!

In the test document, the shapes present the user with drop-down lists in the Shape Data window. The numbers 1 through 50 appear, corresponding to the order that the territories officially became states. When you choose a number, a huge formula inside the shape’s ShapeSheet (called: User.StateName) finds the state’s name according to the index chosen by the user.

The shape is easy to look at, to use, and to understand. Pick a number, get a state! Looks like Delaware was our first:

The shapes are quite simple on the outside, but the inner-workings of their ShapeSheets are terrifying! A humongous, inefficient formula containing 50 nested IF functions is used to match text to number.

Fortunately, Visio 2010 has a nifty new Edit Formula button on its ShapeSheet Tools ribbon that makes it easier for us to examine the formula. If you’ve got Visio 2010, you can click it:

This pops up a handy text window for editing long formulas. You can add line breaks to get a clearer view of your formula, as I’ve done here:

Phew!

That’s one ugly formula, isn’t it? And we’re only seeing the first 23 out of 50 IFs. And yes, the very end of the formula looks like this: ))))))))))))))))))))))))))))))))))))))))))))))))))

Unfortunately, those line-breaks and beautiful white space don’t stick around after you hit OK, but thanks to MS for taking a step in the right direction!

Making changes to a formula this long in the formula bar, or by editing “in-cell”, would be just about impossible. When I look at this monster in the formula editing bar at the top of the ShapeSheet window, it is about 10 screen-widths long on my laptop! Editing it in-cell would require even more scrolling!

You can read more about Visio 2010’s ShapeSheet enhancements here.

Note: 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 job done. However, there may be cases when you really need to use IFs.

Yes, Visio 2010 has added formula-editing enhancements, but if you think I typed that formula in by hand, then…well…I didn’t! Ok?

I wrote a bit of VBA code to help me create the formula. The process involved scraping data form Wikipedia, massaging it into a comma-separated list, then adding quotes and line-breaks so that it could be fed to a VB array. Here’s the whole listing, with a few comments even!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
Sub CreateIfStatements()
 
  Dim shp As Visio.Shape
  Set shp = Visio.ActivePage.Shapes(1)
  
  '// List of states in order of statehood:
  Dim names As String
  names = "Delaware;Pennsylvania;New Jersey;Georgia;" & _
        "Connecticut;Massachusetts;Maryland;South Carolina;" & _
        "New Hampshire;Virginia;New York;North Carolina;" & _
        "Rhode Island;Vermont;Kentucky;Tennessee;Ohio;" & _
        "Louisiana;Indiana;Mississippi;Illinois;Alabama;" & _
        "Maine;Missouri;Arkansas;Michigan;Florida;Texas;Iowa;" & _
        "Wisconsin;California;Minnesota;Oregon;Kansas;" & _
        "West Virginia;Nevada;Nebraska;Colorado;North Dakota;" & _
        "South Dakota;Montana;Washington;Idaho;Wyoming;Utah;" & _
        "Oklahoma;New Mexico;Arizona;Alaska;Hawaii"
  
  Dim namesArray As Variant
  namesArray = Split(names, ";")
  
  Dim numberList As String
  Dim num As Integer
  num = 1
  
  '// Generate a nested IF formula using a loop and
  '// string concatenation:
  
  Dim fBegin As String, fEnd As String
  Dim i As Integer
  For i = LBound(namesArray) To UBound(namesArray)
  
    '// Note: Chr(34) is the double-quote character.
    
    fBegin = fBegin & "If(Prop.StateNumber=" & num & "," & Chr(34) & _
    namesArray(i) & Chr(34) & ","
    fEnd = fEnd & ")"
    
    '// Create a semi-colon-separated list for Prop.StateNumber
    If (num > 1) Then numberList = numberList & ";"
    numberList = numberList & num
    num = num + 1
  
  Next i
  
  '// Add the format-list formula (ie: 1;2;3;4;5;6...) so that
  '// the user can choose from a drop-down list of the numbers
  '// 1 through 50:
  shp.Cells("Prop.StateNumber.Format").FormulaForceU = _
  Chr(34) & numberList & Chr(34)
  
  Debug.Print
  Debug.Print fBegin & fEnd
  Debug.Print
  
  '// Stuff the big, long formula into the User.StateName cell:
  shp.Cells("User.StateName").FormulaForceU = fBegin & fEnd
 
End Sub
Sub CreateIfStatements()

  Dim shp As Visio.Shape
  Set shp = Visio.ActivePage.Shapes(1)
  
  '// List of states in order of statehood:
  Dim names As String
  names = "Delaware;Pennsylvania;New Jersey;Georgia;" & _
        "Connecticut;Massachusetts;Maryland;South Carolina;" & _
        "New Hampshire;Virginia;New York;North Carolina;" & _
        "Rhode Island;Vermont;Kentucky;Tennessee;Ohio;" & _
        "Louisiana;Indiana;Mississippi;Illinois;Alabama;" & _
        "Maine;Missouri;Arkansas;Michigan;Florida;Texas;Iowa;" & _
        "Wisconsin;California;Minnesota;Oregon;Kansas;" & _
        "West Virginia;Nevada;Nebraska;Colorado;North Dakota;" & _
        "South Dakota;Montana;Washington;Idaho;Wyoming;Utah;" & _
        "Oklahoma;New Mexico;Arizona;Alaska;Hawaii"
  
  Dim namesArray As Variant
  namesArray = Split(names, ";")
  
  Dim numberList As String
  Dim num As Integer
  num = 1
  
  '// Generate a nested IF formula using a loop and
  '// string concatenation:
  
  Dim fBegin As String, fEnd As String
  Dim i As Integer
  For i = LBound(namesArray) To UBound(namesArray)
  
    '// Note: Chr(34) is the double-quote character.
    
    fBegin = fBegin & "If(Prop.StateNumber=" & num & "," & Chr(34) & _
    namesArray(i) & Chr(34) & ","
    fEnd = fEnd & ")"
    
    '// Create a semi-colon-separated list for Prop.StateNumber
    If (num > 1) Then numberList = numberList & ";"
    numberList = numberList & num
    num = num + 1
  
  Next i
  
  '// Add the format-list formula (ie: 1;2;3;4;5;6...) so that
  '// the user can choose from a drop-down list of the numbers
  '// 1 through 50:
  shp.Cells("Prop.StateNumber.Format").FormulaForceU = _
  Chr(34) & numberList & Chr(34)
  
  Debug.Print
  Debug.Print fBegin & fEnd
  Debug.Print
  
  '// Stuff the big, long formula into the User.StateName cell:
  shp.Cells("User.StateName").FormulaForceU = fBegin & fEnd

End Sub

So now we know that the ShapeSheet can handle 50 nested IF functions. If you need more than that, let us know in the comments section below. I’d be interested to know WHY!?

And if any intrepid investigators out there find an upper-limit, let us know that too!

Nested IF Statements in ShapeSheet (50.38 kB) - 1094 downloads
Help support Visio Guy!

Note: donations are not required for this download. However, we really appreciate any help we can get!

Feel free to try out the download first, then donate afterwards if you find it really useful. Thank you!

2 Comments »

  • Karin says:

    Hey there, Visio guy, great post. I love how the “And yes, the very end of the formula looks like this: ))))))))))))))))))))))))))))))))))))))))))))))))))” kind of shimmer in the text.

    I’m looking to do this for a fill color on a shape based on the value that the user types into a field (like department), and it seems to work the first time, but after that (in other words, if the user changes the value), it doesn’t recalculate (or whatever it’s called) to reflect the changed value, and therefore, the color. For example, if dept. is “it”, then fill with red, otherwise fill with blue. Type IT in the dept field, and the shape is now red. Type “finance” in the department field, and the field is still red, rather than turning to blue. hmm. I’m going to read your related post now on Index and Match.

  • Visio Guy says:

    Hi Karin, I fixed those links so they at least point somewhere now! This one is probably the most useful for what you are doing: Choose Colors with Shape Data Fields

    The idea behind this post was just to “stress test”, not to recommend a technique. But it also lets people know that nested IF statements are possible in the ShapeSheet.

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.

*