Chapter 3: Understanding the ShapeSheet (Microsoft Visio 2010 Business Process Diagramming and Validation)

Applies to: Office 2010 | VBA | Visio 2010

In this article
Introduction
Where Is the ShapeSheet?
Reading a Cell's Properties
Can I Print Out the ShapeSheet Settings?
What Is a Function?
What Are the Important Sections for Rules Validation?
Summary
Additional Resources
About the Author

Summary:  Learn about the Microsoft Visio 2010 ShapeSheet and the key sections, rows, and cells, along with the functions available for writing ShapeSheet formulae, as they apply to structured diagrams.

This article is an excerpt from Microsoft Visio 2010 Business Process Diagramming and Validation by David J. Parker from Packt Publishing (ISBN 1849680140, copyright 2010 by Packt Publishing, all rights reserved). No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Contents

• Introduction

• Where Is the ShapeSheet?

• Reading a Cell's Properties

• Can I Print Out the ShapeSheet Settings?

• What Is a Function?

• What Are the Important Sections for Rules Validation?

• Summary

• Additional Resources

• About the Author

Introduction

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 chapter 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 Microsoft Office Fluent user interface (UI), which has a Show ShapeSheet button. The drop-down list on the button allows you to choose which ShapeSheet window to open.

Figure 1. Show ShapeSheet button

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:

Figure 2. Show ShapeSheet shortcut menu command

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.

Figure 3. View Sections dialog box

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 formulae is listed in the following table.

Table 1. ShapeSheet formula syntax

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 coordinates 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.

Figure 4. Insert Section dialog box

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.

Figure 5. ShapeSheet formula bar

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.

Figure 6. Edit Formula dialog box

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.

Figure 7. Tracing formulas

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

I have tried to be selective about the properties displayed to illustrate some points.

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. This will install an extra group, Visio SDK, on the Developer ribbon and one extra button, Print ShapeSheet.

Figure 8. Print ShapeSheet dialog box

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

Figure 9. Excel worksheet

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 Visio 2010 ShapeSheet Reference in the Visio 2010 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.

Figure 10. Compensation submenu

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)

You can nest expressions inside each other.

You will often need to use the logical expression evaluators like the following:

• AND(logical_expression1, logical_expression2 [, opt_logical_expression3][,...] [, opt_logical_expressionN])

• OR(logical_expression1, logical_expression2 [, opt_logical_expression3][,...] [, opt_logical_expressionN])

You may also need to reverse a Boolean value using NOT(logical_expression).

These are the main evaluators and there are no looping functions available. Now let's look at each relevant ShapeSheet section.

What Are the Important Sections for Rules Validation?

When validating documents, there are some sections that are more important and more regularly used than others. Therefore, we will look at just a few of the sections in detail.

The User-Defined Cells Section

The User-defined Cells section is used to store hidden variables (because they are never displayed in the UI unless you open the ShapeSheet) and perform calculations. There are just two columns in this section. The first, Value, is normally where the real work is done, and the second, Prompt, is often used as a description of the row.

Microsoft will often use specially named User-defined Cell rows to hold specific information. For example, the Task shape has a row named User.msvShapeCategories, which is used to specify the category or categories that it belongs to. The Task shape belongs, not surprisingly, to the Task category, but it could have belonged to multiple categories by having them expressed as a semicolon-separated list.

What Category Is a Shape?

Visio 2010 introduced the new function HASCATEGORY(category) in order to support structured diagrams.

In the BPMN diagrams, the Task shape has the Task category, so the following formula will return TRUE for the Task shape:

=HASCATEGORY("Task")

But the following will return FALSE because the string is case-sensitive:

=HASCATEGORY("task")

Therefore, it is important to know what the exact spelling and case are for the values in the User.msvShapeCategories cells.

Consequently, I have written the following VBA macro, ListStencilShapeCategories, to list all of the categories used in the docked stencils, and then to optionally list the stencil title, master name, and a count of the number of categories that the master belongs to.

Public Sub ListStencilShapeCategories()
' List the categories used in the docked stencils
    If Not Visio.ActiveWindow.Type = _
        Visio.VisWinTypes.visDrawing Then
        Exit Sub
    End If
Dim aryStencils() As String
    Visio.ActiveWindow.DockedStencils aryStencils
Dim stenCounter As Integer
Dim sten As Visio.Document
Dim mst As Visio.Master
Dim shp As Visio.Shape
Dim categories() As String
Dim catCounter As Integer
Dim category As String
Dim colMasters As Collection
Dim dicCategories As Dictionary
    Set dicCategories = New Dictionary
    ' Loop thru the stencils 
    For stenCounter = 0 To UBound(aryStencils)
        ' Do not read the document stencil
        If Len(aryStencils(stenCounter)) > 0 Then
            Set sten = _
                Visio.Documents(aryStencils(stenCounter))
            ' Loop thru each master in the stencil
            For Each mst In sten.Masters
                Set shp = mst.Shapes.Item(1)
                ' Check that the Category cell exists
                If shp.CellExists("User.msvShapeCategories", _
                    VisExistsFlags.visExistsAnywhere) Then
                    ' The default List Separator is ;
                    categories = _
                        Split(shp.Cells("User.msvShapeCategories").ResultStrU(""), ";")
                    For catCounter = 0 To UBound(categories)
                        If dicCategories.Exists(categories(catCounter)) Then
                            Set colMasters = dicCategories.Item(categories(catCounter))
                            colMasters.Add sten.Title & " - " & mst.Name & _
                                " (" & UBound(categories) + 1 & ")"
                            Set dicCategories.Item(categories(catCounter)) = colMasters
                        Else
                            Set colMasters = New Collection
                            colMasters.Add sten.Title & " - " & mst.Name & _
                                " (" & UBound(categories) + 1 & ")"
                            dicCategories.Add _
                                categories(catCounter), colMasters
                        End If
                    Next catCounter
                End If
            Next
        End If
    Next
    
Dim msg As String
    msg = "There are " & UBound(dicCategories.Keys) + 1 & _
        " categories in the " & _
        UBound(aryStencils) + 1 & " docked stencils:" & vbCrLf
    For catCounter = 0 To UBound(dicCategories.Keys)
        Set colMasters = dicCategories.Item(dicCategories.Keys(catCounter))
        msg = msg & vbCrLf & dicCategories.Keys(catCounter) & " - " & colMasters.Count & " masters"
    Next catCounter
    msg = msg & vbCrLf & vbCrLf & "Do you want to view the details?"
Dim ret As Integer
Dim mstCounter As Integer
    ret = MsgBox(msg, vbInformation + vbYesNo, _
        "ListStencilShapeCategories")
    If Not ret = vbYes Then
        Exit Sub
    End If
    
    ' Display the masters for each category
    For catCounter = 0 To UBound(dicCategories.Keys)
        Set colMasters = _
            dicCategories.Item(dicCategories.Keys(catCounter))
        msg = colMasters.Count & _
            " masters that have the Category : " & _
            dicCategories.Keys(catCounter) & vbCrLf
        For mstCounter = 1 To colMasters.Count
            msg = msg & vbCrLf & colMasters.Item(mstCounter)
        Next mstCounter
        msg = msg & vbCrLf & vbCrLf & _
            "Do you want to continue to view the next category?"
        ret = MsgBox(msg, vbInformation + vbYesNo, _
            "ListStencilShapeCategories")
        If Not ret = vbYes Then
            Exit For
        End If
    Next catCounter
End Sub

If you run this macro with, say, a blank document created from the BPMN Diagram (Metric) template, then you will be presented with a list of all of the categories found in the docked stencils as shown in the following screenshot:

Figure 11. List of stencil categories

If you continue to view the details of the listed categories, then you will be presented with a dialog listing the stencil, master, and category count in brackets:

Figure 12. Category details

This is essential information for building validation rules that use category.

What Structure Type Is a Shape?

Visio 2010 structured diagrams use another specifically named User-defined Cell, User.msvStructureType, to define the Structure Type of the shape.

I will spare you the VBA code for the ListStencilStructureTypes method in this text because it is very similar to the ListStencilShapeCategories method previously, but we can discover that there are three different Structure Types in the BPMN stencils. They are:

• Container: There are 12 masters in all, including Expanded Sub-Process, Pool/Lane, and Group.

• Callout: There is only one master, Text Annotation.

• List: There are two masters, Swimlane List and Phase List.

Is the Shape Inside a Container?

The formula =CONTAINERCOUNT() returns 1 in the examples because the Document shape is inside the container shape labeled Drafting. If there are nested containers, then the function will return the total number of containers that the shape is within.

If the shape is inside a container, then you can use the new =CONTAINERSHEETREF(index[, category]) function to get a reference to the container shape, and thus to any of the cells inside it. As there can be multiple containers, the index, which is one-based (the first index number is 1, not 0), specifies which one to return. The category argument is optional.

How Many Shapes Are Inside a Container Shape?

Perhaps surprisingly, the CONTAINERMEMBERCOUNT() returns 9 in this example, because it includes the three flowchart shapes, the three callouts, and the three connectors between the flowchart shapes, even though the last three are 1-D shapes. If either end of a connector is outside of the container, then it would not be counted. Also, note that the lines between the callouts and the flowchart shapes are part of the callout shape, and thus do not count either. It can be seen in the following screenshot:

Figure 13. CONTAINERMEMBERCOUNT function

Where Is the Shape in the List?

In this example, I have used the List box and List box item shapes from the Controls stencil in the Software and Database | Wireframe Diagram template, to construct a partial Visio Type Library object model. I have added two User-defined Cells to the ShapeSheet of the List box item so that the item contains the index of its position in the List box and the text of the List box shape.

Figure 14. LISTSHEETREF function

This is achieved by using the following formula in the User.ListOrder.Value cell:

=LISTORDER()

The ListSheetRef() function will return the containing list box shape (if there is one), and then its cells and properties can be referenced by following this with an exclamation mark. Therefore, the formula to return the text of the container list box in the User.ListHeaderText.Value cell is:

=SHAPETEXT(LISTSHEETREF()!TheText)

However, this formula will display =#REF! if the list item is not within a list box, so a more complete formula would be:

=IF(LISTORDER()=-1,"n/a",SHAPETEXT(LISTSHEETREF()!TheText))

Alternatively, these values could be surfaced to the UI as Shape Data rows, in which case you would protect them from being overwritten by using the GUARD() function.

=GUARD(IF(LISTORDER()=-1,"n/a",SHAPETEXT(LISTSHEETREF()!TheText)))

In either case, having these values available on the List box item makes reports and rule validation much easier.

How Many Shapes Are in a List Shape?

A List shape can contain the function LISTMEMBERCOUNT() in order to get the number of list item shapes within it.

Are There Any Callouts Attached to a Shape?

In the following examples, I have added a new row to the User-defined Cells section, named myTestFormula, of the first Document shape in my example Packt Editorial Process diagram. I have entered the function CALLOUTCOUNT() into the Value cell of this row, and you can see that the result is displayed as 1.0000.

Figure 15. CALLOUTCOUNT function

This is because there is a single Callout shape connected to this shape.

Which Shape Is a Callout Connected To?

When a Callout shape is connected to another shape, you can get at any of the cells in that target shape by use of the CALLOUTTARGETREF() function. In the following example, I have used a formula to return the text of the target shape. The following formula uses the SHAPETEXT() function to return the text of the associated Callout shape:

=SHAPETEXT(CALLOUTTARGETREF()!TheText)

Figure 16. CALLOUTTARGETREF function

For example, this could be surfaced in the UI as a Shape Data row, thus making reporting easier.

The Shape Data Section

The Value cell stores the actual values, and because it is the default cell in the row, it can be retrieved in a ShapeSheet formula as Prop.Cost, for example, rather than Prop.Cost.Value. Other cells have to be referenced explicitly, as say, Prop.Cost.Invisible, for example.

The ShapeSheet developer cannot move Shape Data rows up or down, but the display order can be modified by entering text into the SortKey cells. The Visio UI will sort the Shape Data rows according to the text sort order of the values in these cells.

The visibility of a Shape Data row is controlled by the Boolean result of the formula in the Invisible cell.

There are eight different types in Shape Data rows, almost all of which are data types. So, it is important to understand how to handle their values in any rule validation.

Figure 17. Shape Data types

Each type is defined by an enumerator visPropTypes, which has the following values:

• String

• Fixed list

• Number

• Boolean

• Variable list

• Date or time

• Duration

• Currency

The default Type is 0, so if the Type has not been set then it is assumed to be String.

Each row in the Shape Data section can be named, and has a Label that is displayed in the UI. If a row is not specifically named, then it will be automatically named Row_1, Row_2, and so on.

If your Visio diagrams have been used with Data | Link Data to Shapes, then you need to know that this feature will attempt to link the data by matching the text in the Shape Data row's Label cell with the column header, or the field name of the external data first and it is case-sensitive. If the target shape does not already have a Shape Data row, then Visio will automatically create a row named after the Label text, but with a _VisDM_ prefix, and any spaces or special characters removed.

Figure 18. VisDM rows automatically created by Visio

Therefore, you may need to match values based on the Label rather than the row Name, if your solution uses Link Data to Shapes.

The String Type

String data is just text that has been entered into a Shape Data row. It may have been imported from elsewhere, for example using the Link Data to Shapes feature, or it may just have been entered manually. In either case, if your validation rules are using text values to match, then you may be wise to ensure that the case is consistent by using the LOWER() or UPPER() functions, which will force the text to be in lowercase or uppercase, respectively. Alternatively, use case sensitivity on the string matching functions below.

The Format cell may contain a pattern that modifies the display of the string to be in lowercase or uppercase, but that does not mean that the Value is in these cases.

You can use the STRSAME(string1,string2[,opt_ignore_case]) and STRSAMEEX(string1,string2,localeID,flag) functions to compare two strings, though you may need to use TRIM(string) to remove any accidental spaces at the beginning and end of the string.

Visio also provides a few functions to get specific parts of a string. LEFT(string[,num_of_chars]) and RIGHT(string[,num_of_chars]) functions will return the specified number of characters (default is 1) from the start or end of a string. MID(string,start_num,num_of_chars) function will extract characters from within a string.

You can get the starting position of a string within another by using the FIND(find_text,within_text[,opt_start_num][,opt_ignore_case]) function. You may also need to use LEN(string) to get the number of characters in a text string.

Be aware that there are some solutions that will automatically enter the string values, and there are others that may contain special formulae to retrieve a value, For example, the Cross Functional Flowchart template in Visio 2010 gets the value of the Prop.Function Shape Data row of a shape from the text that has been entered into the Swimlane that it is within.

Figure 19. Shape Data String type

This is done with the following formula in the Value cell:

=IFERROR(CONTAINERSHEETREF(1,"Swimlane")!User.VISHEADINGTEXT,"")

What this means is that if the shape is surrounded by a container with the category Swimlane, then return the value in the User.visHeadingText cell; otherwise, just return an empty string.

Therefore, the Prop.Function.Value will be "" if the Process shape is not inside a Swimlane shape; otherwise, it will be the value of the text in the container Swimlane shape.

The Fixed List Type

If a Shape Data row is set to a Fixed List type, then the value must exist in the drop-down list.

Figure 20. Shape Data Fixed List type

Recent versions of Visio will automatically create a formula in the Value cell that returns the string value at a specific zero-based index in this list. For example look at the following value:

=INDEX(2,Prop.BpmnStatus.Format)

It will return the third item from the semicolon-separated list in the Prop.BpmnStatus.Format cell, which contains the formula:

="None;Ready;Active;Cancelled;Aborting;Aborted;Completing;Completed"

Thus, the value is Active.

If you were using rules based on a Fixed List value, then it might be better to use the index rather than the string value, since this could be mistyped, or even translated into a different language. Therefore, you could get the index position using the LOOKUP() function as follows:

=LOOKUP(Prop.BpmnStatus,Prop.BpmnStatus.Format)

The Number Type

Visio stores numbers as double precision numeral, but the Format cell may be used to modify the display in the UI.

Figure 21. Shape Data Number type

However, Visio also provides some functions to enable rounding and calculations. Commonly used functions are:

• ROUND(number,numberofdigit),INT(number)

• INTUP(number) to round a number to a given precision, or to round down or up to the next integer.

• FLOOR(number[, opt_multiple]), which rounds a number towards zero, to the next integer, or the next instance of the optional multiple.

• CEILING(number[, opt_multiple]), which rounds a number away from zero.

The MODULUS(number, divisor) function can also be useful if you need to formulate a rule that requires specific values to be entered, for example.

ABS(number) function returns the absolute value, and SIGN(number[, opt_fuzz]) returns a value that represents the sign of a number.

Since Visio is a graphics system, there are a large number of functions for dealing with points, lines, and angles, that are not really relevant for rules validation.

You can simply compare number values using the equals sign (=), and you can add values using number1+number2, or SUM(number1[, opt_number2] [, opt_number3] [, ...] [, opt_number14]). Multiplication and division of values is simple, using number1*number2 and number1/number2.

You can get the maximum or minimum value of a series of values with MAX(number1,number2,...,numberN) or MIN(number1,number2,...,numberN).

The Boolean Type

Often referred to as True/False or Yes/No type, the Boolean type returns FALSE (zero) or TRUE (non-zero). Visio actually stores TRUE as 1 internally, but some other programming languages use -1, so you may need to use the ABS() function to get the absolute value, depending on your circumstances.

Figure 22. Shape Data Boolean type

The Variable List Type

A Variable List type is similar to the Fixed List mentioned earlier, but it is usually not appropriate to retrieve the index position of the selected value because Visio will automatically add values to the list if the user enters a value that is not present already. As can be seen in the following example, this even means that the same word can be repeated in the list if the case is different.

Figure 23. Shape Data Variable List type

Also, the list is only extended for this particular shape instance, and other process shapes in the diagram will have their own variable list.

So, a Variable List may seem like a flexible feature for the user, but it is a nightmare for data validation, and the resultant text value should be treated just like the String type above.

The Date Type

Visio provides a Date Picker for the user if the Type is set as Date for a Shape Data row. However, a custom solution may use either a date or a time picker, since a DATETIME(double) value is actually stored.

Figure 24. Shape Data Date type

The display format of the date time value can be modified using the Format cell, but any rules validation should use the double precision number value. This will avoid any problems with the optional positioning of day and months in a date string. The United Kingdom, for example, always uses DD/MM/YY, but the United States uses MM/DD/YY.There are a number of functions that enable you to get to specific integer parts of a date time value. They are:

• DAY(datetime[, opt_lcid])

• MONTH(datetime[, opt_lcid])

• YEAR(datetime[, opt_lcid])

• HOUR(datetime[, opt_lcid]), MINUTE (datetime[, opt_lcid])

• SECOND(datetime[, opt_lcid])

There are also a couple of functions to return the integer value of the day in the week or in the year, namely, WEEKDAY (datetime[, opt_lcid]) and DAYOFYEAR(datetime[, opt_lcid]).

If you need to convert text to dates or times, then you can use the DATETIME(datetime|expression[, opt_lcid]),DATEVALUE(datetime|expression[, opt_lcid]) or TIMEVALUE(datetime|expression[, opt_lcid]) functions.

However, if you have the integer parts of a date or time, then use the DATE(year,month,day) or TIME(hour,minute,second) functions.

Since date time is stored as double precision numbers internally, you can check if they are equal (=), before (<), or after (>) easily enough, but you may wish to check one date time against another within a duration range. For example, you may want to verify that Prop.EndDate is greater than the Prop.StartDate plus the Prop.Duration. This could be expressed as:

= Prop.EndDate<(Prop.StartDate+Prop.Duration)

This will return True or False.

Similarly, you could test if the Prop.EndDate is within the next 12 weeks by using the following:

=Prop.EndDate<(Now()+12 ew.)

You can use any of the duration units in such formulae.

The Duration Type

Visio can store Duration values expressed as elapsed day (ed.), hour (eh.), minute (em.), second (es.), or week (ew.). They are all stored internally as days and fractions of days.

Figure 25. Shape Data Duration type

The Format cell may have been used to modify the presentation in the UI. The Visio 2010 SDK contains a page called About Format Pictures in the documentation, where you can review all of the different format pictures.

The Currency Type

The last Type is Currency, the display of which defaults to the system settings, although it is stored as a double precision number.

Figure 26. Shape Data Currency type

The Format cell may be used to modify the appearance of the value in the UI. See the Visio 2010 SDK for more information.

Generally, you would treat Currency in a similar manner to the Number Type described earlier.

The Hyperlinks Section

A shape in Visio can have multiple hyperlinks but one row has a reserved name, Hyperlink.msvSubprocess, to provide a link to a sub-process page.

Figure 27. Hyperlink.msvSubprocess row

So, you can test if a shape has a sub-process reference with the following formula:

=NOT(ISERR(INT(INDEX(0,"Hyperlink.msvSubprocess.NewWindow"))))

You cannot easily test a page to check if it is a sub-process, or where it is used in a main process, because a sub-process may be part of many parent processes.

Layer Membership

Shape Layer Membership is more complicated than you might think. The ShapeSheet of the page stores the Layers for that page, and, as you can see from the following screenshot, an individual shape's ShapeSheet merely stores a list of indexes of the page's Layers.

Figure 28. Page layers

Layers with the same name may have a different index number on different pages within the same document. Therefore, you cannot create a rule that tests for a layer by index. The layer settings in the page control whether a layer is visible or printable.

You could have a rule that insists that all relevant shapes must be assigned to a layer, which is given as:

=NOT(STRSAME(LayerMember,""))

Or a rule that states that it must be on one layer only:

=NOT(AND(STRSAME(LayerMember,""),FIND(";",LayerMember,1)))

You can then check if the assigned layer is currently visible:

=INT(INDEX(0,"ThePage!Layers.Visible["&INDEX(0,LayerMember)+1&"]"))

Notice how you can refer to the ShapeSheet of the page using the ThePage! syntax. You can similarly refer to the ShapeSheet of the document using the TheDoc! syntax.

Summary

In this chapter we have explored a lot of the ShapeSheet functions that can be used in validation tests, and we have focused on the ShapeSheet sections that are probably most relevant for creating validation rules.

You may have noticed that there are no functions for checking connectivity in this chapter. Well, they are part of the new quasi-ShapeSheet functions that can only be used with the Validation API, so we will examine those later.

In the next chapter, we will examine the new Validation Rules API and you will understand why it is important to understand both the Visio object model and ShapeSheet functions, if you want to be able to analyze existing rules or create your own.

Additional Resources

• Visio 2010 SDK

• Visio Developer Center

• Adding Structure to Diagrams in Visio 2010

• bVisual

• bVisual Blog

About the Author

  David J. Parker is a Visio MVP and the owner of bVisual Ltd. He writes data visualization solutions, primarily using Visio and Bing Maps.

David writes a regular blog about Visio at bvisual.spaces.live.com, and he is also the author of Visualizing Information with Microsoft Office Visio 2007.