Many languages include an IsNum / IsNumeric function to check an incoming string value, however this isn’t present in Visio’s ShapeSheet function set. A recent project required this kind of check and I thought I’d jot down one way of creating a similar function in the ShapeSheet.
The general use case for this is where you can’t be certain of an incoming value’s data type. In my case the target Shape Data row was always typed as a string (0), but another example is the variable list type (4), which enable users to extend the drop down options in a given Shape Data row.
ISERRVALUE
The solution relies on another ShapeSheet function – ISERRVALUE(). This function normally takes a cell reference as its only parameter and as long as the target cell is not in error state, it will return false. So given:
Prop.MyData = 2
ISERRVALUE(Prop.MyData) = false
If you change the test value to “banana” the result is the same:
Prop.MyData = “banana”
ISERRVALUE(Prop.MyData) = false
What you can also include, however, is a calculated value and the ISERRVALUE function will then return a result based on that calculation. So, for example, if you look at the above two scenarios, and this time include a simple addition you can see how the results change:
Prop.MyData = 2
ISERRVALUE(0+Prop.MyData) = false
and again with a non-numeric string:
Prop.MyData = “banana”
ISERRVALUE(0+Prop.MyData) = true
Using this method allows you to write a formula that includes a fallback value if the incoming value is not valid, for example:
User.CurrentValue =
IF(ISERRVALUE(0+Prop.MyData),0,Prop.MyData)
And, of course, you can also format the value at the same time:
User.CurrentValue =
IF(ISERRVALUE(0+Prop.MyData),
0 mm,
FORMATEX(Prop.MyData,"0.0000 u","mm","mm")
You could, if you’re targeting Visio 2007 or later, wrap this up into it’s own function using the EVALCELL and ARG functions:
User.fnISNUM = ISERRVALUE(0+ARG("Value","$"))
User.CallingCell =
EVALCELL(User.fnISNUM,"Value",Prop.MyData)
However, I suspect that for a single parameter like this and given how short the original formula is, just using the function directly is a little simpler. (If you’re interested in the EVALCELL function then check out Chris Roth’s post on User-defined ShapeSheet function in Visio 2007.)
So I hope this helps if you’re faced with a similar problem to solve.
