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.
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 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!