Number of arguments limited for the OR shapesheet function. Maybe others?

[Hey Ken]

Folks:

   As I've mentioned far too many times, I often find myself on the edge of Visio functionality, and today I fell off another edge.  Did you know that the shapesheet function OR has a limit to the number of arguments it accepts?  Well, I didn't; but I found out today.  It's 128. 

   But it gets worse, because Visio doesn't tell you when you've hit the limit.  If you have an OR with 129 arguments, when you hit the Enter key it only keeps the last argument and throws away all the others!  AND NEVER REPORTS AN ERROR!!!   How rude.

   Here's what my VBA was trying to add to the document's shapesheet's User cell...

Code

IF(OR(FALSE,STRSAME(User.Set,User.84__DataColor),STRSAME(User.Set,User.58__DataColor),STRSAME(User.Set,User.56__DataColor),STRSAME(User.Set,User.4__DataColor),STRSAME(User.Set,User.49__DataColor),STRSAME(User.Set,User.48__DataColor),STRSAME(User.Set,User.29__DataColor),STRSAME(User.Set,User.25__DataColor),STRSAME(User.Set,User.90__DataColor),STRSAME(User.Set,User.82__DataColor),STRSAME(User.Set,User.81__DataColor),STRSAME(User.Set,User.80__DataColor),STRSAME(User.Set,User.79__DataColor),STRSAME(User.Set,User.78__DataColor),STRSAME(User.Set,User.77__DataColor),STRSAME(User.Set,User.73__DataColor),STRSAME(User.Set,User.72__DataColor),STRSAME(User.Set,User.70__DataColor),STRSAME(User.Set,User.69__DataColor),STRSAME(User.Set,User.68__DataColor),STRSAME(User.Set,User.67__DataColor),STRSAME(User.Set,User.30__DataColor),STRSAME(User.Set,User.26__DataColor),STRSAME(User.Set,User.89__DataColor),STRSAME(User.Set,User.88__DataColor),STRSAME(User.Set,User.87__DataColor),STRSAME(User.Set,User.86__DataColor),STRSAME(User.Set,User.85__DataColor),STRSAME(User.Set,User.76__DataColor),STRSAME(User.Set,User.75__DataColor),STRSAME(User.Set,User.74__DataColor),STRSAME(User.Set,User.66__DataColor),STRSAME(User.Set,User.65__DataColor),STRSAME(User.Set,User.64__DataColor),STRSAME(User.Set,User.63__DataColor),STRSAME(User.Set,User.62__DataColor),STRSAME(User.Set,User.61__DataColor),STRSAME(User.Set,User.60__DataColor),STRSAME(User.Set,User.59__DataColor),STRSAME(User.Set,User.55__DataColor),STRSAME(User.Set,User.50__DataColor),STRSAME(User.Set,User.47__DataColor),STRSAME(User.Set,User.40__DataColor),STRSAME(User.Set,User.37__DataColor),STRSAME(User.Set,User.36__DataColor),STRSAME(User.Set,User.97__DataColor),STRSAME(User.Set,User.96__DataColor),STRSAME(User.Set,User.95__DataColor),STRSAME(User.Set,User.71__DataColor),STRSAME(User.Set,User.54__DataColor),STRSAME(User.Set,User.43__DataColor),STRSAME(User.Set,User.42__DataColor),STRSAME(User.Set,User.35__DataColor),STRSAME(User.Set,User.34__DataColor),STRSAME(User.Set,User.24__DataColor),STRSAME(User.Set,User.94__DataColor),STRSAME(User.Set,User.93__DataColor),STRSAME(User.Set,User.92__DataColor),STRSAME(User.Set,User.91__DataColor),STRSAME(User.Set,User.83__DataColor),STRSAME(User.Set,User.81__DataColor),STRSAME(User.Set,User.80__DataColor),STRSAME(User.Set,User.79__DataColor),STRSAME(User.Set,User.78__DataColor),STRSAME(User.Set,User.77__DataColor),STRSAME(User.Set,User.73__DataColor),STRSAME(User.Set,User.72__DataColor),STRSAME(User.Set,User.70__DataColor),STRSAME(User.Set,User.69__DataColor),STRSAME(User.Set,User.68__DataColor),STRSAME(User.Set,User.67__DataColor),STRSAME(User.Set,User.30__DataColor),STRSAME(User.Set,User.26__DataColor),STRSAME(User.Set,User.89__DataColor),STRSAME(User.Set,User.88__DataColor),STRSAME(User.Set,User.87__DataColor),STRSAME(User.Set,User.86__DataColor),STRSAME(User.Set,User.85__DataColor),STRSAME(User.Set,User.76__DataColor),STRSAME(User.Set,User.75__DataColor),STRSAME(User.Set,User.74__DataColor),STRSAME(User.Set,User.66__DataColor),STRSAME(User.Set,User.65__DataColor),STRSAME(User.Set,User.64__DataColor),STRSAME(User.Set,User.63__DataColor),STRSAME(User.Set,User.62__DataColor),STRSAME(User.Set,User.61__DataColor),STRSAME(User.Set,User.60__DataColor),STRSAME(User.Set,User.59__DataColor),STRSAME(User.Set,User.55__DataColor),STRSAME(User.Set,User.50__DataColor),STRSAME(User.Set,User.47__DataColor),STRSAME(User.Set,User.40__DataColor),STRSAME(User.Set,User.37__DataColor),STRSAME(User.Set,User.36__DataColor),STRSAME(User.Set,User.97__DataColor),STRSAME(User.Set,User.96__DataColor),STRSAME(User.Set,User.95__DataColor),STRSAME(User.Set,User.71__DataColor),STRSAME(User.Set,User.54__DataColor),STRSAME(User.Set,User.43__DataColor),STRSAME(User.Set,User.42__DataColor),STRSAME(User.Set,User.35__DataColor),STRSAME(User.Set,User.34__DataColor),STRSAME(User.Set,User.24__DataColor),STRSAME(User.Set,User.94__DataColor),STRSAME(User.Set,User.55__DataColor),STRSAME(User.Set,User.50__DataColor),STRSAME(User.Set,User.47__DataColor),STRSAME(User.Set,User.40__DataColor),STRSAME(User.Set,User.37__DataColor),STRSAME(User.Set,User.36__DataColor),STRSAME(User.Set,User.97__DataColor),STRSAME(User.Set,User.96__DataColor),STRSAME(User.Set,User.95__DataColor),STRSAME(User.Set,User.71__DataColor),STRSAME(User.Set,User.54__DataColor),STRSAME(User.Set,User.43__DataColor),STRSAME(User.Set,User.42__DataColor),STRSAME(User.Set,User.35__DataColor),STRSAME(User.Set,User.34__DataColor),STRSAME(User.Set,User.24__DataColor),STRSAME(User.Set,User.94__DataColor),STRSAME(User.Set,User.93__DataColor),STRSAME(User.Set,User.92__DataColor),STRSAME(User.Set,User.91__DataColor),STRSAME(User.Set,User.83__DataColor),STRSAME(User.Set,User.33__DataColor)),User.Set,User.Reset)

   ...and when you hit the Enter key, here's what Visio actually put into the User cell...

Code

IF(OR(STRSAME(User.Set,User.33__DataColor)),User.Set,User.Reset)

   It only kept the last argument!  And no error!!  Fortunately, you can nest the ORs so I could replace groups of arguments, such as...

Code

STRSAME(User.Set,User.58__DataColor),STRSAME(User.Set,User.56__DataColor)

   ...with...

Code

OR(STRSAME(User.Set,User.58__DataColor),STRSAME(User.Set,User.56__DataColor))

   ...and that works as expected.  I assume each nested OR can have its own 128 arguments, meaning I can have 16,384 total arguments, then nest the nests 128 times.  But it's a pain to code because I need to count how many arguments I have before I can add another.  To repeat: How rude!  But at least I have a solution. 

   I assume the same problem exists with many other similar functions, like AND.  I didn't test that; I figure I'll wait until the next time Visio bites me again when I dance too close to the edge.  I should've followed Yacine's and Vojo's advice from years ago.

   I'm using Visio 2013 Professional, by the way.  Your mileage may vary.

   - Ken

[wapperdude]

Searching for needle in the haystack are you?

There are more effective algorithms, than a sequential, one at a time approach.

[vojo]

good visio practice for complex functions is to use several user cells to build up the function.
Much easier to debug and get around this issue.

A lot of people use something like user.x1 or user.y2....comments explain what the cell is trying to do.

Also, index function can help reduce complexity

I believe a cell can have 127 characters  ( visio guy post from 10 years ago I believe)
that probably means something like 10-15 variables.

[Yacine]

If any possible, you may want to consider the following structure.

Instead of many user.xx__DataColor have ONE user field as semi-colon separated values.
Then you can check whether user.set is within the given list.

INDEX(LOOKUP(user.set,user.DataColor), user.DataMapped)
This works with very long lists (tested with 250+ items).

[Hey Ken]

Folks:

   Thanks for the suggestions, as always. 

   I’m at the end of this project and only found the Visio bug during final, full-up testing, so rather than re-work how the color connection is built, I took the easy way out and only modified the few lines of code that add another _DataColor.  Specifically, on every 100th addition (i.e., "If Count Mod 100 = 0"), I change the beginning from “IF(OR(FALSE” to “IF(OR(FALSE,OR(FALSE” and change the end from “),User.Set,User.Reset)” to “)),User.Set,User.Reset)” using a pair of VBA Replace functions, and new values continue to be added at the beginning, right after the “IF(OR(FALSE”, as it does now.  Problem solved – until I get 12,801 of them, that is.  But I’m only expecting a few hundred, so that day’s a long way off, if ever.  And should that day arrive, I have your fine examples to guide me.

   FYI, the problem I’m solving is to highlight one specific shape on a main page whenever any one of hundreds of shapes on many other pages are individually highlighted.  The main shape only reverts to normal when every one of the other shapes returns to normal. 

   Thanks again for now – at least until I stumble over the next edge-dweller’s problem, that is.

   - Ken