• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Using Visio in Education for GIS
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Visualize Complex Processes with Microsoft Visio
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Formatting Shape Data in Visio

Published on July 24, 2015 by David Parker

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

Related

Filed Under: Shape Data, VBA, Visio Tagged With: Shape Data, ShapeSheet Functions

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript LayerManager Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Setup and Deployment Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Validation VBA Video Visio Visio 2007 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • Amazon
  • E-mail
  • Facebook
  • LinkedIn
  • Twitter
  • YouTube

Search this website

Recent posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Copyright © 2025 · Executive Pro on Genesis Framework · WordPress · Log in