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

Started by Hey Ken, January 22, 2020, 03:41:43 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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



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



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



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



   ...with...



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



Ken V. Krawchuk
Author
No Dogs on Mars - A Starship Story
http://astarshipstory.com

wapperdude

Searching for needle in the haystack are you?

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

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).
Yacine

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


Ken V. Krawchuk
Author
No Dogs on Mars - A Starship Story
http://astarshipstory.com