Formatting Shape Data in Visio

One of the best aspects of Visio is the ability of shapes to hold data, and the easiest way to do this is to define rows of Shape Data. Each row can be defined as holding one of eight different types, and most of these can have their values formatted for display. In this article I discuss the formatting of these values, using either ShapeSheet functions or automation methods. The driver for this is my on-going frustration with the Microsoft provided standard Shape Data window because of its shortcomings … for example, its lack of multi-line support even though it can hold 64k characters in each row, and its lack of a time control, even though it can store dates and time.

image

I created a simple shape which has at least one example of each type of Shape Data, and applied some sample formatting using the Define Shape Data dialog.

I then inserted a table of each row into the shape, and displayed the Type, Label, actual Value, the Format string, and the formatted value (if applicable).

image

My intention was to understand if the ShapeSheet FORMAT(…) function could be applied in all cases, and it turns out that t can’t. So, I had to test if a Format actually existed before applying it. Therefore, I inserted a custom formula similar to the following in the fifth column of the table:

=IF(FORMULAEXISTS(Prop.FString.Format),FORMAT(Prop.FString,Prop.FString.Format),Prop.FString)

If I look at the values in the ShapeSheet of my sample shape, then it is clear that the display in the Shape Data window is slightly different in some cases. Look at the Formatted String and Time rows, for example.

image

So, now if I switch the view the formulas in the ShapeSheet, I can see how the values are represented as formulas:

image

Thus, it is apparent that the Shape Data window value display is not exactly available from the ShapeSheet direct, so I wrote a VBA method to display the data values exactly how they appear in the Shape Data window:

Type

Label

Format

Value

0

String

 

This is a normal case sentence

0

Formatted String

@

THIS IS A NORMAL CASE SENTENCE

2

Number

 

123.45

2

Formatted Number with units

#.#### u

123.45 cm

2

Number fraction with units

# ##/## u

123 9/20 cm

1

Fixed list

;A;B;C;D;E;F

A

4

Variable List

;A;B;C;D;X

X

3

Boolean

 

TRUE

7

Currency

 

£123.45

7

Currency System

@

£123.45

7

Currency with unit

U 0.00

£ 123.45

5

Date

 

24/07/2015

5

Date formatted

{{dd MMM. yy}}

24 Jul. 15

5

Time

{{h:mm:ss am/pm}}

1:21:00 PM

6

Duration       

 

123.4500 ed.

6

Formatted Duration

[h]:[mm]

123:27

 

The code to produce the display above (without the tabular grid) is:

Public Sub DisplayData()
    If Application.ActiveWindow.Selection.Count = 0 Then
        Exit Sub
    End If
Dim shp As Visio.Shape
    Set shp = Application.ActiveWindow.Selection.PrimaryItem
   
Dim sect As Integer
    sect = Visio.VisSectionIndices.visSectionProp
Dim iRow As Integer
Dim format As String
Dim value As String
Dim dataType As Integer
Dim toUpper As Boolean
Dim toLower As Boolean
Dim label As String
Dim units As Integer
Dim txt As String

    txt = "Type" & vbTab & "Label" & vbTab & "Format" & vbTab & "Value"
    For iRow = 0 To shp.RowCount(sect) – 1
        If shp.CellsSRC(sect, iRow, _
            Visio.VisCellIndices.visCustPropsInvis).ResultIU = 0 Then
           
            dataType = shp.CellsSRC(sect, iRow, _
                Visio.VisCellIndices.visCustPropsType).ResultIU
            format = shp.CellsSRC(sect, iRow, _
                Visio.VisCellIndices.visCustPropsFormat).ResultStr("")
            label = shp.CellsSRC(sect, iRow, _
                Visio.VisCellIndices.visCustPropsLabel).ResultStr("")
            units = shp.CellsSRC(sect, iRow, _
                Visio.VisCellIndices.visCustPropsValue).units
               
            If Len(format) > 0 Then
                If dataType = 1 Or dataType = 4 Then
                    value = shp.CellsSRC(sect, iRow, _
                        Visio.VisCellIndices.visCustPropsValue).ResultStr("")
                ElseIf dataType > 0 Then
                    value = Application.FormatResult(shp.CellsSRC(sect, iRow, _
                            Visio.VisCellIndices.visCustPropsValue).ResultIU, _
                        "", units, format)
                Else
                    If InStr(format, "+") > 0 Then
                        toUpper = True
                    ElseIf InStr(format, "-") > 0 Then
                        toLower = True
                    End If
                    format = Replace(Replace(format, "+", ""), "-", "")
                    value = Replace(format, "@", _
                        shp.CellsSRC(sect, iRow, _
                            Visio.VisCellIndices.visCustPropsValue).ResultStr(""))
                    If toUpper Then
                        value = UCase(value)
                    End If
                    If toLower Then
                        value = LCase(value)
                    End If
                End If
            Else
                If dataType = 2 Then
                    value = CStr(shp.CellsSRC(sect, iRow, _
                            Visio.VisCellIndices.visCustPropsValue).ResultIU)
                Else
                    value = shp.CellsSRC(sect, iRow, _
                        Visio.VisCellIndices.visCustPropsValue).ResultStr("")
                End If
            End If
            txt = txt & vbCrLf & dataType & vbTab & label & vbTab & format & vbTab & value
        End If
    Next iRow
   
    Debug.Print txt
   
End Sub

 

So, I had to make special consideration for lists and text formatting, and for displaying numbers if there is no formatting.

Armed with this knowledge, I can continue to create a replacement Shape Data window add-in in C# and WPF.

 

Visio Format(…) function : https://msdn.microsoft.com/EN-US/library/office/ff766359.aspx

About Format Pictures : https://msdn.microsoft.com/EN-US/library/office/ff768634.aspx

Application.FormatResult Method : https://msdn.microsoft.com/EN-US/library/office/ff765455.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: