• 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 / Automating Area and Perimeter Length Shape Data

Published on April 21, 2010 by David Parker

Automating Area and Perimeter Length Shape Data

[UPDATE: Microsoft introduced a new function into Visio 2010 called PATHLENGTH(…), so some of the following is now not required. See Automatic Line and Segment Lengths in Visio )

Another newsgroup question has asked about automating the update of shape area and perimeter lengths.  Now, this is an area (no pun intended) that I am most interested in because I used to do a lot of space planning.  Visio Professional does include Space and Boundary shapes on the Resources stencil that use an add-in to update the area, but does nothing about the perimeter length.  These shapes do highlight some of the issues to be considered though…

  • Visio pages can be scaled, and indeed the various floor plan and site layout templates in Visio are pre-scaled.  Most templates are not scaled, and  therefore default to 1:1 scale.
  • Visio measures everything internally in inches, although you can display in almost whatever units you choose.
  • The Visio Application object has a handy ConvertResult (StringOrNumber, UnitsIn, UnitsOut) method, which can be used for linear and area measurements … and can also be used for date and times.
  • Visio can store decimal numbers to a very high degree of precision (I counted 14 decimal places), but you almost always want to format the display.
  • Visio can call a method in a VBA project when the values in specified cells are changed.
  • It is easy to display Shape Data in a shape, either by using Data Graphics or by Insert Field

Therefore, I have written a method AddUpdateMetricsTrigger() which will add some Shape Data rows and an Action row to the page (or Master page):

image and then add some Shape Data rows and a User-defined row to the selected shapes:

image

The User.UpdateMetricsTrigger cell contains a formula that calls the UpdateMetrics() method in the bVisualUtilities VBA project. It will be called whenever the Width or Height of the shape is changed, or whenever the values in the new Shape Data rows in the page are changed.  It will also be called whenever the Refresh Metrics right mouse action in the page is selected.

So what happens when UpdateMetrics() is run?  Simply, the Area, Area Text, Length and Length Text values in the shape are updated. Any, or all, of these can be added to the shape as Data Graphics.

image

The page properties control the display of the Area Text and Length Text Shape Data values.

image

The user can change the display units and format for both area and the perimeter length.  This will be immediately updated by all shapes that have the User.UpdateMetricsTrigger formula.  Possibly the best way of using this utility is to create a master shape that already has the cells added, then just drag and drop them onto a page.

This is the code listing for the bVisualMetrics module in the bVisualUtilities VBA project:

First, I declared some constants for the name of the Shape Data rows on the shapes, the area and length units and the format strings.  These last three items will appear as fixed lists on the Page Shape Data rows.

Option Explicit
'These cells will be added to the page when the UpdateMaster is dropped onto a page
Const actionUpdateCell As String = "Actions.UpdateMetrics"
Const areaUnitCell As String = "Prop.AreaUnits"
Const areaUnits As String = "sq in;sq ft;sq yd;acres;sq mi;sq mm;sq cm;sq m;ha;sq km"
Const areaFormatCell As String = "Prop.AreaFormat"
Const lengthUnitCell As String = "Prop.LengthUnits"
Const lengthUnits As String = "in;ft;yd;nm;mi;mm;cm;m;km"   'nm=nautical mile
Const lengthFormatCell As String = "Prop.LengthFormat"
Const formats As String = "0;0.0;0.00;0.000;0.0000;0.00000;0.000000"
Const areaCell As String = "Prop.Area"
Const areaTextCell As String = "Prop.AreaText"
Const lengthCell As String = "Prop.Length"
Const lengthTextCell As String = "Prop.LengthText"

The AddUpdateMetricsTrigger() needs to run to prepare the shape and page (it can be run on the shape in the Master Edit window). [Actually this following listing is incomplete because I had trouble uploading it all …. so please download the code at the end of this article]

Public Sub AddUpdateMetricsTrigger()
Dim shp As Visio.Shape
Dim iSect As Integer
Dim iRow As Integer
Dim pag As Visio.Shape
    For Each shp In Visio.ActiveWindow.Selection
        If Not shp.ContainingMaster Is Nothing Then
            Set pag = shp.ContainingMaster.PageSheet
        ElseIf Not shp.ContainingPage Is Nothing Then
            Set pag = shp.ContainingPage.PageSheet
        End If
        iSect = Visio.VisSectionIndices.visSectionProp
        If pag.SectionExists(iSect, Visio.VisExistsFlags.visExistsAnywhere) = 0 Then
            pag.AddSection iSect
        End If
        If pag.CellExists(areaUnitCell, Visio.visExistsAnywhere) = 0 Then
            iRow = pag.AddNamedRow(iSect, Split(areaUnitCell, ".")(1), 0)
            pag.CellsSRC(iSect, iRow, Visio.VisCellIndices.visCustPropsLabel).FormulaU = _
                "=""Area Units"""
            pag.CellsSRC(iSect, iRow, Visio.VisCellIndices.visCustPropsType).FormulaU = _
                "=1"
            pag.CellsSRC(iSect, iRow, Visio.VisCellIndices.visCustPropsFormat).FormulaU = _
                "=""" & areaUnits & """"
            pag.CellsSRC(iSect, iRow, Visio.VisCellIndices.visCustPropsValue).FormulaU = _
                "=INDEX(0," & areaUnitCell & ".Format)"
        End If
    Next
Sub

The UpdateMetrics() method is called from each shape when the values in specified cells change:

Public Sub UpdateMetrics(ByVal shp As Visio.Shape)
Dim dbl As Double
Dim dblConv As Double
Dim txt As String
Dim fmt As String
Dim units As String
Dim ratio As Double
Dim iSect As Integer
Dim iRow As Integer
Dim pag As Visio.Shape
    If Not shp.ContainingMaster Is Nothing Then
        Set pag = shp.ContainingMaster.PageSheet
    ElseIf Not shp.ContainingPage Is Nothing Then
        Set pag = shp.ContainingPage.PageSheet
    End If
    If pag Is Nothing Then
        Exit Sub
    End If
    ratio = pag.Cells("DrawingScale").ResultIU / pag.Cells("PageScale").ResultIU
    iSect = Visio.VisSectionIndices.visSectionProp
    If UCase(Left(areaCell, 5)) = "PROP." Or UCase(Left(areaTextCell, 5)) = "PROP." Or _
        UCase(Left(lengthCell, 5)) = "PROP." Or UCase(Left(lengthTextCell, 5)) = "PROP." Then
         If shp.SectionExists(iSect, Visio.VisExistsFlags.visExistsAnywhere) = 0 Then
            shp.AddSection iSect
        End If
    End If
    'Update the area
    If shp.CellExists(areaCell, Visio.visExistsAnywhere) Then
        dbl = shp.AreaIU(False)
        fmt = pag.Cells(areaFormatCell).ResultStr("")
        units = pag.Cells(areaUnitCell).ResultStr("")
        dblConv = Application.ConvertResult(dbl, "sq in", units)
        shp.Cells(areaCell).FormulaU = dblConv
        shp.Cells(areaTextCell).FormulaU = """" & Format(dblConv, fmt) & " " & units & """"
    End If
    'Update the length
    If shp.CellExists(lengthCell, Visio.visExistsAnywhere) Then
        dbl = shp.LengthIU(False)
        fmt = pag.Cells(lengthFormatCell).ResultStr("")
        units = pag.Cells(lengthUnitCell).ResultStr("")
        dblConv = Application.ConvertResult(dbl, "in", units)
        shp.Cells(lengthCell).FormulaU = dblConv
        shp.Cells(lengthTextCell).FormulaU = """" & Format(dblConv, fmt) & " " & units & """"
    End If
End Sub

You can download the stencil that contains the VBA code here : bVisualUtilities.vss

Related

Filed Under: Scale, ShapeSheet Formulas, VBA, Visio

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

Comments

  1. Ian Clyde says

    May 25, 2011 at 9:15 pm

    Thanks – I will try to figure out how to use your stencil.

    Log in to Reply
  2. dejudicibus says

    December 22, 2016 at 3:04 pm

    I downloaded the stencil but I do not understand how to use it. I uploaded the stencil in Visio, but it is empty.

    Log in to Reply
    • davidjpp says

      March 10, 2017 at 7:05 am

      The code is in the VBA project inside the stencil.

      Log in to Reply
  3. dejudicibus says

    December 22, 2016 at 3:09 pm

    I get a run-time error ‘-2032464666 (86db0ce6)’ on AddUpdateMetricTriggers, at instruction
    shp.Cells(areaCell).FormulaU = dblConv

    Log in to Reply
    • davidjpp says

      March 10, 2017 at 7:44 am

      Does this still happen?

      Log in to Reply
      • Toku says

        November 30, 2017 at 9:05 am

        Hi,
        I am struggling with the same exact error.
        BR Toku

        Log in to Reply
        • davidjpp says

          February 4, 2018 at 8:33 pm

          Hmmm, maybe I should have made two rows
          xxx.FomulaU =
          I have updated the code.

          Log in to Reply
  4. Berni says

    July 15, 2021 at 4:00 pm

    We like to use Visio as a drawing program for vacuum piping design in a building based on 2D layouts. For that we use the “Connector” from one Equipment to the other. Those “Connectors” are interlinked with an external data file of Excel, whereas technical data’s are mention. After all we create a “Shape Reports” whereas in the up-popping Excel those individual “Connectors” are shown, however, the length is not correct, i.e. it only takes straight lines but when ever the “Connectors” have a angle it does not calculate this distance as well.
    Can you guide me what I am doing wrong or is the “Shape Reports” not in in the position to calculate the actual piping length? Your help would be highly appreciated.
    Thank you
    Berni

    Log in to Reply
    • David Parker says

      July 16, 2021 at 12:49 pm

      I have written a new post about the PATHLENGTH(..) function for you at Automatic Line and Segment Lengths in Visio

      Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Co-authoring and Commenting with Visio Documents
  • 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

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

  • Co-authoring and Commenting with Visio Documents
  • 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

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