Indexed colors across languages in Visio

If your users operate in different system languages then you need to consider what happens to your formulas when a document, authored in one language, is edited in a different one.

Problem - Color becomes black when edited in another language

A common scenario in Visio is that you want to have a shape change color based on some state - usually in Shape Data.

There are lots of approaches to solve this, two of the main ones employing the IF and INDEX functions.

For both of these options you might add a Shape Data cell that holds the value you want to map to, Prop.Status, and also an index cell, User.StatusIndex, that allows you to abstract the actual value to an index.

The IF function

Using an IF function you could create a lookup to match the index to a color like this:

This is a robust solution that will work perfectly across languages. However, even for three colors, it's not easy to read and therefore maintain and, if you start to add more colors, the problem just gets worse. (For the screenshot I've added line breaks to make it more readable, but in the ShapeSheet you'll see this as a single line.)

The INDEX function

The alternative then, is to use the INDEX function.

Here you add an intermediate cell to hold the palette of colors, User.Colors, and User.Color references the status index to select just one of the colors from the palette list.

This also works well as long as your users are working with a single language. There's a problem lurking, however, if that's not the case.

Suppose that you create a document in an English based system (en-UK) and then open it on a French one (fr-FR). Although Visio changes the local RGB functions to use French comma separators instead of English semi-colon ones, a CellChanged event is not fired and so the result is not re-evaluated. This means that you have a string representation of the RGB function that cannot be evaluated and so the resulting fill color of the shape becomes the default: 0 (black).

Universal and Local formulas

So why is this happening?

When opening a document in a non-English language, Visio converts some formulas from their English (US), or universal, representation to a their local one.

The ShapeSheet only shows universal syntax, but the underlying cells have local and universal syntax, and this applies to both the formula and result properties.

For example, here's the same shape that you looked at above and I've written some quick code, using LINQPad, to report the Formulas and Results for three cells. This is what it looks like with the system language set to English.

Note that the RGB argument separator is a comma.

If you then open the document with French set as the system language, you'll see this:

Notice now that the local Formula (not FormulaU) has changed to use the French argument separator, the semi-colon, and the universal FormulaU keeps using the comma as before.

Also notice that the local ResultStr (not ResultStrU) continues to use the comma argument separator and so when User.Color looks up the color in the User.Colors list it also retrieves an RGB function string with those incorrect separators.

At this stage no changes have been made to the Shape Data and the FillForegnd cell remains red, as it should be.

The problem becomes visible when you attempt to edit the Status property - User.Color is re-evaluated, but with a string that FillForegnd can't parse and so the cell now defaults to black.

Units

What appears to happen, is that when Visio opens a document in a non-English language it will attempt to convert some local formulas based the cells data type or units.

For string type cells Visio doesn't know what the purpose of the cell is, and so (I believe), doesn't fire a change event.

The type of a cell is really the result of the section that it's housed in and its formula that, in turn, depends on the functions and operators used within the expression.

RGB, for example, returns a color (visUnitsColor) but the ampersand operator (&), used to concatenate the list returns a string and so User.Colors is also a string (visUnitsString) type.

The INDEX function returns a string (visUnitsString) and so User.Color is, again, a string.

Solution - ensure string cells are re-evaluated

So, if the problem is that User.Colors doesn't see a change when a language change occurs on open then we need some way to trigger the re-evaluation when the Shape Data is changed.

To achieve this you can append the Shape Data cell (Prop.Status) to the end of the palette list and this ensures that every time that data cell changes so the colors list will also re-evaluate and pickup the current local RGB syntax.

Here's an example of the amended shape opened and edited, with French set as the system language. You can see the correct color is interpreted from the local string representation in User.Color, to its local and universal counterparts in FillForegnd:

Summary

So, to ensure color lists and the INDEX function continue to work across languages just append a reference the data cell they're associated with to ensure that updates happen correctly.