4. Visio Formulas
The ShapeSheet Window
A Microsoft Visio object is stored internally as a set of formulas. For example, when you view a shape in a drawing window, you see it rendered graphically and see it behave according to its formulas. When you view the same shape in a ShapeSheet window, you see the underlying formulas that determine how the shape looks and behaves on the drawing page. These two windows simply provide different views of the same shape.
In a drawing window, some of the changes you make to an object affect its formulas. For example, when you move a shape with the Pointer tool, Visio changes and then reevaluates the formulas that define the shape's center of rotation, or pin, on the drawing page, because those formulas determine the shape's location on the page. However, a ShapeSheet window gives you more precise control over the appearance and behavior of the object, because you can edit the object's formulas to change its behavior. Whether you change an object in a drawing window or a ShapeSheet window, the modifications are automatically saved when you save the Visio document that contains the object.
Dispalying a ShapeSheet Window
Most Visio objects-shapes, groups, masters, guides and guide points, pages, documents, styles, and linked or embedded objects from other applications-have underlying formulas that you can edit to change the object's behavior. To edit an object's formulas, you must first display a ShapeSheet window for the object.
|Figure 4-1 ( A ) When a ShapeSheet window is active, the menu bar contains commands for working with an object's formulas. ( B ) You can edit the formula in the selected cell or in the formula bar. ( C ) Each ShapeSheet section represents a set of related formulas.|
To display a ShapeSheet window for an object on a
To add the Show ShapeSheet command to shapes' shortcut (right-click) menus, click Options on the Tools menu, click the Advanced tab, and select Run in developer mode . This option also adds the Add-ons submenu to the Tools menu.
Drawing pages, styles, Visio documents, and masters in stencils also have formulas that you can edit.
To display a ShapeSheet window for a page,
master, style, or document
|Figure 4-2 The Drawing Explorer window.|
You can also display a ShapeSheet window for a page by clicking Show ShapeSheet on the Window menu with nothing selected on the page. Or, click the Show ShapeSheet buttonon the Developer toolbar.
To display a ShapeSheet window for a master in a
Dispalying Sections in a ShapeSheet Window
A ShapeSheet window is divided into sections of labeled cells that contain formulas that define related aspects of object behavior and appearance. Initially, Visio does not display all possible sections in a ShapeSheet window. Some sections are hidden simply to save space on the screen; others are present for an object only if they are needed and make sense for that type of object.
For example, to create a command that appears on a shape's shortcut menu, you must add an Actions section to the shape, either by clicking the Section command on the Insert menu while a ShapeSheet window is open and active, or through Automation. (For details about adding sections through Automation, see Chapter 17 , Automating Formulas.)
To show or hide sections in a ShapeSheet window
To add sections using a ShapeSheet window
You can expand or collapse a section in a ShapeSheet window by clicking the section name.
ShapeSheet Sections and What They Control
Each ShapeSheet section controls some aspect of a Visio object. As a solution developer, you need to know which section or sections control the behavior you want to modify.
This topic lists all possible ShapeSheet sections with a brief description of what the section does. For details about specific cells in a ShapeSheet section, search the ShapeSheet Reference in the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference ).
Many of the sections you see in the ShapeSheet window interface are actually referred to as rows when you manipulate them through Automation. For a list of the sections, rows, and cells that appear in the ShapeSheet window, and for the corresponding index constants that you can use in a program to access sections, rows, and cells with Automation, see Appendix B , ShapeSheet Section, Row, and Cell Indices in this guide.
|1-D Endpoints||The x- and y-coordinates of the begin point and end point of a one-dimensional (1-D) shape.|
|Actions||Custom command names that appear on an object's shortcut menu and the actions that the commands perform.|
|Alignment||Alignment of an object with respect to the guides or guide points that the object may be glued to.|
|Character||Formatting attributes for an object's text, including font, color, transparency, text style, case, position relative to the baseline, and point size.|
|Connection Points||Connection points of an object.|
|Controls||x- and y-coordinates and behavior of an object's control handles.|
|Custom Properties||User-defined data associated with the object.|
|Document Properties||Document attributes, such as preview settings and output format.|
Formulas that evaluate when certain events occur, such as
double-clicking a shape.
ShapeSheet events are unique to the ShapeSheet; they are not the same as Automation events.
|Fill Format||Fill formatting attributes for an object and its drop shadow, including pattern, foreground color and transparency, and background color and transparency.|
|Foreign Image Info||Width, height, and offset within its borders of an object from another application in a Visio drawing.|
|Geometry||Coordinates of the vertices for the lines and arcs that make up an object's geometry. If the object has more than one path, it has a Geometry section for each path.|
|Glue Info||Formulas that influence how a 1-D shape glues to other objects.|
|Group Properties||Behavior, selection, and display attributes for groups, including selection mode, display mode, and text, snap, and drop behavior.|
|Hyperlinks||Links between an object and a destination, such as another drawing page, another file, or a Web site.|
|Image Properties||Bitmap attributes, such as image intensity (gamma), brightness, contrast, and transparency.|
|Layer Membership||Layers to which the object is assigned.|
|Layers||Layers of an object and the properties of each layer.|
|Line Format||Line formatting attributes, including pattern, weight, color, and transparency; whether the line ends are adorned (for example, with an arrowhead); the size of the adornments; the radius of the rounding circle applied to the line; and line cap style (round or square).|
|Miscellaneous||Properties that control various attributes, such as how the object looks when it is selected or dragged.|
|Page Layout||Page attributes that control automatic layout of shapes and routing of dynamic connectors, including default appearance and behavior of dynamic connectors and shapes.|
|Page Properties||Attributes such as drawing scale, page size, and offset of drop shadows.|
|Paragraph||Paragraph formatting attributes, including indents, line spacing, and horizontal alignment of paragraphs.|
|Protection||Status of locks set with the Protection command plus additional locks that can be set only in the ShapeSheet window.|
|Ruler & Grid||Settings of a page's rulers and grid, including density, origin, and spacing.|
|Scratch||A work area for intermediate formulas that are referred to by other cells.|
|Shape Layout||Placement and routing attributes, such as whether a connector can cross a shape or the style a connector should use when it jumps over another connector.|
|Shape Transform||General positioning information, such as width, height, angle, and center of rotation (pin); whether the object has been flipped; and how the object should behave when resized within a group.|
|Style Properties||Style attributes such as whether the style defines text, line, and fill formatting.|
|Tabs||Tab stop position and alignment.|
|Text Block Format||Alignment and margins of text in a text block. Alsobackground color and transparency of the text block.|
|Text Fields||Custom formulas inserted in text using the Field command on the Insert menu.|
|Text Transform||Positioning information about a shape's text block.|
|User-Defined Cells||Named cells for entering formulas and constants that are referred to by other cells and add-on tools. Unlike Scratch cells, user-defined cells are portable-for example, if a shape that refers to a user-defined cell in the page sheet is copied to another page that does not have the same user- defined cell, the cell is added to the page. If the page already has such a user-defined cell, the shape simply refers to that cell for its value.|
Examining a Shape in a ShapeSheet Window
A good way to learn about Visio formulas and the ShapeSheet window is to view a shape with a drawing window and a ShapeSheet window side by side. This is a useful technique for taking apart existing masters so you can see how their behavior is controlled by custom formulas. It's also helpful to try changing the default formulas for a master to see the effect on the shape in the drawing window.
To examine a shape in a ShapeSheet window
|Figure 4-3 Examining a shape in a ShapeSheet window. ( A ) Selecting certairows or cells in a ShapeSheet window highlights the corresponding vertex in a drawing window.|
To try this yourself, start by drawing a simple shape with straight line segments, such as a rectangle or other polygon, and display the ShapeSheet window as described in the previous procedure. In the ShapeSheet window, try any of the following suggestions and notice the effect on the shape in the drawing window.
If a ShapeSheet window displays values rather than formulas in cells, click Formulas on the View menu to display formulas instead.
To accept any change you make to a cell value, press the ENTER key.
You can display a section that is not visible, or you can hide a section you're not interested in. For details, see Displaying Sections in a ShapeSheet Window earlier in this section.
As you modify the shape, you might notice that some formulas are displayed in black text and others in blue. This indicates whether the formula is inherited or local, respectively. For details, see How Shapes Inherit Formulas later in this chapter.
In the drawing window, you can change the shape using the Visio drawing tools and commands to see the effect on the shape's formulas. Try any of the following suggestions:
For a brief discussion of the Visio drawing tools, seeChapter 2 , Creating Visio Shapes. For details about the Shape Transform and Geometry sections, see Chapter 5 , Controlling Shape Geometry with Formulas.
For reference information about any ShapeSheet section or cell and for information on how to work in the ShapeSheet, see the ShapeSheet Reference in the Microsoft Visio Developer Reference (on the Help menu, click Developer Reference ).
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.
Much of what you do to shapes with Automation is done by getting and setting their formulas. For details, seeChapter 17 , Automating Formulas.
Entering and Editing Formulas in a ShapeSheet
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.
|Figure 4-4 Entering a formula in a ShapeSheet window. ( A ) Select a cell, and then type or edit the formula and click the Accept button on the formula bar or press ENTER. ( B ) 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 ).
Right-click a ShapeSheet cell to display its shortcut menu, which contains commands you can use to edit the cell.
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.
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:
FUNCTION(argument1, argument2, ... argumentN)
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 ).
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.
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:
4 + 5 * 6
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:
( 4 + 5 ) * 6
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 ).
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.
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:
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:
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:
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:
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||CellName||Width object|
|In a Geometry section||Geometryn.ColumnnameRowIndex||Geometry1.X1|
|In a named column with indexed rows||Sectionname.Columnname[RowIndex]||Char.Font|
|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 Cellreference LineColor||Styles!Stylename!||Styles!Connector!|
|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|
Any multibyte character
Any alphabetic character
Any of the following: ? ~ @ # _ (underscore)
|Other than first||
Any multibyte character
Any of the following: ? ~ @ # _ (underscore)
$ . (period)
Specifying nonstandard sheet names in cell
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|
Beginning with Microsoft Visio version 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.
Units of Measure in Visio Formulas
Microsoft Visio assigns the result of a formula differently depending on the cell in which you enter it:
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:
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 ).
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:
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.
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.
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|
|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.
You can identify units using a variety of strings (such as i, in, in., inch, inches) and you can use the FORMAT function to display results using popular formatting options.
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 ).
Designing Visio Formulas
Designing good Microsoft Visio formulas requires more than correct syntax. A shape developer needs to understand where a shape obtains its default formulas, the advantages and disadvantages of storing formulas in certain cells, how to protect custom formulas against inadvertent changes, and how to control formula recalculation for best performance.
How Shapes Inherit Formulas
When you open a ShapeSheet window, a formula you see in a cell might be inherited from a master or a style. Rather than make a local copy of every formula for a shape, an instance of a master inherits formulas from the master and from the styles applied to it. This behavior has two benefits: It allows changes to the master's formulas or the style definition to be propagated to all instances, and it results in smaller Visio files because inherited formulas are stored once in the master, not once in each instance.
When you enter a formula in such a cell, you override the inherited formula with a local formula. The cell containing the local formula no longer inherits its formula from the master, changes to the master's formula are not propagated to that cell in the instance, and the shape occupies more storage in the document. (Style application is an exception-unless you choose to preserve local overrides when you apply a style, it always writes new formulas into the corresponding ShapeSheet cells. For details, seeChapter 10 , Managing Styles, Formats, and Colors.)
You can tell whether a formula is local or inherited by the color of its text in the ShapeSheet. Black text in a cell indicates an inherited formula. Blue text indicates a local formula-either the result of editing the formula in a ShapeSheet window or some change to the shape (for example, resizing it in the drawing window) that caused the formula to change.
To restore an inherited formula to a cell, delete the local formula. Visio replaces it with the corresponding formula >from the master.
In earlier versions of Visio, geometry formulas were always local. In Visio version 2000 and later, geometry formulas are inherited from masters. This means that any local change to a shape's geometry creates a copy of the inherited formula and causes the shape to occupy more storage. To keep Visio documents small in size, change inherited formulas in the master in the document stencil so that shapes can continue to inherit from the master. Solutions that use Automation to change geometry formulas in shapes should be redesigned to do the same.
User-Defined Cells and "Scratch" Formulas
Most ShapeSheet sections have a predefined purpose: Their cells control particular shape attributes or behaviors. However, you might need to simplify a formula with intermediate calculations, or store values to be used by other formulas or add-ons. You can store such formulas and values in an object's User-Defined Cells section or its Scratch section. To add these sections in a ShapeSheet window, click Section on the Insert menu, and then select the section you want to add.
The cells in User-Defined Cells and Scratch sections do not control specific shape attributes or behaviors, so you can use either or both to contain any formula. However, there are times when it makes more sense to use one or the other:
If a shape's cells will be accessed using Automation, place formulas in user-defined cells rather than Scratch cells. Any program can write to a Scratch cell and so overwrite formulas you place there. This is less likely to happen in a cell with a unique name.
You can add a cell whose value and name you specify in the User-Defined Cells section. A user-defined cell can contain any formula, such as a constant referenced in other formulas or a calculation used by an add-on. For example, a master might refer to a user-defined cell in a page sheet. When an instance of the master is created, the instance refers to the user-defined cell of the page it is on-if the page already has that user-defined cell. If the page does not already have that cell, it is copied from the master. (The same is true for user-defined cells in document sheets.) This feature makes user-defined cells extremely portable, because the shape developer doesn't have to ensure that all possible destinations have that user-defined cell-if the shape needs the cell, it will be there.
The name you give to a user-defined cell must be unique within a section. To refer to the value of a user-defined cell in the same shape, use the syntax User.name. For example, User.Constant.
To refer to a user-defined cell in another shape, a page, or the current document, precede User.name with the appropriate scope. For example:
Sheet.2!User.Constant ThePage!User.Constant TheDoc!User.Constant
The User.Prompt cell, Action.Menu cell, Controls.Tip cell, and certain other cells are designated by default to contain strings. When you type in these cells in a ShapeSheet window, Visio automatically encloses the text in quotation marks. Begin the formula with an equals sign (=) to make the Visio application evaluate it as a formula. Visio does not automatically enclose text in quotation marks if you set the formulas of one of these cells using Automation.
The Scratch section has six columns labeled X, Y, and A through D. The X and Y cells use the drawing's units of measure, so place calculations involving dimensions or shape coordinates in those cells. The A through D cells have no intrinsic units and are appropriate to use for any result type. To refer to cells in the Scratch section, specify the section name and the column and row label; for example, Scratch.A1.
Scratch cells are best suited for what their name implies-intermediate calculations that are local to a shape and not involved with Automation. Besides the fact that Scratch cells can't have meaningful names like user-defined cells, they also aren't as portable; if a shape refers to a Scratch cell in a page or document and you copy that shape to another page or document, the referring formula will fail with a #REF error because the Scratch formula from the source page or document is not copied to the destination.
The only way to protect the formulas in individual ShapeSheet cells from change is to use the GUARD function. GUARD protects the entire formula in a cell; it cannot protect parts of formulas. Actions in the drawing window cannot overwrite formulas protected by the GUARD function. The GUARD function uses this syntax:
where expression is the formula to protect. A formula protected with the GUARD function evaluates to exactly the same result as a formula not protected with GUARD.
When a shape is moved, resized, grouped, or ungrouped, Visio writes changes to ShapeSheet cells and can overwrite custom formulas. The cells most commonly affected by such actions are Width, Height, PinX, and PinY in the Shape Transform section. For example, to prevent a shape from being flipped, you can enter the formula:
FlipX = GUARD(FALSE) FlipY = GUARD(FALSE)
A single action in the drawing window can affect several ShapeSheet cells. You must guard the formulas in each of these cells if you want to prevent unexpected changes to the shape. Of course, if a user deletes a ShapeSheet section, all the formulas in it, including guarded ones, will be deleted.
The GUARD function prevents certain user actions via the drawing window; however, Visio does support Automation methods that you can use to overwrite guarded formulas programmatically.
Controlling Recalculation of Formulas
By default, a formula that refers to another cell is recalculated when the referenced cell changes. For example, if a formula refers to a shape's PinX cell and that shape is moved on the page, the formula is recalculated because PinX has changed. Most of the time this behavior is exactly what you want, and Visio formulas depend on it for much of their power and versatility.
Some recalculations might seem to result from simple cause and effect, but many factors influence the order in which formulas are recalculated. Formulas should be designed so that they do not depend on a particular order of recalculation.
However, not all recalculations are necessary. For example, the SETF function is a powerful function that can be used in a formula to set the formula of another cell. The SETF function only needs to be recalculated when the condition that triggers it occurs, but if the formula refers to cells that often change, it might frequently be recalculated unnecessarily. Recalculation takes time and affects shape performance.
To prevent unnecessary recalculations and improve the performance of your solution, enclose cell references in one of the following functions:
A big advantage of GETREF is that the target cell reference does not have to be enclosed in quotation marks. In earlier versions of Visio, a target cell reference used in a SETF function had to be enclosed in quotation marks, which required the formula to be translated for localized solutions.
Both GETREF and GETVAL allow a formula to track a cell reference if the reference changes-for example, if preceding rows are deleted or if the cell itself is deleted-but the referring formula is not recalculated when the referenced cell's value changes.
For example, the following formula is recalculated when Width changes, but not when PinX and PinY change:
= GETVAL(PinX) + GETVAL(PinY) + Width
The following formula is recalculated when the cell containing the SETF formula is triggered (for example, by a user choosing an action from a shortcut menu), but not when PinX changes:
= SETF(GETREF(PinX), 7)
When to Supplement Visio Formulas with Automation
One of the more important questions in developing a Microsoft Visio solution is this: When should you supplement formulas with Automation? Visio formulas can be extremely powerful, but the more complex formulas become, the more difficult they can be to design and test.
You might need to add Automation to your solution if your formulas:
For a discussion of how to divide functionality between Visio formulas and Automation, seeChapter 1 , Introduction to Developing Microsoft Visio Solutions. For an introduction to Automation in the Visio product, see Chapter 14 , Automation and the Visio Object Model.