Excel Laundry Tip for Visio

After exploring the Visio Format codes I found the documentation lacking so I decided to create a Format Code Sample shape. It is basically a rectangle with a few hundred User cells that show examples of Format Codes. Most of the codes are obvious, but some require explanation. In addition to the shape, I will provide some documents that explains the rules and fills in the gaps in the Visio documentation in a future post.

Visio has predefined Formats that are mention in a note here

https://docs.microsoft.com/en-us/office/client-developer/visio/about-format-pictures

but there is no explanation of the enumerations of these predefined formats. Luckily, Ben, a former MVP friend pointed me to

https://docs.microsoft.com/en-us/office/vba/api/visio.visfieldformats.

Unfortunately, there is no definition of the enumerations or how to decypher the names. So, I ended up with a number of tables and used my favourite Visio companion for data manipulation, Excel to consolidate the tables. I created an Excel sheet with a row for each sample and ended up with more than 300 rows.

Which behind the scenes looks like (these are other rows.)

The rows did contain other columns of information like name, description and VBA code to create the examples.

To be able to import into Visio, I need a column with a VBA statement to create the User cells.

and a VBA statement to populate the cell.

The VBA2 column between VBA1 and VBA3 that only contains a colon, the VBA separator character when you have more than one statement on the line. So, you can select the rows in the VBA1, VBA2 and VBA3 columns and do a single cut and paste.

I did use Word to handle some of the tables, but Word corrupted some of the tables by insisting on capitalization. Not good when the Format codes are case sensitive. Of course I did have to tell Excel to behave because it wanted to treat some of the text as commands. I just wanted Excel to treat things as just text. I did not want it playing in with my “Date”.

The cells of VBA code are added to the following VBA routine,

Sub LoadFormats()
Dim vsoShape As Visio.Shape

Set vsoShape = ActivePage.DrawRectangle(1, 1, 2, 2)
If Not (vsoShape.SectionExists(visSectionUser, False)) Then vsoShape.AddSection (visSectionUser)

' User definition statements - to be replaced by the Excel generated code.
vsoShape.AddNamedRow visSectionUser,"Currency0", visTagDefault
vsoShape.Cells("User.Currency0").FormulaU = "Format( 2.05,""$###,###.00"")

End Sub

The row names indicate the Format group, Format Code and a unique identifier, some of the other columns are. The rows below are from the currency Formats from Create Shape Data.

So, why are the cells green? Initially, all the rows were red and as I converted the statements into proper Excel formulas referencing other columns and Char(34) (double quotes) and tested the formulas I changed the working rows to green. Far easier to spot what I need to do. So rather than a jumble of rows, I could sort by colour and isolate the rows that need work. As with laundry, sorting by colour is useful.

Now back to working on my Format Code Sample shape and the surrounding documents. A Word document describing the codes and grouping. Including an explanation of the naming conventions. An Excel file that is the consolidation of the tables with columns of VBA code that can be plugged into a VBA routine to create the shape…. and the Visio diagram containing the Visio shape and the code needed to recreate the shape. The Excel table can be updated and with the generated VBA code, the routine in the Visio diagram can be run and a new updated shape created.

The files will be eventually be available as a download from my download page. I will blog when it is ready.

John… Visio MVP in x-aisle
JohnVisioMVP.ca