## User-defined ShapeSheet Functions in Visio 2007

**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