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 for more information about the the Error property, and 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.


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.


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:


My macro printed out the following table:


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
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
End Sub

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


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

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

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another 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

Smart graphics for visual people

%d bloggers like this: