Working with Formulas in Cells | Developing Microsoft Visio Solutions (Pro-Documentation)

3 4

In the Microsoft Visio object model, a Shape object has many cells—each cell contains a formula whose value determines some aspect of an object's appearance or behavior.

Figure 17-1. <b>Cell</b> object and related objects higher in the Visio object model.

Figure 17-1 Cell object and related objects higher in the Visio object model.

Let's say you want to change the width of a Shape object from your program. A Shape object in the Visio object model does not expose a width property. However, the Shape object does contain a Cell object that defines the shape's width. You can modify the shape's width by referring to the shape's width cell and changing its formula. In this way—referring to the appropriate Cell objects—all of the functionality available to you in an object's ShapeSheet window is also available to you through Automation.

To work with formulas of a Shape or Style object, use the Cells or CellSRC property of the object to get a particular Cell object.
To work with formulas of a Page or Master object, use the PageSheet property of the object, which returns a Shape object. You can then use the Cells or CellSRC property of that Shape object to work with its formulas.
To work with the formulas of a Document object, use the DocumentSheet property of the object, which returns a Shape object. You can then use the Cells or CellSRC property of that Shape object to work with its formulas.

Getting a Cell Object

You can get a Cell object from a collection either by name, or by its section, row, and cell indices. After you retrieve a Cell object, you can use its methods and properties to get or set the cell's formula or its value.

Getting a Cell object by name

To get a Cell object, use the Cells property of a Shape object and specify the cell name. You can use any valid cell reference with the Cells property.

For example, to get the PinX cell of a shape:

 Set pinXCellObj = shpObj.Cells("PinX")

To get the y -coordinate of the shape's fourth connection point:

 Set conYCellObj = shpObj.Cells("Connections.Y4")

In these examples, we are getting Cell objects from a collection by name. These cell names are the same names you see in the shape's ShapeSheet window.

Note

The Cells property uses a local name. If you plan to localize your solution, use the CellsU property to specify a universal name. For more details on local and universal names, see Using Universal Names in Your Solution in Chapter 26, Packaging a Visio Automation Solution.

Getting a Cell object by section, row, and cell indices

You can use the CellsSRC property to retrieve any cell by its section, row, and cell indices.

For example, to get the Font cell in the first row of a shape's Character section:

 Set fontCellObj = shpObj.CellsSRC (visSectionCharacter, _       visRowCharacter + 0, visCharacterFont)

If a section contains more than one row and you want to refer to a cell in the second row or beyond, add an integer offset to the row constant for that section. Although you can use a row constant without an offset to get the first row of a section, it's good practice to use the row constant as a base and add an integer offset to it, starting with zero (0) for the first row. For example:

 'First row of the Scratch section visRowScratch + 0 'Second row of the Scratch section visRowScratch + 1 'Third row of the Scratch section visRowScratch + 2

The position of a section or row can change as a result of operations that affect other sections and rows. For example, if a Scratch section contains three rows and you delete the second row, the third row shifts to become the second row. As a result, visRowScratch + 2 is no longer a valid reference because the section no longer has a third row.

You can also use section and row indices to add or delete sections or rows from a shape or to iterate through rows in a section.

Note

In Visio 5.0, the Geometry n.NoFill and Geometry n.NoShow cells appeared in a ShapeSheet window in the third and fourth cells in the Start row of a Geometry section, and were named Geometry n.A1 and Geometry n.B1 (Geometry n.X0 and Geometry n.Y0, respectively, in Visio versions earlier than 5.0). You can refer to these cells by either name.

Getting a user-defined or custom properties cell

Certain shapes might have cells named by the user or the shape developer. User-defined cells are defined in the shape's User-Defined Cells section; custom property cells are defined in the shape's Custom Properties section. Each row in the User-Defined Cells or Custom Properties section has a Value cell that contains the value of the user-defined cell or property, and a Prompt cell that can contain a string. A custom property row has additional cells that control how the custom property can be used.

The Value cell is the default for a user-defined or custom property row, so you can get the Value cell by specifying just the section and name of the row. For example, to get the Value cell of a user-defined cell named Vanishing_Point:

 Set celObj = shpObj.Cells("User.Vanishing_Point")

To get any other cell in a user-defined cell or custom property row, you must include the name of the cell you want. For example, to get the Prompt cell for a custom property named Serial_Number:

 Set celObj = shpObj.Cells("Prop.Serial_Number.Prompt")

For details about defining custom properties in a ShapeSheet window, see Custom Properties in Chapter 7, Enhancing Shape Behavior.

Changing Cell Formulas Using the Formula property

To change a cell's formula, set the Formula property of a Cell object to a string that is a valid formula for that cell. For example, to set the formula of a shape's LocPinX cell to = 2 * Width:

 Set celObj = shpObj.Cells("LocPinX") celObj.Formula = "2 * Width"

If you omit the equals sign from a formula string, the Visio engine automatically adds it to the formula.

If the formula string contains quotation marks—for example, if inches are specified as " rather than inches or in.—use two quotation mark characters ( "" ) to pass one quotation mark to the Visio engine. Or, if you want to assign a string constant—for example, to set the formula of the Prompt cell of a custom property row, use the following:

 shpObj.cells("prop.row_1.prompt").Formula = """Enter property"""

Note

Formulas set using the Formula property are processed using local syntax (localized names, decimal- and list-separators defined by the local regional settings). To provide a solution that works in multiple locales, use the FormulaU property and universal syntax.

For more details on local and universal names, see Using Universal Names in Your Solution in Chapter 26, Packaging a Visio Automation Solution.

Getting the Result of a Formula

Every cell has a formula, and every formula evaluates to a result. You can see this in a ShapeSheet window by clicking Formulas or Values on the View menu. If you're viewing formulas, a cell might display Width * 0.5. If you're viewing values, and if Width is 5.0 in., the same cell would display 2.5 in.

The following properties get the result of a formula:

Result returns the formula's result as a floating point number in the units you specify.
ResultIU returns the result as a floating point number in Visio internal units, inches, radians, or elapsed days.
ResultInt returns the result as an integer in the units you specify.
ResultStr returns the result as a string in the units you specify.
GetResults returns the results of multiple cells in the units you specify.

For example, the formulas that determine local coordinates of a shape's center of rotation are stored in its LocPinX and LocPinY cells. The following statements get the result of the formula in the LocPinX cell:

 Set celObj = shpObj.Cells("LocPinX") localCenterX = celObj.Result("inches")

When getting the results of certain shape formulas, especially those that determine a shape's dimensions or vertices, you'll probably want to preserve the level of precision that floating point numbers provide.

If you plan to use the floating point numbers returned by Result or ResultIU to re-create shapes in other Visio drawings, assign the results to Variant or Double variables to reduce the possibility of rounding errors and maintain the same level of precision.

You can specify units using an integer constant (such as visInches or visMeters) or any string that the Visio engine accepts.

To specify the Visio internal units (inches or radians), specify a zero-length string ( "" ) for the units, or use the ResultIU property instead of Result.

As an alternative to specifying units as a string, use the unit constants (defined by VisUnitCodes) in the Visio type library. For example, you can specify centimeters by using the constant visCentimeters.

 localCenterX = celObj.Result(visCentimeters)

Note

Passing units as integers is more time- and space-efficient than passing units as strings. Passing integers is also preferable because it helps avoid localization issues (for example, "inches" is not localized as "inches" in all languages.)

Use visPageUnits to specify the units defined for the page or visDrawingUnits to specify the units defined for the drawing. ResultStr, like any other result property, takes a units argument, effectively giving you a way to convert between any units. You might use ResultStr specifically to access cell formulas that contain strings, such as the Prompt cell in a custom property row, or to retrieve a string to populate a control in the user interface.

Replacing a Formula with a Result

Occasionally you might want to replace a formula with its result expressed as a constant, either to improve the performance of a shape or because you no longer need to preserve its formulas. The Visio engine evaluates formulas any time you make a change to a shape that affects its formulas. Depending on how often this occurs while your program is executing, it can have a noticeable effect on performance. To replace a formula with its result, use the cell's Result property to set its formula. This is similar to setting the cell's Formula property—it's a shortcut for evaluating the formula and replacing it with the equivalent constant as the cell's new formula.

For example, suppose a shape's LocPinX formula is = 3 in. + 1 ft/2, which evaluates to 9 inches. To replace that formula with its result, use the following statement:

 celLocPinX.Result("inches") = celLocPinX.Result("inches")

After this statement executes, the LocPinX cell's formula is = 9 in.

You can also improve performance by reducing the number of dependencies in your formulas. For details about designing formulas, see Controlling Recalculation of Formulas in Chapter 4, Visio Formulas.

Overriding Guarded Formulas

Microsoft Visio has a guard function that protects a cell's formula from changes. If a cell's formula is protected with guard , attempting to set the formula with the Formula, Result, or ResultIU property causes an error. You can, however, change the cell's formula as follows:

Use ResultForce or ResultIUForce instead of Result or ResultIU.
Use FormulaForce instead of Formula.

Important

Be cautious when overriding guarded formulas. Often a shape developer guards the formulas of a master to protect its smart behavior against inadvertent changes by a user. If you override these formulas, the shape might no longer behave as originally designed.

Using Formulas to Move Shapes: an Example

This sample program moves selected shapes in the active window by setting formulas for the pin of a two-dimensional (2-D) shape, or the begin and end points for a one-dimensional (1-D) shape. The program uses a user form with four buttons that call the Nudge subroutine with the arguments shown.

 Sub Nudge (dx As Double, dy As Double)       'Call Nudge as follows:       'Nudge 0, -1 (Move down one unit)       'Nudge -1, 0 (Move left one unit)       'Nudge 1, 0 (Move right one unit)       'Nudge 0, 1 (Move up one unit)       On Error GoTo lblErr       Dim selObj As Visio.Selection       Dim shpObj As Visio.Shape       Dim unit As Double       Dim i As Integer       'Establish a base unit as one inch        unit = 1        Set selObj = ActiveWindow.Selection       'If the selection is empty, there's nothing to do       'Otherwise, move each object in the selection by the value of       'unit       For i = 1 To selObj.Count             Set shpObj = selObj(i)             Debug.Print "Nudging " ; shpObj.Name; " _                   ("; shpObj.NameID; ")"             If (Not shpObj.OneD) Then                   shpObj.Cells("PinX").ResultIU = (dx * unit) + _                         shpObj.Cells("PinX").ResultIU                   shpObj.Cells("PinY").ResultIU = (dy * unit) + _                         shpObj.Cells("PinY").ResultIU             Else                   shpObj.Cells("BeginX").ResultIU = (dx * unit) + _                          shpObj.Cells("BeginX").ResultIU                   shpObj.Cells("BeginY").ResultIU = (dy * unit) + _                          shpObj.Cells("BeginY").ResultIU                   shpObj.Cells("EndX").ResultIU = (dx * unit) + _                         shpObj.Cells("EndX").ResultIU                   shpObj.Cells("EndY").ResultIU = (dy * unit) + _                         shpObj.Cells("EndY").ResultIU             EndIf       Next i       Exit Sub lblErr: End Sub