In this article by David J. Parker, author of Microsoft Visio 2010 Business Process Diagramming and Validation, we will discuss Microsoft Visio ShapeSheet™ and the key sections, rows, and cells, along with the functions available for writing ShapeSheet™ formulae, where relevant for structured diagrams.

Microsoft Visio is a unique data diagramming system, and most of that uniqueness is due to the power of the ShapeSheet, which is a window on the Visio object model. It is the ShapeSheet that enables you to encapsulate complex behavior into apparently simple shapes by adding formulae to the cells using functions. The ShapeSheet was modeled on a spreadsheet, and formulae are entered in a similar manner to cells in an Excel worksheet.

Validation rules are written as quasi-ShapeSheet formulae so you will need to understand how they are written. Validation rules can check the contents of ShapeSheet cells, in addition to verifying the structure of a diagram. Therefore, in this article you will learn about the structure of the ShapeSheet and how to write formulae.

Where is the ShapeSheet?

There is a ShapeSheet behind every single Document, Page, and Shape, and the easiest way to access the ShapeSheet window is to run Visio in Developer mode. This mode adds the Developer tab to the Fluent UI, which has a Show ShapeSheet button. The drop-down list on the button allows you to choose which ShapeSheet window to open.

Alternatively, you can use the right-mouse menu of a shape or page, or on the relevant level within the Drawing Explorer window as shown in the following screenshot:

The ShapeSheet window, opened by the Show ShapeSheet menu option, displays the requested sections, rows, and cells of the item selected when the window was opened. It does not automatically change to display the contents of any subsequently selected shape in the Visio drawing page—you must open the ShapeSheet window again to do that. The ShapeSheet Tools tab, which is displayed when the ShapeSheet window is active, has a Sections button on the View group to allow you to vary the requested sections on display.

You can also open the View Sections dialog from the right-mouse menu within the ShapeSheet window.

You cannot alter the display order of sections in the ShapeSheet window, but you can expand/collapse them by clicking the section header.

The syntax for referencing the shape, page, and document objects in ShapeSheet formula is listed in the following table.

Object	ShapeSheet formula	Comment
Shape	Sheet.n!	Where n is the ID of the shape Can be omitted when referring to cells in the same shape.
Page.PageSheet	ThePage!	Used in the ShapeSheet formula of shapes within the page.
	Pages[page name]!	Used in the ShapeSheet formula of shapes in other pages.
Document.DocumentSheet	TheDoc!	Used in the ShapeSheet formula in pages or shapes of the document.

What are sections, rows, and cells?

There are a finite number of sections in a ShapeSheet, and some sections are mandatory for the type of element they are, whilst others are optional. For example, the Shape Transform section, which specifies the shape’s size (that is, angle and position) exists for all types of shapes. However, the 1-D Endpoints section, which specifies the co-ordinates of either end of the line, is only relevant, and thus displayed for OneD shapes. Neither of these sections is optional, because they are required for the specific type of shape. Sections like User-defined Cells and Shape Data are optional and they may be added to the ShapeSheet if they do not exist already. If you press the Insert button on the ShapeSheet Tools tab, under the Sections group, then you can see a list of the sections that you may insert into the selected ShapeSheet.

In the above example, User-defined Cells option is grayed out because this optional section already exists.

It is possible for a shape to have multiple Geometry, Ellipse, or Infinite line sections. In fact, a shape can have a total of 139 of them.

Reading a cell’s properties

If you select a cell in the ShapeSheet, then you will see the formula in the formula edit bar immediately below the ribbon.

Move the mouse over the image to enlarge it.

You can view the ShapeSheet Formulas (and I thought the plural was formulae!) or Values by clicking the relevant button in the View group on the ShapeSheet Tools ribbon.

Notice that Visio provides IntelliSense when editing formulae. This is new in Visio 2010, and is a great help to all ShapeSheet developers.

Also notice that the contents of some of the cells are shown in blue text, whilst others are black. This is because the blue text denotes that the values are stored locally with this shape instance, whilst the black text refers to values that are stored in the Master shape. Usually, the more black text you see, the more memory efficient the shape is, since less is needed to be stored with the shape instance. Of course, there are times when you cannot avoid storing values locally, such as the PinX and PinY values in the above screenshot, since these define where the shape instance is in the page. The following VBA code returns 0 (False):

ActivePage.Shapes("Task").Cells("PinX").IsInherited

But the following code returns -1 (True) :

ActivePage.Shapes("Task").Cells("Width").IsInherited

The Edit Formula button opens a dialog to enable you to edit multiple lines, since the edit formula bar only displays a single line, and some formulae can be quite large.

You can display the Formula Tracing window using the Show Window button in the Formula Tracing group on the ShapeSheet Tools present in Design tab. You can decide whether to Trace Dependents, which displays other cells that have a formula that refers to the selected cell or Trace Precedents, which displays other cells that the formula in this cell refers to.

Of course, this can be done in code too. For example, the following VBA code will print out the selected cell in a ShapeSheet into the Immediate Window:

Public Sub DebugPrintCellProperties ()
'Abort if ShapeSheet not selected in the Visio UI
    If Not Visio.ActiveWindow.Type = Visio.VisWinTypes.visSheet Then
        Exit Sub
    End If
Dim cel As Visio.Cell
    Set cel = Visio.ActiveWindow.SelectedCell
'Print out some of the cell properties
    Debug.Print "Section", cel.Section
    Debug.Print "Row", cel.Row
    Debug.Print "Column", cel.Column
    Debug.Print "Name", cel.Name
    Debug.Print "FormulaU", cel.FormulaU
    Debug.Print "ResultIU", cel.ResultIU
    Debug.Print "ResultStr("""")", cel.ResultStr("")
    Debug.Print "Dependents", UBound(cel.Dependents)
'cel.Precedents may cause an error
On Error Resume Next
    Debug.Print "Precedents", UBound(cel.Precedents)
    
End Sub

In the previous screenshot, where the Actions.SetDefaultSize.Action cell is selected in the Task shape from the BPMN Basic Shapes stencil, the DebugPrintCellProperties macro outputs the following:

Section	240
Row	2
Column	3
Name	Actions.SetDefaultSize.Action
FormulaU	SETF(GetRef(Width),User.DefaultWidth)+SETF(GetRef(Height),User.DefaultHeight)
ResultIU	0
ResultStr(“”)	0.0000
Dependents	0
Precedents	4

Firstly, any cell can be referred to by either its name, or section/row/column indices, commonly referred to as SRC.

Secondly, the FormulaU should produce a ResultIU of 0, if the formula is correctly formed and there is no numerical output from it.

Thirdly, the Precedents and Dependents are actually an array of referenced cells.

Can I print out the ShapeSheet settings?

You can download and install the Microsoft Visio SDK from the Visio Developer Center (visit http://msdn.microsoft.com/en-us/office/aa905478.aspx). This will install an extra group, Visio SDK, on the Developer ribbon and one extra button Print ShapeSheet.

I have chosen the Clipboard option and pasted the report into an Excel worksheet, as in the following screenshot:

The output displays the cell name, value, and formula in each section, in an extremely verbose manner. This makes for many rows in the worksheet, and a varying number of columns in each section.

What is a function?

A function defines a discrete action, and most functions take a number of arguments as input. Some functions produce an output as a value in the cell that contains the formula, whilst others redirect the output to another cell, and some do not produce a useful output at all.

The Developer ShapeSheet Reference in the Visio SDK contains a description of each of the 197 functions available in Visio 2010, and there are some more that are reserved for use by Visio itself.

Formulae can be entered into any cell, but some cells will be updated by the Visio engine or by specific add-ons, thus overwriting any formula that may be within the cell. Formulae are entered starting with the = (equals) sign, just as in Excel cells, so that Visio can understand that a formula is being entered rather than just a text. Some cells have been primed to expect text (strings) and will automatically prefix what you type with =” (equals double-quote) and close with “(double-quote) if you do not start typing with an equal sign.

For example, the function NOW(), returns the current date time value, which you can modify by applying a format, say, =FORMAT(NOW(),”dd//MM/YYYY”). In fact, the NOW() function will evaluate every minute unless you specify that it only updates at a specific event. You could, for example, cause the formula to be evaluated only when the shape is moved, by adding the DEPENDSON() function:

=DEPENDSON(PinX,PinY)+NOW()

The normal user will not see the result of any values unless there is something changing in the UI. This could be a value in the Shape Data that could cause linked Data Graphics to change. Or there could be something more subtle, such as the display of some geometry within the shape, like the Compensation symbol in the BPMN Task shape.

In the above example, you can see that the Compensation right-mouse menu option is checked, and the IsForCompensation Shape Data value is TRUE. These values are linked, and the Task shape itself displays the two triangles at the bottom edge.

The custom right-mouse menu options are defined in the Actions section of the shape’s ShapeSheet, and one of the cells, Checked, holds a formula to determine if a tick should be displayed or not. In this case, the Actions.Compensation.Checked cell contains the following formula, which is merely a cell reference:

=Prop.BpmnIsForCompensation

Prop is the prefix used for all cells in the Shape Data section because this section used to be known as Custom Properties. The Prop.BpmnIsForCompensation row is defined as a Boolean (True/False) Type, so the returned value is going to be 1 or 0 (True or False).

Thus, if you were to build a validation rule that required a Task to be for Compensation, then you would have to check this value.

You will often need to branch expressions using the following:

IF(logical_expression, value_if_true, value_if_false)