Elements of Visio Formulas

The key to controlling shape actions is to write formulas that define the behavior you want. A formula is an expression that can contain constants, functions, operators, and cell references. Microsoft® Visio® evaluates a formula to a result and then converts the result to the appropriate units for the cell that contains the formula. (Some formulas consist of a single constant, but all formulas go through this evaluation and conversion process.) In a ShapeSheet® window, you can display cell contents as either values or formulas by clicking the appropriate command on the View menu.

Note Much of what you do to shapes with Automation is done by getting and setting their formulas. For details, see Chapter 17, Automating Formulas.

In this section…

Entering and Editing Formulas in a ShapeSheet Window

Functions and Operators in Visio Formulas

ShapeSheet Cell References

Rules for Cell References in Visio Formulas

Units of Measure in Visio Formulas

Multidimensional Units

Specifying Units of Measure

Entering and Editing Formulas in a ShapeSheet Window

You can edit a cell's formula to change the value calculated for the cell and, as a result, change a particular behavior of a shape. For example, the Height cell in the Shape Transform section contains a formula that you can edit to change the shape's height. You enter and edit formulas in a ShapeSheet window much the same way you work in any spreadsheet program.

Visio regards anything in a cell—even a numeric constant, string, or cell reference—as a formula. Unlike a spreadsheet program, however, many Visio cells require a result of a specific type, such as a dimension, so anything you enter in them must imply a unit of measure, such as inches or centimeters.

The Visio engine automatically converts a formula's natural result into an equivalent result of the type required by the cell that contains the formula. For example, the FlipX cell in the Shape Transform section requires a Boolean result (TRUE or FALSE); in the FlipX cell, therefore, any formula that evaluates to a non-zero number is converted to TRUE, and any formula that evaluates to zero is converted to FALSE. For details, see Units of Measure in Visio Formulas later in this section.

To enter a formula, select a cell and then start typing either in the cell or in the formula bar, as the following figure shows.

Entering a formula in a ShapeSheet window

Entering a formula in a ShapeSheet window

  1. Select a cell, and then type or edit the formula and click the Accept button on the formula bar or press ENTER.
  1. Click the Cancel Change button on the formula bar to cancel changes to a formula.

For details about entering and editing formulas or working in the formula bar, search the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference).

Tip Right-click a ShapeSheet cell to display its shortcut menu, which contains commands you can use to edit the cell.

TOP

Functions and Operators in Visio Formulas

If you've created formulas in a spreadsheet program, you've probably used functions and operators much like those you'll use in Visio formulas. This topic provides a brief overview of functions and operators and how they are used.

Functions

A function performs a single, well-defined task. Most functions take a fixed number of arguments as input, although some functions take none, some take a variable number of arguments, and some allow optional arguments. Although the type and number of arguments depend on the function, all functions have the same general syntax:

Many functions that you can use in Visio formulas resemble those you've probably seen in spreadsheet programs: mathematical, such as SUM or SQRT; trigonometric, such as SIN or COS; or logical, such as IF or NOT. Many other functions are unique to Visio, such as GUARD, GRAVITY, or RUNADDON.

For details about functions, see the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference).

Note Certain functions appear in formulas generated by Visio, but are not listed in the Insert Function dialog box or described in the Microsoft Visio Developer Reference. These functions begin with a single underscore (for example, _ELLIPSE_THETA). They are reserved for internal use and should not be used in other formulas.

Operators

An operator performs an operation, usually by combining two operands to produce a result.

Many operators can be classed as arithmetic (addition, subtraction, multiplication, and so on) or logical (greater than, less than, or equal to). Other operators manipulate strings or perform actions such as running add-ons.

An operand can be a constant (a single value) or an expression (perhaps containing one or more functions) that evaluates to a single value. In a Visio formula (as in any spreadsheet program), an operand can also be a cell reference.

When a formula contains more than one operator, operators are evaluated in a certain order (sometimes called their precedence). For example, the multiplication operator ( * ) is evaluated before the addition operator ( + ). Consider the following expression:

Because multiplication has a higher precedence than addition, first 5 * 6 is multiplied to obtain 30, and then 4 is added to 30 to obtain a result of 34.

You can alter the order of evaluation by grouping expressions in parentheses. For example:

Because 4 + 5 is enclosed in parentheses, it is evaluated first and becomes 9. Then 9 * 6 is multiplied to obtain a result of 54.

If expressions in parentheses are nested, Visio starts with the expression in the innermost set of parentheses and works its way outward.

For a table of operators and their precedence in Visio formulas, search the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference).

TOP

ShapeSheet Cell References

You can create interdependencies among Visio formulas by means of cell references. Cell references give you the power to calculate a value for one cell based on another cell's value. For example, a shape's Width cell might contain a formula that calculates the shape's width by referring to the value of its Height cell, so that when a user stretches the shape vertically its width stays in proportion.

A given formula can refer to any cell in a document, although it's most common with formulas for shapes to refer to cells of objects on the same page or particular cells in the same object.

Note If you're planning to localize your solution for international markets, you might want to use universal names in formulas. In Visio, any object that can be assigned a name (for example, shapes or rows in a User-Defined Cells section) can actually have two names: a local name and a universal name. The local name is displayed to the user and must be translated if the solution is localized. The universal name is (for the most part) concealed from the user, does not need to be translated, and can be assigned only with Automation.

You can enter universal names in formulas in a ShapeSheet window or set them with Automation, but once the formula is entered, it is displayed with local names in a ShapeSheet window. For details about using universal names in a solution, see Chapter 26, Packaging a Visio Automation Solution and the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference).

References to cells in the same shape

A reference to a cell in the same shape needs to specify only the cell name, such as Height. If the cell is in a section with indexed rows, the section and row index are part of the cell name. For example, the following reference specifies the cell in column X, row 5, of the Connection Points section:

Tip You can quickly reference a cell in a formula by placing the insertion point in the formula bar or a cell, and then clicking the cell you want to reference. The name of the cell is inserted at the insertion point.

References to cells in other shapes or containers

A reference from one shape to a cell in another shape, page, master, or style requires a prefix that identifies the container of that cell. For example, a reference to a cell in another shape of the same page or master must include the shape's name or ID followed by an exclamation point and then the name of the cell, as shown in the following reference:

This reference specifies the Width cell in the Shape Transform section of the shape named Armchair.

If the shape is not named, or as an alternative to its name, a reference can include the shape's ID. For example, the following reference specifies the Width cell in the Shape Transform section of the shape whose ID is 2. This is recommended, because names are scoped to their containers (for example, two groups can each contain a shape that has the same name), but Sheet.ID is unique within that page, master, or style collection. For example:

Tip An object on a drawing page always has an ID, whether or not it also has a descriptive name. Visio assigns the ID when the object is created. This ID does not change unless you move the object to a different page or document. To display an object's ID or give it a descriptive name, select the object, and then click Special on the Format menu.

A reference from the cell of a shape on the page to a cell that defines a property of that page requires the name ThePage followed by an exclamation point and the cell name. (In a master, a reference to ThePage refers to the object that defines properties of the master as a whole, such as its overall size and its drawing scale.) For example, the following reference specifies the PageScale cell of the page:

TOP

Rules for Cell References in Formulas

The syntax you use and whether you can refer to a shape by name depend on the relationship between the two objects. The following general rules apply:

  • If a shape is a peer of the shape whose formula you are editing, you can refer to the peer shape by name. If the peer shape is a group, you can refer by name to the group, but not to its members. Neither can you refer by name to a shape's parent or its parent's peers.
  • You can use Sheet.ID syntax to refer to any shape on the page, whether the shape is in a group or is a parent of a shape.
  • If a shape contains a group shape, you must use Sheet.ID syntax to refer to it.
  • Names that contain nonstandard characters must be enclosed in single quotation marks. Single quotation mark characters in a nonstandard name must be prefixed by a single quotation mark.

The following tables summarize rules for cell references in formulas, and the standard characters allowed in sheet names.

Summary of cell reference syntax

Cell

Cell reference syntax

Example

In the same object

CellName

Width

In a Geometry section

Geometryn.ColumnnameRowIndex

Geometry1.X1

In a named column with indexed rows

Sectionname.Columnname[RowIndex]

Char.Font[3]

In an unnamed column with indexed rows

Sectionname.ColumnnameRowIndex

Scratch.A5

In another named object in the same container

Shapename!Cellname

Star!Angle

In another object with the same name in the same container

Shapename.ID!Cellname

Executive.2!Height

In any object on the page

Sheet.ID!Cellname

Sheet.8!FillForegnd

In the page sheet

ThePage!Cellreference

ThePage!PageWidth

In a containing group

Sheet.ID!Cellname

MyParentGroup!PinX

In a named row

Sectionname.Rowname[.Cellname]

User.Vanishing_Point.Prompt

A cell in the page sheet of another page in the document

Pages[Pagename]!Shapename!Cellreference

Pages[Page-3]!ThePage!DrawingScale

In a style sheet

Styles!Stylename!Cellreference

Styles!Connector!LineColor

In a master sheet

Masters[Mastername]! Shapename!Cellreference

Masters[Door]!Sheet.5!Width

In the document sheet of the current document

TheDoc!Cellreference

TheDoc!OutputFormat

A shape, page, master, document, or style with a nonstandard name

'Sheetname'!CellName

'1-D'!LineColor

Standard characters allowed in sheet names

Character position

Allowed characters

First

Any multibyte character
Any alphabetic character
Any of the following: ? ~ @ # _ (underscore)

Other than first

Any multibyte character
Any alphanumeric-character
Any of the following: ? ~ @ # _ (underscore) $ . (period)
Space

Specifying nonstandard sheet names in cell references

If you name a shape, page, master, document, or style using nonstandard characters (such as a dash), the Visio engine automatically delimits that name using single quotation marks ('). The Visio engine also prefixes any single quotation mark character in a nonstandard name with an additional single quotation mark. This adjustment allows Visio to correctly interpret the sheet name.

Keep this behavior in mind when you name various objects. If you want to use nonstandard characters in names, be sure that any references to nonstandard names include the required quotation marks. For example, if you name a shape 1-D, you would refer to it in a formula using single quotation marks, such as '1-D'!LineColor.

Examples of how Visio handles nonstandard sheet names in formulas

Object

Name

Original formula

Adjusted formula

Shape

1-D

=1-D!LineColor

='1-D'!LineColor

Shape

One's

=One's!LineColor

='One's'!LineColor

Style

Red!Bang

=Styles!Red!Bang!LineColor

=Styles!'Red!Bang'!LineColor

Note Beginning with Microsoft Visio 2002, the parser only makes this adjustment for nonstandard sheet names within the context of a formula. Nonstandard sheet names are not fully supported in previous versions of Visio.

TOP

Units of Measure in Visio Formulas

Microsoft Visio assigns the result of a formula differently depending on the cell in which you enter it:

  • In general, cells that represent shape position, a dimension, or an angle require a number-unit pair that consists of a number and the qualifying units needed to interpret the number. For example, a formula in the Width cell might evaluate to 5, which might mean 5 inches or 5 centimeters, depending on the units of measure in effect for the drawing.
  • Other cells have no intrinsic units of measure and evaluate to a string, to TRUE or FALSE, or to an index, depending on the nature of the cell. For example, the formula =5 in the FillForegnd cell means color 5 from the drawing's color palette, whereas =5 in the LockWidth cell means true (only zero is false) and locks the shape's width.

For best results, always specify the units of measure in your formulas, rather than relying on Visio to supply the correct units. If you don't specify units of measure with a number, it is evaluated using the internal units defined for the cell, which can be page units, drawing units, type units, duration units, or angular units:

  • Page units measure sizes on the printed page, including typographic measurements. Page units are typically used for line thicknesses and font sizes that do not scale with the drawing.
  • Drawing units specify the real-world measurement, such as a 50-meter pool (drawing units) that appears 10 cm long (page units) on paper. For example, if you enter the formula =50 into the Width cell, which expects a number-unit pair in drawing units, Visio supplies the default drawing units currently set for the page and evaluates the formula accordingly.
  • Angular units measure angular distances, such as a shape's rotation. Angles can be expressed in degrees or radians.

Internally, Visio uses inches for measuring distance, radians for measuring angles, and days for measuring durations.

For more details on units of measure, search the ShapeSheet Reference in the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference).

TOP

Multidimensional Units

A Visio formula that multiplies or divides dimensional units produces a result in multidimensional units that can be stored in some cells. For example, if a shape is 5 feet wide and 10 feet high in drawing units, the formula = Width * Height evaluates to 50 ft ^ 2 (50 square feet). The following cells can store multidimensional results:

  • The Value cell in a row of a Custom Property or User-Defined Cells section in the ShapeSheet.
  • The A, B, C, and D cells in a row in the Scratch section in the ShapeSheet.

Use the FORMAT function to display multidimensional units using abbreviations such as sq. in. For details, see the FORMAT function in the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference).

Be aware that most multiplication is intended to combine a value that has units with a value that has none. If such a calculation happens to multiply two values with units, the multidimensional result might not make sense. For example, if a color cell such as FillForegnd is set to the product of two cells in the Geometry section, the result would be a #DIM error because cells in the Geometry section always have units but the FillForegnd cell cannot contain a multidimensional value.

Note In versions of Visio earlier than Visio 2000, formulas that multiplied or divided dimensional values could generate incorrect results. For example, the formula =1 cm. * 1 cm. was converted to 0.394 in. * 0.394 in. Multiplying just the constants and not the units, this formula evaluated to 0.155 in. Converting this result back to centimeters by multiplying it by 2.54 cm./in. produced an incorrect result of 0.394 cm. instead of the correct result of 1 cm.^2 (centimeters squared). Existing solutions that employ workarounds for this behavior should be changed to take advantage of multidimensional units in Visio versions 2000 and later.

TOP

Specifying Units of Measure

Because many drawings represent physical objects, you can specify units of measure in the imperial and metric systems, and you can specify angles in radians, decimal degrees, or degrees, minutes, and seconds of arc. You can also use standard typographical measurements such as picas, points, ciceros, and didots.

For best results, always specify a unit of measure when you enter a formula in a cell that expects a dimensional value, as shown by the examples in the following table.

Examples of number-unit pairs

Use

Don't use

5 in.

5

Width + 0.5 in.

Width + 0.5

7 in. * 1.5

7 * 1.5

DEG(MODULUS(Angle, 360 deg.))

MODULUS(Angle, 360 deg.)

Specifying units explicitly makes it easier to identify the number-unit pairs in your calculations, so that you don't inadvertently divide one number-unit pair with another number-unit pair or combine incompatible units, such as adding angles to lengths. In addition, specifying units of measure makes it easier to localize your formulas for international use.

Note If you're planning to localize your solution for international markets, you might want to use universal names in formulas. The local name is displayed to the user and must be translated if the solution is localized. The universal name is (for the most part) concealed from the user, does not need to be translated, and can be assigned only with Automation.

For more details about units of measure and a list of the units supported in Visio, search the ShapeSheet Reference in the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference).