Understanding ShapeSheetâ„¢ in Microsoft Visio 2010

Exclusive offer: get 50% off this eBook here
Microsoft Visio 2010 Business Process Diagramming and Validation

Microsoft Visio 2010 Business Process Diagramming and Validation — Save 50%

Create custom Validation Rules for structured diagrams and increase the accuracy of your business information with Microsoft Visio 2010 Premium Edition with this book and eBook

€23.99    €12.00
by David John Parker | July 2010 | Enterprise Articles Microsoft

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)
Microsoft Visio 2010 Business Process Diagramming and Validation Create custom Validation Rules for structured diagrams and increase the accuracy of your business information with Microsoft Visio 2010 Premium Edition with this book and eBook
Published: July 2010
eBook Price: €23.99
Book Price: €38.99
See more
Select your format and quantity:

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 is 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:

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:

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 shown 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:

Where is the shape in the List?

In this example, I have used the List box and List box item shapes from 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.

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.

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)

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.

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.

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

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.

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 semi-colon 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)
Microsoft Visio 2010 Business Process Diagramming and Validation Create custom Validation Rules for structured diagrams and increase the accuracy of your business information with Microsoft Visio 2010 Premium Edition with this book and eBook
Published: July 2010
eBook Price: €23.99
Book Price: €38.99
See more
Select your format and quantity:

The Number type

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

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

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.

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.

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 UK, for example, always use DD/MM/YY, but the US 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.

The Format cell may have been used to modify the presentation in the UI. The Visio Developer 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.

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

Generally, you would treat Currency in a similar manner to 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 subprocess page.

So, you can test if a shape has a subprocess 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 subprocess, or where it is used in a main process, because a subprocess 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.

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 article 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 article. Well, they are part of the new quasi-ShapeSheet functions that can only be used with the Validation API.

About the Author :


David John Parker

David began his professional life as a qualified building architect in the UK, but found the challenge of linking information to drawings too exciting. He gave up architecture in the late 80s to follow his interest and became a developer and consultant on facilities and cable management software. In 1996, David began to use Visio linked to enterprise databases, and in 1998 started his own data visualization consultancy business, bVisual ltd. His Visio-based solutions have extended to include project management, risk analysis, and process flows, amongst other things.

David has been a speaker at several Visio conferences over the years, and has been awarded Most Valued Professional status for his services to the Visio community for the last five years. He writes a regular Visio developer blog, and has previously authored a book about visualizing information with Visio 2007.

Books From Packt


Documentum 6.5 Content Management Foundations
Documentum 6.5 Content Management Foundations

IBM Cognos 8 Report Studio Cookbook
IBM Cognos 8 Report Studio Cookbook

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

Microsoft Silverlight 4 Business Application Development: Beginner’s Guide
Microsoft Silverlight 4 Business Application Development: Beginner’s Guide

Microsoft Dynamics NAV 2009 Application Design
Microsoft Dynamics NAV 2009 Application Design

Oracle Application Express 3.2 – The Essentials and More
Oracle Application Express 3.2 – The Essentials and More

Oracle 11g R1 / R2 Real Application Clusters Handbook
Oracle 11g R1 / R2 Real Application Clusters Handbook

Least Privilege Security for Windows 7, Vista and XP
Least Privilege Security for Windows 7, Vista and XP

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software