When building custom shapes in Visio, you can often find yourself having to construct complex formulae that follow a repeatable pattern. There are a number of ways of attacking this problem that range from writing each formula one at a time through to generating the formula strings in code…
The method you choose will depend on the number of ShapeSheet cells you need to write to, the complexity of each formula and the frequency with which you might need to repeat the process.
A middle ground, and a very fast and flexible option, is to use Excel. As an example, I recently used this approach when constructing the jQuery Mobile shapes.
RGB()
The document ShapeSheet holds a global series of colour arrays in the form of an RGB function.
These colours were derived from the five themes in the default jQuery Mobile css. As css defines colours in hex format these need to be converted to RGB values, so that Visio can make use of them.
Excel has a convenient HEX2DEC() function does the job very nicely. For example, if you a hex value of ‘85bae4’ in cell D9 then this formula (in cell E9):
="RGB(" & HEX2DEC(MID(D9,1,2)) & "," & HEX2DEC(MID(D9,3,2)) & "," & HEX2DEC(MID(D9,5,2)) & ")"
…would produce the following result string:
RGB(133,186,228)
[Note – if you’re not familiar with hex, it’s just a different number system with a base of 16 rather than 10 in decimal. The order being 0,1,2,3,4,5,6,7,8,9, followed by a,b,c,d,e and f for the 10 to 15 values. Each pair of digits in the above example (85, ba and e4) represent the respective red, green and blue colour components. So considering the blue value e4 = (14x16) + 4 = 228.]
“Double Quotes”
So that’s the colour values converted, but in order to concatenate the array of RGB functions together into a single string you need to add a delimiter character in between each one and wrap the lot in a pair of double quotes. (I’ll talk more about the delimiter character, strings and localization in the next post.)
To produce a double quote character Excel you use two double quotes surrounded by an outer pair, which is then interpreted as a single double quote – that makes sense doesn’t it! Here’s an example formula the produces the correct final string:
="=" & """" & E9 & ";" & G9 & ";" & I9 & ";" & K9 & ";" & M9 & """"
…where cells E9 to M9 contain the previous Hex to RGB formulae.
Once your first row is ready you can drag fill down the worksheet to produce hundreds of unique formulae in seconds.
I’ve used the same principle to build up the ShapeSheet user cell names, the screen size options for the Page Background shape and the Master shape meta data.
The point here is not so much how to use Excel, but to demonstrate how easy it is to produce formula data. A little code to write these cells back to Visio and you have a very flexible system for updating your shapes.
[Update 12th Oct 2011 - There's a new post up on the Excel Team blog, Tips on using seven heavenly text functions that might be useful if you've not used the Excel string functions before.]