Designing Visio Formulas

3 4

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, see Chapter 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.

Note


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:

  • You can provide a meaningful name for a user-defined cell, so it's a better place to store constants and values referred to in other formulas because references to a meaningful cell name make formulas easier to read.
  • The Scratch section has X and Y cells, which are designated to contain a number-unit pair in drawing units. These cells are good places to put formulas involving shape coordinates.

Note


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.

User-defined cells

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 

Tip


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.

Scratch cells

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.

Protecting Formulas

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:

 GUARD(expression) 

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.

Note


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:

  • Use GETREF(cell reference) to include a reference to another cell in a formula but not recalculate the formula if the value of that cell changes.
  • Use GETVAL(cell reference) to use the value of another cell in a formula.

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) 


Developing Microsoft Visio Solutions 2001
Developing Microsoft Visio Solutions (Pro-Documentation)
ISBN: 0735613532
EAN: 2147483647
Year: 2004
Pages: 180

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net