List Separators in Visio’s ShapeSheet are Crazy!

Are you developing Visio solutions that will be used across the Globe? Have you experienced strange behavior using ShapeSheet functions like INDEX, LOOKUP, SUM, RGB or others that involve lists of items?

If so, then you, my friend, are a victim of list-separator craziness!

 

 

 

Of Babel and Punctuation

Not every country and culture on Earth use commas and periods the same way as Americans do. Shocking, I know!

Some countries use the semi-colon to separate lists. So instead of writing:

Chris, Ralph, George, John

they write:

Kris; Rolf; Georg; Johann

See the semi-colons in there between the names? Verrückt, oder?

Similarly, decimal separators and thousands-separators also change across borders. In the U.S., your monthly pay-stub might say something like:

$8,503.28

But in Germany, it might say

€6.298,72

Note the reversal of period and comma for separating thousands and decimals.

Many years ago, the designers of Visio decided that it was a bad idea for the ShapeSheet to have localization, and changed everything to be “universal” or “invariant culture”.

This meant that inside of the ShapeSheet, all cell-names would be in English, and work would be done with “U.S. Settings”. Commas for separating lists and thousands groups, and dots for indicating the decimal location.

But even after years of fiddling with this and doing Visio-based work for clients all across the globe, the story remains unclear to me. Part of the problem is the fact that you can “program” Visio shapes using the ShapeSheet. And you can program Visio using languages such as VBA, VB.NET and C#. Plus you can programmatically set ShapeSheet formulas and values using those languages. So many avenues where culture settings might cause problems!

Such a problem recently cropped up when I posted the article: Choose Colors with Shape Data Field. The ShapeSheet technique described there uses all sorts of fancy lists of lists and lookups and indexes to allow users to select colors from a drop-down list. But it was discovered that it didn’t work in Germany. This is because the German list-separator is a semi-colon instead of a comma.

Variable Soup

Adding to the fun is the matrix of possibilities that you can have that affect list separators on your system, and how they are handled by the software you are using. Things to consider include:

• Language of operating system
• Language-settings in operating system
• Language of installed Visio

I normally use US Visio on a US Windows 7 machine. But I can switch my culture to German quite easily. This doesn’t change the language on my system, but it does change my list-, decimal- and thousands-separators in one fell swoop.

If you go to Control Panel, then Clock, Language and Region, you’ll see the Change the date, time or number format option:

The Region and Language screen pops up, where you can choose another language/country from the Format drop-down:

If you click Additional Settings…, you’ll see the list-separator for the locale that you’ve chosen:

This offers a convenient way to do some testing, without building a virtual pc with a completely foreign environment.

Let’s Go Europe (…European ShapeSheet)

Now that I’ve set up Euro-list-separator settings, I can go have a look at my ShapeSheet to see if anything is different. Here I’ve added a couple of User-defined cells that use list-containing functions.

Here you can see the formulas I’ve entered. I’ve entered the same expression in the Value and Prompt cells, because the Prompt cell is geared more for displaying text values, and sometimes things look slightly different. So far, my Euro-US Visio setup looks completely normal:

US Visio, US Windows 7, German Settings – ShapeSheet Formulas

When I switch to values, the Prompt cell for User.testColor looks a bit different. See the semi-colons?!

US Visio, US Windows 7, German Settings – ShapeSheet Values

My conclusion is that Visio maintains some sort of distinction between pure value of a ShapeSheet formula, and the value of that formula as text. Remember that User.testColor and User.testColor.Prompt hold the same formula, but they display different results!

Note, that though the results look a bit different, referring to either cell works just fine. So if I set:

FillForegnd = User.testColor

or

FillForegnd = User.testColor.Prompt

My shape still turns a nice, glowing orange color.

But with these settings, the problem in: Choose Colors with Shape Data Field did raise it’s ugly head.

I then decided to try “programming” a cell from within the ShapeSheet. Using the SETF function, you can blast a formula into another cell, and Visio will do it’s best to evaluate the result. So I set up the shape so that a double-click would send an RGB formula to User.testColor:

EventDblClick = SETF(GetRef(User.testColor),”RGB(255;255;255)”)

Note the semi-colons in the RGB-formula. For US Visio, with German settings the above expression worked fine. Setting a ShapeSheet cell with an apparently localized formula worked just fine. So much for universal ShapeSheeting!

Curiously, this formula didn’t work!

EventDblClick = SETF(GetRef(User.testColor),”RGB(255,255,255)”)

Using SET to jam a “universal” formula into the ShapeSheet doesn’t work if the machine’s settings don’t match the universal/invariant culture. Hmm. Is this a bug?

When I switched back to US-English settings, then the comma-separated RGB formula could be jammed via double-clicking the shape, and the semi-colon-separated formula failed. At this point, that was to be expected. The problem is that designing Visio shapes for multiple cultures that use SETF to stuff formulas containing lists is difficult. The claim of a universal ShapeSheet falls short.

Off to Viking Land

I dug up a Virtual PC image that had US Windows XP with Danish Visio. I chose to use Danish settings and see if anything was different. The Danes have the same setup as the Germans: semi-colons separate lists, commas indicate decimals, and periods separate thousands.

With the same ShapeSheet cells as before, my formulas looked the same:

Danish Visio, US Windows XP, Danish Settings – ShapeSheet Formulas

And so did the values, with the strange difference between Value-cells’s display and Prompt-cell’s display:

Danish Visio, US Windows XP, Danish Settings – ShapeSheet Values

Setting FillForegnd to either User.testColor or User.testColor.Prompt also resulted in a nice, bright orange-filled shape.

And the formula-stuffing double-click behavior?

EventDblClick = SETF(GetRef(User.testColor),”RGB(255;255;255)”)

works just fine, and

EventDblClick = SETF(GetRef(User.testColor),”RGB(255,255,255)”)

doesn’t work. Just as in my US-German configuration.

So it seems you can simulate “being in Europe” by simply choosing separator settings of Germany, Denmark, or the like. No need to create  complete in-country virtual system images.

ShapeSheet-ing Via Code

If you are programming a Visio solution and setting ShapeSheet values from your code, I’ve cobbled together an example that deals with the various cases.

Take a gander at this, noting the comments and the various list-separators used in the RGB formulas:

Sub Set_Formulas_For_SemiColon_Locales()
 
  '// Comment out the sections that don't apply to your situation:
 
  Dim shp As Visio.Shape
  Set shp = Visio.ActiveWindow.Selection(1)
 
  '// If list-separator is ";", then this will work:
  shp.Cells("User.testColor").Formula = "RGB(255;120;0)"
  'shp.Cells("User.testSum").Formula = "SUM(1;2;3;4;5;6;7;8;9;10)"
 
  '// If list-separator is ",", then this will work:
  shp.Cells("User.testColor").Formula = "RGB(255,120,0)"
  shp.Cells("User.testSum").Formula = "SUM(1,2,3,4,5,6,7,8,9,10)"
 
  '// This will work, no matter the list-separator, due to
  '// the "U" in "FormulaU":
  shp.Cells("User.testColor").FormulaU = "RGB(255,120,0)"
  shp.Cells("User.testSum").FormulaU = "SUM(1,2,3,4,5,6,7,8,9,10)"

End Sub

Note the last section uses FormulaU instead of Formula to set the formulas. The “U” stands for “Universal” and is part of the initiative to alleviate cultural worries when developing on top of Visio. Using FormulaU, you can use the US-style formulas and set the formulas no-matter the culture-settings of the target machine.

Or at least I hope so. There is a nagging voice inside my head that says even FormulaU won’t save you in every case. If I remember the example, I’ll come back and update this article with the additional info!