Happy New Year 2007 Visio Fans!
Not only does the new year bring hope, rebirth, and a chance to start anew, but also a new version of Visio. Yes folks, Visio 2007 will soon be on the shelves, and beneath the shrink-wrap and behind the registration cards you’ll find an exciting new feature for ShapeSheet developers: the ability to define your own ShapeSheet functions!
By defining your own functions, you can really reduce the clutter and repeated effort that can occur with complicated ShapeSheet functions.
New ShapeSheet Functions
Visio 2007 adds two ShapeSheet functions that make the definition of custom functions possible: ARG() and EVALCELL().
- ARG allows you to define a named argument using a string. You use ARG inside of a typical ShapeSheet expression as a placeholder for a variable. It also allows you to optionally specify a default value for the variable.
- EVALCELL allows you to pass arguments to a ShapeSheet cell that contains a custom function.
The syntax for each function is as follows:
- ARG( ArgName, [DefaultValue] )
- EVALCELL( CellRef, [ Arg1Name, Arg1 ], [ Arg2Name, Arg2 ], … )
But it is easier to understand by showing a simple example. In this case we create a function that returns the square of a number. One cell holds the function, the other cell calls it:
User.fnSquared = ARG( “number”, 0 )^2
User.testFunction = EVALCELL( User.fnSquared, “number”, 6 )
You can see that the named argument: “number” is defined with the ARG function, then specified in the EVALCELL function. For this example, User.testFunction evaluates to 36.
Another way to look at it, for those who are comfortable with mathematical notation, is as follows, where I slowly substitute Visio expressions for mathematical ones. Let’s start with the algebra for a squared function:
y = f(x) = x^2
Then we make a function cell:
User.fx = ARG(“X”)^2
And finally a result cell that uses the function:
User.y = EVALCELL( User.fx, “x” ,6)
User.y = 36
Parameterized Pentagon
Now lets discuss a slightly more complicated example. I’ve created a heavily-parameterized pentagon. On the drawing page, it doesn’t look like much:
But behind-the-scenes in the ShapeSheet, you can see that there’s some magic at work:
The ShapeSheet cells are a bit easier to understand if you look at the following diagram:
The points (x, y) are calculated such that the center of the shape is also the center of the pentagon, ie: Width*0.5, Height*0.5. We then use trigonometry’s Sin and Cos functions to find the x- and y-distances from this center point.
The starting angular offset, User.dAngStart, allows us to rotate the pentagon so that the first point isn’t due-east (ie: 0 degrees.) In this case, an offset of 18 degrees gets our pentagon to sit right-side up.
The angle for each side of the pentagon is calculated using User.sides and User.dAng. Simply dividing 360 degrees by User.sides gives us User.dAng, the angle-per-side, so to speak.
The cells User.fnPtX and User.fnPtY are “custom-function cells” for locating the x- and y-coordinates of the polygon points. The expressions in these cells contain the ARG() function, and that is what makes them custom functions. ARG essentially parameterizes a formula and makes it a re-usable engine, instead of a fixed formula. For our pentagon, we pass an index to the functions, “i”, instead of an angle. The functions then figure out the angle based on this index, and calculate the coordinates for each point.
Benefits of Custom ShapeSheet Functions
Without the custom functions, we would have to copy the formulas stored in User.fnPtX and User.fnPtY to every single Geometry row. Such as pre-Visio-2007 nightmare would look like this:
click to view larger image
Egad! You can see that the new version is a lot easier to read than this beast. Plus it has the HUGE BENEFIT of allowing you to change the calculation in a central location. In our smart-pentagon’s case that would be in two formulas — User.fnPtX and User.fnPtY) — as opposed to ten formulas in the old version!
I’m sure you’ll agree that being able to define your own functions is not only COOL, but will streamline your development and debugging processes. Now if we could only get a method to self-reference the index of a ShapeSheet row!
Thanks for the excellent write-up on this feature, Chris. It really can be a powerful feature – if a bit hard to understand. We had quite a lot of debate over the naming of ARG and EVALCELL following the not-so-well-understood SETATREF and SETATREFEXPR functions in 2003.
Chris– This is a really cool example! Hope you don’t mind, I’m going to use this in my next Visio developer training I give. I’m also going to tell the class to subscribe to your great blog!
Visio Guy
Thank you for the illuminating examples; I wasn’t too thrilled about the emphasis on themes in the 2007 offering. It is nice to see the developers threw us a bone with this functionality of custom shapesheet functions.
Chris
I’m glad to see this “function”-ality (pun intended). Although it won;t help me for a while, because Intel’s *&T^%%&^%&% IT department refuses to upgrade past Visio 2003.
Still: why all this ugly, pseudo-spreadsheet, stuff? Why not provide real functions inside the smartsheet – with iterators, loops, etc?
I’m guessing the reason is something like “if you really want to do that, use VBA”.
Hi Andy,
A lot of it is historical. The ShapeSheet has been around for many years, and it has been very useful on many accounts. It allows you to get a lot of stuff done without programming, which can be seen as a feature for many reasons.
This new function-functionality is an incremental improvement. Ugly yes, but better than no improvement, I guess!
Visio has a full automation model that can be driven from VBA, VB.NET, C#, C++, etc…so like you say, do it’ wit’ VBA! 🙂
– Chris
Although the math looks correct in your parameterized pentagon, it didn’t work as expected for me. It only seemed to work if the Height & Width were 1. Otherwise, as I scaled it, the pentagon grew faster than the bounding box. And rotation was not around the center of the pentagon.
Oddly enough, I had to eliminate scaling the fnPtX & fnPtY functions based on Height & Width in order to get it to work. I ended up with these:
User.fnPtX =0.5+(0.5*COS(User.angStart+User.dAng*ARG(“i”)))
User.fnPtY =0.5+(0.5*SIN(User.angStart+User.dAng*ARG(“i”)))
I also locked the aspect ratio to make re-sizing easier.
Can you think of any reason why it didn’t scale properly for me with the original functions? And why the modified function DO scale properly? I’m a bit perplexed.
Hi John,
Were your row types LineTo or RelLineTo? If the latter, then the numbers in the cells are percentages of Width and Height. So, for example, a rectangle can go (0,0) (0, 1), (1,1), (1,0), (0,0). If the row type is LineTo, then you need Width * fx, Height * fy.
Thanks, Visio Guy. You nailed it!
I started by drawing a simple rectangle, opening its ShapeSheet, and modifying from there to create the pentagon. Visio had used RelLineTo in the ShapeSheet, and I never noticed. I just modified the parameters and added a few more rows.
Thanks again,
John
Yeah, the newer versions of Visio default to the relative “rel” geometry row types. When this article was written, the “rel” rows hadn’t been invented yet.