Writing ShapeSheet Formulas

ShapeSheet cells can contain either a value or a formula, and you can alternate the view to show one or the other. A formula is the expression in a cell that evaluates to a value. Visio 2007 adds some default formulas to shapes; a shape inherits other formulas from a master shape. And then there are the formulas that you can create by typing in a ShapeSheet cell or on the formula bar, as Figure 25-3 shows. A formula always starts with an equal sign (=), which Visio 2007 inserts automatically.

When you edit a cell’s formula, you change the way that Visio 2007 calculates the value of the cell. The result is that shape behavior changes. For example, you can edit the formula in the TxtAngle cell of the Text Transform section by typing 45 deg. and then pressing Enter. The result is that the angle of the shape’s text block is rotated 45 degrees on the drawing page. You can accomplish the same feat with the Text Block tool, so what’s the difference? Editing formulas becomes much more interesting when you realize that you can calculate a shape’s geometry or appearance dynamically. That is, you can write formulas to adjust the value of one cell based on the value of another. This feature is the heart of SmartShapes programming.

Contents of a Formula

A Visio 2007 formula can contain the following elements:

• Coordinates Most formulas are expressed as coordinates for controlling the position and size of a shape. A coordinate describes the position of a vertex with respect to the origin of a shape and to the origin of the group or page that contains the shape.

• Numbers In a formula, numbers specify a wide range of values, such as measurements and angles. Constants represent numbers that map to predefined Visio 2007 settings, such as those used to represent themes and colors. A number can include a plus (+) or a minus (–) sign and can be entered in exponential notation (for example, 1.2E-6) or as a fraction (for example, 5 1/16).

• Units of measure Because many values in Visio 2007 are dimensional (for example, width in inches or angle in degrees), many formulas include measurement units after a number. If you don’t specify a unit of measure, Visio 2007 uses default units of measure in cells that require a unit of measure.

• Cell references A formula can contain the name of another cell, the value of which is then used in the formula.

• Functions Like Microsoft Office Excel functions, Visio 2007 functions perform a task. Visio 2007 includes mathematical, trigonometric, logical, date and time, statistical, and other functions.

• Operators and parentheses Formulas typically include mathematical operators (+ for addition, – for subtraction, * for multiplication, / for division) and parentheses that control the order of operation. Formulas can also contain the Boolean expressions TRUE and FALSE.

• Strings A string is simply text in a formula, usually set off with quotation marks.

For example, the default formula that Visio 2007 enters for the position of a shape’s pin (its center of rotation) is stored in the LocPinX and LocPinY cells and looks like this:

LocPinX=Width*0.5
LocPinY=Height*0.5

What does this tell you? In the top formula, the LocPinX cell represents the x-coordinate of the pin—that is, its horizontal location. Width is a cell reference and refers to the Width cell in the Shape Transform section and defines a measurement, such as 1 inch. The asterisk (*) symbol is the operator for multiplication. So, the horizontal location of the pin is the point that is half the value of the Width cell. In short, it’s in the middle. The formula in the LocPinY cell is similar but defines the vertical distance of the pin in terms of the shape’s height. Suppose you select the Rotation tool on the drawing page and use it to move the pin, thereby changing the shape’s center of rotation. Visio 2007 writes new formulas into these cells to describe the pin’s new position.

Creating and Editing Formulas

To create a formula, you can select a cell and type, just as you can select a shape and type. Similarly, you can double-click a cell to display the insertion point so that you can edit a formula. You can also use the formula bar, which works much like the one in Excel. It’s somewhat easier to type on the formula bar, because you can see more of your formula at once.

Follow these steps to type a new formula for a cell:

You can edit formulas on the formula bar, much as you do in an Excel spreadsheet. Table 25-3 describes how.

Creating a Cell Reference in a Formula

Cell references provide a powerful way to create interdependent behavior in a shape. You can set one cell to a particular value based on the setting in another cell—in the same ShapeSheet or in the ShapeSheet for another object, including the page. You must follow Visio 2007’s naming syntax, which describes the rules for correctly referring to another cell. It’s easy to refer to another cell in the same ShapeSheet. When you’re typing a formula, you can click in another cell to add a reference to that cell in the formula, as Figure 25-4 shows.

It gets a little trickier when you want to refer to another object’s ShapeSheet cell. Then you must know the object’s identifier or name and use an exclamation point (!) to separate the object name from the name of the cell like this:

Sheet.12!Width

Visio 2007 displays an object’s identifier or name on the title bar of the ShapeSheet window, but you can also select an object, and then choose Format, Special, to display the identifier or name. (Choose Tools, Options, select the Advanced tab, and turn on the Run In Developer Mode check box to see the Format, Special command.) An object’s identifier, such as Sheet.12, doesn’t change unless you move the object to another page or document.

Entering Functions

You can perform a variety of calculations using functions. Functions are really just predefined formulas that use specific values, called arguments, in a particular order. For example, the SUM function adds a series of numbers, and the UPPER function transforms text into uppercase letters. The structure of a function (that is, its syntax) requires you to use parentheses around the arguments. For example, the GOTOPAGE function uses the following syntax:

GOTOPAGE(pagename)

The GOTOPAGE function creates a link to another page specified by the pagename argument. You can use the name that appears on the page tabs at the bottom of the drawing page window. Because the page name is a string, you must enclose it in quotation marks. A valid formula using the GOTOPAGE function would look like this:

GOTOPAGE("page-2")

Follow these steps to quickly add a function to a ShapeSheet formula:

Protecting Shape Formulas

In other chapters, Visio 2007’s built-in protections have been discussed—features such as protection locks and other safeguards that prevent you from making changes to shapes. The equivalent mechanism in a ShapeSheet formula is the GUARD function, which prevents an expression from being overwritten by changes that occur on the drawing page. For example, when you click the Fill Color button and apply a new color to a shape, Visio 2007 records your action by changing the formula in the FillForegnd cell. If you want to prevent a shape’s fill color from changing, you can guard the formula in that cell with the GUARD function. Many Visio 2007 shapes are guarded in this way to prevent SmartShapes formulas from being inadvertently changed. The syntax for the GUARD function looks like this:

GUARD(expression)

You most often see the GUARD function used in the Width, Height, PinX, and PinY cells of the Shape Transform section in Visio 2007 shapes. For example, when you drag a shape from the Backgrounds stencil, Visio 2007 sizes the background to fit the drawing page and guards the formulas that control size and position, as Figure 25-5 shows. The width and height of the background shape is set to match the page’s width and height. The PinX, PinY, FlipX, and FlipY cells use the GUARD function to prevent the shape from being repositioned or flipped.