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!