Detect Drawing Units in the ShapeSheet

Detecting the units of a drawing page can be useful for creating reactive SmartShapes that can behave nicely in both Metric or US-unit environments.

Summary

I’ve developed a ShapeSheet technique for determining if a Visio drawing page is set up to use Metric units, U.S. units, or other types of units. This information could be used to build shapes that react to their environment, and offer appropriate options and sizes for the target audience.

If you’re here specifically for the nitty-gritty ShapeSheet information, jump down to section A New Hope and skip the prose! If you’re new to Visio’s nerdy side, then you might appreciate the background information that follows.

Also, be sure to check out the comments at the bottom. Some ingenious ShapeSheeters have suggested some simpler methods, that may work for your situation.

The Path to the Problem

One of my current projects is to update a large number of Visio shapes that are part of a product that integrates tightly with Visio and AutoCAD.

Along with trying to improve consistency, add new features, and improve existing ones, the question of proper support for metric units (aka: Most of the World) has come up. As the company has grown, non-US customers play an ever-increasing role in the organization’s fortunes.

Measured Multi-shapes

A lot of the shapes we’ll be reworking are mostly symbolic in nature – even when they are used in measured drawings like plan-view floor-plans. Think electrical outlet symbols, light switches, exit lights, speaker locations, etc. I don’t think users are overly worried about a 1-inch shape showing up as 2.54mm in these cases. These shapes simply indicate “this thing goes roughly here” on the measured floor-plan.

But some of the shapes represent real-world items and have real-world sizes: modular cable raceways, shelving, rack cabinets, rack equipment, etc. These shapes often have drop-down lists of standard sizes. Perhaps you’ve created a floor-plan in Visio and run across window or door shapes. If you live in the USA, you’ve likely encountered the door on the right, with its built-in list of inch-sizes. The rest of you might have seen the door on the left, which has a list of metric door sizes:

Notice above how the text on the door shapes appears when dropped into the US-units template (top) and the Metric template (bottom).

While 2’-11 7/16” is indeed 900mm, it’s a bit of a shock to look at. Similarly, there probably aren’t a lot of 762mm-wide doors for sale out in the metric world—unless you somehow get your hands on a 2’-6” door!

How to Manage Shape Sets having Different Unit-types

If we have users all around the globe, how should we handle the problem? There are essentially two strategies that I can think of for creating shapes that can handle both worlds:

1. Create two sets of shapes, and give them different, unit-friendly default sizes, and lists of sizes.
2. Build one shape that can switch between “Metric and Imperial” or “Metric and U.S.” units.

 

1. Create Two Sets of Shapes, Manage Two Stencils

Microsoft ships Visio with two separate sets of stencils. I think you can select which sets to install, but I typically install “everything”. If you look deep within your program files directory, you’ll find the MS-supplied Visio stencils and templates. They typically live in one of these directories, depending on the “bit-idness” of your system:

• %PROGRAMFILES(X86)%\Microsoft Office\root\Office16\Visio Content\1033
• %PROGRAMFILES%\Microsoft Office\root\Office16\Visio Content\1033

Note: if you’re in the U.S. and run an English system, you’ll probably have a 1033 subdirectory. Other countries will have a different Locale ID [link: https://msdn.microsoft.com/en-us/library/ms912047(WinEmbedded.10).aspx]. For example, Croatia will have 1050, the U.K. will have 2057, and Germany has 1031. Also, your Office16 might be something similar, but different as well.

If you browse to your Visio Content folder, you may see two entries for every stencil and template. One set ends with _M for Metric, the other ends with _U for U.S.-units.

When you create a new drawing, you might encounter the choice to create either a Metric Units or U.S. Units flavor of drawing:

Each template will open with the corresponding set of _M or _U stencils, of course.

Maintaining two sets of shapes can be quite the headache if you anticipate numerous changes to the shapes. The fiddly-tweaky-dink-around nature of ShapeSheet programming can make it difficult to ensure that both versions end up with the same features.

Say I add some super-fancy new behavior to the metric door. I had better be sure to write down every ShapeSheet cell and formula that I add, along with any new geometry and formatting, so that I can accurately re-create that exact same intelligence in the US-version of the shape. Joy of joys!

If you’ve got the time and skill, you could write nifty “Shape Differ” tool that can find the differences, ignore the metric-vs-us-units differences, but magically transfer the newly-added ShapeSheet stuff to the other shape. Probably worth the time investment, the more I think about it. Maybe some existing developer tool could be coerced into helping with the job.

2. Create One Set of Reactive Shapes

To avoid having to double-up on ShapeSheet edits, you could build shapes that can be switched to be either Metric or US. Theoretically, this would “only” involve storing two lists of measurements for any measured qualities of the shape, plus some sort of switch to tell the shape to be one type or the other.

You would also need some sort of technique for making the default drop size change as well. This might be linked to one of the size lists, or might not.

The missing piece, however, is how to determine the “units type” of a drawing page. There is no ShapeSheet function to do this. Even with automation programming, it’s not the simplest thing to determine. But I’ve come up with a method to do this, and that is described in the next section.

Note: the sharp-eyed among you might have noticed some interesting values in drop-down lists on the Page Size and Drawing Scale tabs of the Page Setup dialog. These settings map to two ShapeSheet cells in a Visio Page’s page ShapeSheet:

DrawingSizeType =

visPrintSetup, visTight, visStandard, visCustom, visLogical, visDSMetric, visDSEngr, visDSArch

and

DrawingScaleType =

visNoScale, visArchitectural, visEngineering, visScaleCustom, visScaleMetric, visScaleMechanical

The “metric” suffixes seem promising, but there isn’t quite enough information to determine if the page is intended for a Metric or US audience.

A New Hope

Sorry, I couldn’t resist trying out Visio’s 3D Perspective feature on that section header:

 

Thoughts of how to detect the category of units in the ShapeSheet have wafted in and out of my head over the years, but I never put in a serious effort. With the current project at hand, I wanted to give it a bit more effort. So I dug in.

Which Units are THE Units?

First, I determined that the units of a page are defined by the DrawingScale cell in the page. Visio pages can have a drawing scale, which is defined by two ShapeSheet cells: PageScale and DrawingScale. Think of PageScale as a length on the actual printed paper, while DrawingScale relates to the real-world physical size of an object.

A (technical) user could conceivably set the scale of a page to be the very strange:

1cm = 1’-0”

The PageUnits are 1cm and the DrawingUnits are one-foot-zero-inches. The “units of the page” will be feet-and-inches, despite the one centimeter. If you draw a rectangle on such a page, you will see feet-and-inches displayed in the status bar at the lower-left, as well as in the Size & Position dialog.

My initial experiments had shapes referring to ThePage!DrawingScale, until I realized that the PinX, PinY (2D box-like shapes) or BeginX, BeginY, EndX, EndY (1D line-like shapes) ShapeSheet cells almost always take on a DrawingUnits value when dropped on the page. (Only shapes that have these cells GUARDed with explicit values won’t float their location-defining unit-values.)

So that was the first simplification – unit-analysis could use a local cell value from the Pin, or Begin or End cells, and eliminate the “cross-sheet” reference to the page’s ShapeSheet.

Analyzing the Text of a Unit-value Pair

Now, Pin and Begin/End values are unit-value pairs, so how could we get just the unit portion of the expression?

If PinX = 20.75cm, I need the last two characters. If PinX = 0.2075m, I need the last character. If PinX = 2’-3”, well, that gets really tricky. Sometimes you see “in”, sometimes you see “in.” (with a period). It seemed pretty precarious, and difficult for a non-procedural programming environment like a spreadsheet engine.

So I created a few User-defined cells in the ShapeSheet of some poor guinea-rectangle, and started fiddling around.

First multiplied PinX by zero:

PinX = 20.75cm

User.Units = 0*PinX —> 0.0000cm

At least this will be similar for all quantities, but how to get rid of all those zeroes?

I then tried some concatenation with an empty string with the value:

User.Units = “” & 0 * PinX —> 0CM.

That’s close. We don’t have “0.0000” anymore, just “0”. Also, note the capital letters and the period in “CM.”.

The ShapeSheet has a SUBSTITUTE function, so we can replace the zero with blank:

User. Units = SUBSTITUTE(“”&0*PinX, “0”, “”) —> CM.

We are very close! Time to start testing different DrawingScales. What happens with different PinX values on pages with different unit settings? I tried a few different settings:

3.33 m —> M.

5.67 in —> “ (inch marker)

7ft 6 3/4in —> ‘ (feet marker)

7′ 6 3/4″ —> CM.

2.3 yd —> YD.

Usually a one or two capital letters, followed by a period. Also the ‘ and ” tick marks for feet or inches. The concatenation made things a bit easier because it resulted in consistent abbreviations for units. You don’t get “cm” or “cm.” or “Cm” or “Cm.”, but always “CM.”.

In total, Visio supports these seventeen units:

1. Inches
2. Feet
3. Miles
4. Picas (1/6 inch, 12 points)
5. Points (1/72 inch)
6. Yards
7. Millimeters
8. Centimeters
9. Miles
10. Kilometers
11. Ciceros (French points)
12. Didots (French picas)
13. Elapsed seconds
14. Elapsed minutes
15. Elapsed hours
16. Elapsed days
17. Elapsed weeks

When confronted with values in the above units, my User.Units function returns these possible values:

‘ “” MI. P PT. YD. MM. CM. M. KM. C D ES. EM. EH. ED. EW.

Our goal is to essentially categorize the units of a page as “Metric”, “US” or “other”. We can use the simple set of abbreviations above to:

1. Find the abbreviation
2. Lookup the abbreviation in a list of categories
3. Get the index of the category in a list to return a number that we can “do things with”.

So I needed a set of unit-categories, and came up with these.

U = US Units

M = Metric units

F = French units (ciceros and didots)

T = Time (the “elapsed” time unitsthat Visio supports)

You could probably roll F and T into O for Other.

Then we need a list of categories that matches each entry in the units list. I started using a vertical bar/pipe symbol as a list separator. This is easier to read, and helps avoid some “international” problems where list separators are different:

Units: ‘|””|MI.|P|PT.|YD.|MM.|CM.|M.|KM.|C|D|ES.|EM.|EH.|ED.|EW.

Category: US|US|US|US|US|US|M|M|M|M|F|F|T|T|T|T|T

That’s all we need. After testing out in a bunch of separate user cells, I condensed the whole mess into one formula, so that you could copy and paste.

Putting it All Together

It all boils down to:

User.UnitCategoryValue = LOOKUP(INDEX(LOOKUP(SUBSTITUTE(""&0*PinX,"0",""),
"'|""|MI.|P|PT.|YD.|MM.|CM.|M.|KM.|C|D|ES.|EM.|EH.|ED.|EW.","|"),
"US|US|US|US|US|US|M|M|M|M|F|F|T|T|T|T|T","|"),"US|M|F|T","|")

Note: for the formula above, be sure to remove the line-feeds, OR use Visio’s Edit Formula pop-up window under ShapeSheet Tools > Design > Editing > Edit Formula. If you still have trouble, make sure that there are no “fancy quotes” in the formula, in case my blog software has decided to mess with my text!

For your eyes’ sake, we can break it out a bit:

User.UnitCategoryValue =

LOOKUP

(

INDEX(

LOOKUP(

SUBSTITUTE(“”&0*PinX,”0″,””),  // The numberless unit

“‘|””|MI.|P|PT.|YD.|MM.|CM.|M.|KM.|C|D|ES.|EM.|EH.|ED.|EW.”,”|”

), // Index of unit in the unit list

“US|US|US|US|US|US|M|M|M|M|F|F|T|T|T|T|T”,”|”

), // Category abbreviation

“US|M|F|T”,”|” // Category value: 0, 1, 2, 3 or -1

)

Note: the ,”|” above is an optional argument for LOOKUP and INDEX that specifies what we are using as a list separator.

This function returns 0, 1, 2, 3 or -1. You can place this formula in the prompt cell to the right of the main User-calculation. It serves as a comment/reminder:

User.UnitCategoryValue.Prompt = “<– 0=US, 1=M(etric), 2=F(rench), 3=T(ime), -1 = Not Found”

Just wrap expressions in double-quotes to enter text in ShapeSheet cells!

You could also build these simple tests:

User.IsUS = (User.UnitCategoryValue=0)

User.IsMetric = (User.UnitCategoryValue=1)

You might want to make one or the other the default, since a typical solution won’t have need for “French” units, nor time-valued units.

Note that there are some double-double-quotes in the expressions above. This is how you enter a single double-quote as a string value in the ShapeSheet. Since the lists are text-between-quotes, the inches-symbol needs to be double-double-quotes.

 

Breaking Down the Big, Ugly Formula

The breakdown of the scary formula above might be easier to follow with the flowchart that separates the individual steps:

Caveats

One caveat: There are some granular choices for the page units of a Visio page that this formula won’t detect. For example you can choose between fractional inches and decimal-inches or miles and decimal-miles. You can also specify feet, inches or feet-and-inches. The formula won’t make a distinction for these cases. But in both cases, the result is a US-unit, so from a category standpoint, everything should be fine.

I also noticed that just changing the units via [right-click page tab] > Page Setup > Page Properties > Measurement Units didn’t always update the result of the calculation. However moving (or dropping/copying) the shape should force a recalculation. I would think that normally, a shape needs to determine its environment when it is dropped into a page, and that page is unlikely to switch between Metric and US-units thereafter.

Practical Use

Armed with your mighty, unit-detecting formula, you can now build, say, a door shape that works well in US or metric environments! The door could be similar to the one that comes in the Walls, Shell and Structure (WALL_M.VSSX or WALL_U.VSSX) stencil. The size of the door is controlled by an editable drop-down list in the Shape Data panel, where the user can pick from a set of standard values, or type in a custom value.

Shape Data rows have a Format cell in the ShapeSheet where lists of values are defined.

So, instead of having two separate shapes that have culture-specific lists:

Prop.DoorSizes.Format = “60cm;80cm;100mm;120mm”

and

Prop.DoorSizes.Format = “24in;32in;40in;40”

We could have a single shape that has a “smart list” that reacts to its parent page:

Prop.DoorSizes.Format = IF( User.UnitCategoryValue = 1, “60cm;80cm;100mm;120mm”, “24in;32in;40in;40” )

 

Happy ShapeSheeting!