In the previous post we examined how the SetAtRef function could be used to keep two Shapesheet cells synchronized. Today we will explore a few advanced capabilities of SetAtRef for manipulating cell values.
Our scenario is a shape with a custom property called Size. We would like this property to control the Width of the shape. Also changes to the Width should update the Size property. A text field displays the current value of the Size property as well.
Notice that the cell values for Width and Prop.Size are not the same. Our previous solution using SetAtRef to synchronize the cells will not work. One unit of Size should correspond with 0.25 inches of Width. What we need is a way to convert between Size and Width as we are synchronizing the cells.
A first attempt might look like this:
We adjust the Width formula to take the Size value and convert it to a width. This works properly whenever the Size changes, but any change to the Width will push the wrong value back to Size. We need to take the incoming Width value and perform a conversion on it before that value is pushed to the Prop.Size cell.
Fortunately, the SetAtRef function supports this capability. You can provide an optional second argument to the function that tells Visio how to transform the incoming value. Our goal is to take the incoming Width value and divide it by the proper conversion factor to calculate the Size. To specify this as an expression in the SetAtRef function, we need a way of referencing the incoming cell value. Visio provides the SetAtRefExpr function for this purpose. SetAtRefExpr starts with no function arguments. Then Visio places the incoming cell value into the function. Like SetAtRef, for recalc SetAtRefExpr evaluates to its function argument: SetAtRefExpr(x) = x.
This almost solves the problem. However, if you look below at the resulting value put in the Prop.Size cell, you can see that Visio is putting the literal expression in the cell rather than the calculated result of the expression. The formula in Prop.Size does evaluate to 4, but we would prefer to have just the final result.
There is another function available to force Visio to evaluate the SetAtRef expression before a value is put in the cell. SetAtRefEval can be used on the entire second argument in SetAtRef or on one or more portions of the expression.
This completely solves our problem. The shape now is able to keep the Width and Size properties synchronized while converting between the two. Our final formula for the Width cell is:
SETATREF(Prop.Size,SETATREFEVAL(SETATREFEXPR(1.25 in)/0.25 in))*0.25 in
Here is a verbose description of this formula. When the Width of the shape changes, do not overwrite the contents of this cell. Place the new Width value into the SetAtRefExpr function argument. Then take the new value and divide it by 0.25 inches. Evaluate that result and place the it in the Prop.Size cell. Then recalculate the Width cell. For recalculation take the current value of Prop.Size and multiply it by 0.25 inches. This is the final value of the Width cell.
As you can see SetAtRef and its related functions allow fairly sophisticated manipulations of cell contents. The result for the user is simply intelligent shape behavior. Next time we’ll look at a few more ways that SetAtRef can be used.