• 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
    • 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
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • VRA makes the case for Prudential
    • 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➡
      • 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
    • Visio blog on Orbus website
    • 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

ShapeSheet Formulas

Published on August 28, 2012 by David Parker

Checking a Visio Shape for Cell Errors

I have been analysing a client’s Visio shapes recently to find out how to increase their performance. These shapes have numerous cells with ShapeSheet functions that reference other cells in the same shape, in parent shapes, in the page and in the document. I suspected that there may be some formulas producing erroneous results, so I wanted to write a macro to check if there are any cell formula evaluation errors in the shape, or its sub-shapes.

A Visio Cell object has an Error property that returns the error code generated by the last evaluation of the result.  See http://msdn.microsoft.com/en-us/library/office/ff767327.aspx for more information about the the Error property, and http://msdn.microsoft.com/en-us/library/office/ff766442.aspx for the VisCellError constants.

In order to test a macro that can check a shape for errors, I fist needed to generate examples of each possible error in a shape. So, I created a User-defined Cells section with seven rows – one for each possible VisCellError code value.

image

I was able to generate errors in all rows, except for the VisErrorName row. No matter how hard I tried, I could not get Visio to generate this error. I am fairly certain that I could generate errors (accidently) before Visio 2010, but I think that Microsoft have made this error much rarer because of the way they substitute last known values when a referenced cell or row is deleted.

image

So, now that I had a shape with errors, I then duplicated it, and made the duplicate copy part of a grouped shape. This way I could test my function, CheckForErrors(), which simply iterates through all of the selected shapes, and their sub-shapes, and prints out any cells with errors into the Immediate Window:

image

My macro printed out the following table:

image

So, I can now check any shape to see if it has any cell errors within it.

The VBA code that produced the listing is :

Public Sub CheckForErrors()
Dim shp As Visio.Shape
    Debug.Print Now()
    Debug.Print "Parent.Name", "Shape.Name", "Section", "Row", "Column", "Name", "Error", "Formula"
    For Each shp In Visio.ActiveWindow.Selection
        CheckShapeForErrors shp
    Next
End Sub

Private Sub CheckShapeForErrors(ByVal shp As Visio.Shape)
Dim shpSub As Visio.Shape
Dim iSect As Integer
Dim iCol As Integer
Dim iRow As Integer
Dim cel As Visio.Cell
    For iSect = visSectionFirst + 1 To Visio.visSectionLast
        For iRow = 0 To shp.RowCount(iSect) - 1
            For iCol = 0 To shp.RowsCellCount(iSect, iRow) - 1
                Set cel = shp.CellsSRC(iSect, iRow, iCol)
                If cel.Error <> VisCellError.visErrorSuccess Then
                    Debug.Print cel.Shape.Parent.Name, cel.Shape.Name, iSect, iRow, iCol, cel.Name, cel.Error, cel.Formula
                End If
            Next iCol
        Next iRow
    Next iSect

    For Each shpSub In shp.Shapes
        CheckShapeForErrors shpSub
    Next
End Sub

I would be interested to find out of anyone who can create a VisErrorName for me!

Filed Under: ShapeSheet Formulas, Visio, Visio 2010, Visio 2013 Tagged With: ShapeSheet Functions

Published on May 8, 2012 by David Parker

Creating a Custom Org Chart Template with Extra Properties

A Visio user recently asked how to add a salary amount for an Org Chart person automatically, based on their selected Level. I thought it was a bit long to answer in the forum, so I have written my answer below. It demonstrates how you can have your own custom template that works with the Organization Chart add-on in Visio.

My approach to creating a custom template that still uses the Organization Chart Add-On is to create a new drawing from the built-in template, and then to add extra properties to it.

First, select Show Document Stencil to view all of the local copies of the built-in master shapes. If they are not there, then simply drag and drop them from the built-in stencil on to the first page, and then delete the shapes on the page. This will not delete the local copies of the masters.

image
[Read more…] about Creating a Custom Org Chart Template with Extra Properties

Filed Under: Org Charts, Shape Reports, ShapeSheet Formulas, VBA, Visio, Visio 2010 Tagged With: Org Chart

Published on April 26, 2012 by David Parker

Visio Date Format Pictures Affect Data Entry via the Calendar Popup

I have been creating roadmaps in Visio lately, and the client is only concerned with the months and years, so I thought it would be sensible to use a Date Format Picture to display only the month and year in the Shape Data window. What I did not expect is that the mere existence of this Date Format Picture would alter the actual date stored in the cell. In this blog, I layout my concerns about the Calendar popup that is provided for Date type Shape Data rows.

In the following example, I have created a shape, coloured pale orange below, and added 25 Date type Shape Data rows. I have not applied any format to Property1, but then I have applied each of the 17 suggested Date Format Patterns provided on a drop-down list in the Define Shape Data dialog. Finally, I added a further 7 patterns manually. I then selected Today as the value for every single Shape Data row:

image
[Read more…] about Visio Date Format Pictures Affect Data Entry via the Calendar Popup

Filed Under: ShapeSheet Formulas, Visio Tagged With: Dates, Visio 2007, Visio 2010

Published on April 22, 2012 by David Parker

Adding the Sixth Legend Icon in Visio 2010

In my previous two articles, I described how to have 6 icons in a Data Graphic Icon Set, and provided some VBA code to make your own icons sets from existing Visio shapes or images. One thing that I overlooked was the Insert Legend command in Visio 2010, because it does not add the sixth icon automatically, so this blog describes how to add it. As a side effect, the code also enables the legend labels of icon and CBV (Color By Value) items to be edited.

image



When you use the Insert Legend command, it examines the Data Graphics used on the active page, to create legends for each Icon Set or Color By Value settings that it finds. Closer examination of the legend shapes, shows that an instance of the Icon Set master shape is added for each of the icons found.

[Read more…] about Adding the Sixth Legend Icon in Visio 2010

Filed Under: Data Graphics, Shape Data, ShapeSheet Formulas, VBA, Visio 2010 Tagged With: Data Graphics, Icon Sets, Legend

Published on April 11, 2012 by David Parker

How to have six icons in a Visio Data Graphic Icon Set

I was recently asked to represent the values of Key Performance Indicators (KPIs) in Visio. Great, I thought, this is just what Visio’s Data Graphic Icon Sets are for! Only trouble was, that there were six possible values for the KPI, but all the example Icon Sets have a maximum of five icons in them. So, I had to come up with a way of displaying six icons …

In the following example, I have defined a Shape Data row with a Fixed List type that can have a value from the list “3;2;1;-1;-2;-3”

image

I applied the Faces Icon Set, and set icons for each value, except for –1:

[Read more…] about How to have six icons in a Visio Data Graphic Icon Set

Filed Under: Data Graphics, Shape Data, ShapeSheet Formulas, Visio, Visio 2010 Tagged With: Data Graphics, Icon Sets

Published on March 21, 2011 by David Parker

Which field is that cow in?

I reader of one of my earlier posts, Copy Data from one Shape to Another ( see http://davidjpp.wordpress.com/2009/02/02/copying-data-from-one-shape-to-another/ ) asked how a cow can inherit the field number of from the field that it in. I think that this is just an example of how you can automatically update any Shape Data value from an underlying container, but I will use a cow in a field example anyway. This example uses VBA code, but should be good to use in any version of Visio since Visio 2000.

[Read more…] about Which field is that cow in?

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

  • « Go to Previous Page
  • Go to page 1
  • Interim pages omitted …
  • Go to page 7
  • Go to page 8
  • Go to page 9
  • Go to page 10
  • Go to page 11
  • Go to page 12
  • Go to Next Page »

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Visio 2010 MVP Session videos reprise
  • Visio ShapeSheet Functions G-K
  • Two Quote or Not Two Quote …
  • Visio in Organizations
  • Viewing Visio Document Changes in Git

Categories

Tags

Accessibility Add-Ins Connectors Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript Layers Legend Link Data to Shapes MSIgnite Office365 Org Chart PowerApps PowerBI PowerQuery Processes Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint SharePoint 2013 SQL Teams Themes Validation VBA Video Visio Visio 2007 Visio 2013 Visio for the Web Visio Online Visio Pro for Office365 Visio Services Visio Viewer Web 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

  • LinkedIn
  • Twitter

Search this website

Recent posts

  • Visio 2010 MVP Session videos reprise
  • Visio ShapeSheet Functions G-K
  • Two Quote or Not Two Quote …
  • Visio in Organizations
  • Viewing Visio Document Changes in Git

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