• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Visio Guy

Smart graphics for visual people




  • Home
  • Hire Me
    • Hire Me
    • Résumé
  • Products
    • Products
    • Bubble Revision Shape
    • Layers to Pages Utility
    • Rack Unit Dimension Line
    • Radial Elements Tool with up to 100 Wedges
    • Text on a Circle Visio SmartShape
  • Index
    • Articles by Date
    • YouTube – VisioGuy
    • Download Information
    • Suggestion Box
    • Shop
    • Visio Art
    • Visio Links
    • – Visio Shapes & Stencils
    • – Visio Templates & Drawings
  • About
    • About
    • Donate
    • GitHub
    • jsFiddle
    • Reading List
    • Subscribe & Follow
      • – E-mail
      • – facebook
      • – Twitter
      • – RSS
    • Privacy Policy
  • Discussion Forum
You are here: Home / Development / Code / Stress Test: Nesting “IF” Functions in the ShapeSheet

Stress Test: Nesting “IF” Functions in the ShapeSheet

July 10, 2010 By Visio Guy 3 Comments

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!

Download “Nested IF Statements in ShapeSheet”

s!Aj0wJuswNyXlhXhUGMTVOf8b_bDo – Downloaded 2192 times – 103.00 B
  • Tweet
  • More
  • Pocket
  • Share on Tumblr
  • Print
  • Email

Related posts:

  1. Visio Nerd Videos – For Developers!
  2. Forefront Icons and Data Graphic Shapes
  3. Title Block #2: Let’s Make a Multi-field SmartShape!
  4. Visio SmartShape for Inauguration Day 2009
  5. Add Spice to Your Visio Skills With Randomization!

Filed Under: Code, ShapeSheet Tagged With: ShapeSheet Formulas, ShapeSheet Functions

Previous Post: « Visio 2010 Flowchart Shapes Get Smarter
Next Post: SharePoint 2010, PowerPivot, Visio 2010 & Real Estate »

Reader Interactions

Comments

  1. Karin says

    January 17, 2015 at 8:56 am

    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.

  2. Visio Guy says

    January 19, 2015 at 4:10 pm

    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.

  3. Sai says

    June 24, 2019 at 12:04 pm

    Hi Visio Guy,

    I have a doubt in visio shape sheet formula. My doubt is: I used guard function to some of the sub shapes. Now I want to modify the size of sub shape so that the other sub shapes should also modify according to the size of the sub shape user is modifying.

Leave a Reply Cancel reply

Primary Sidebar

Buy Über Rack Unit Dimension Line
Article — Purchase

Categories

Buy my book!

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Tag Cloud

A/V Artistic Effects BPM Code Connectors Control Handles Countries Custom Patterns Custom Properties Data Graphics Data Linking Data Visualization David Edson David Parker Fill Format Formulas Functions Geometry Gradient Images Links Maps Multi-shapes Network Programming repeating shapes Resources Right-Click Actions Scale Shape Data ShapeSheet ShapeSheet Formulas ShapeSheet Functions SharePoint shiny SmartShapes Sport Sports Text Themes Tools Transparency User-defined Cells Visio 2007 Visio SmartShapes

Top Posts & Pages

  • - Visio Shapes & Stencils
  • - Visio Templates & Drawings
  • Bubble Revision Shapes
  • Amazon AWS Visio Shapes
  • Sankey Diagram Shapes for Visio
  • Visio Network Server Shape Icon Customization Tool
  • Dynamic Updating Org Charts in Visio!
  • Go 3D with Free Isometric Piping Shapes for Visio
  • Text to the Bottom of the Shape
  • Free Visio People Shapes

www.visguy.com - Visio Guy - since 2006

 

Loading Comments...