Life with Visio and other Microsoft Toys!

Posts Tagged ‘VBA

Colour My World Part II

leave a comment »

Visio does not handle rows well and lists easily in the shapesheet, you can not do basic things like sorting the rows. So I use my favourite list handling app, Excel. When I need to manage large amounts of shape data, I manage it in Excel and then use VBA to remove the old shape data and populate the shape data section from Excel. I also use Excel to handle arrays within the shape data so the various lists remain in sync.

Here is the earlier experience Colour My World

On one of my project I also had to have the shape data in sync with an xml file. With Excel, that was easy. Just add a new column for XML and populate it with a formula that combined the column headers as part of an XML tag with the correct data. It did require a bit of finesse getting an XML column into a single string, but even there I was assisted by a Word VBA macro to remove the extraneous tabs.

In the first blog I talked about handling a handful of colours. I unfortunately got carried away and started to search the net for more colours that had names. The result was that I had more than 4,700 colours. There were a number of RGB values that had several names and several names that had multiple RGB values. There was also a few lists that could stand on their own, Lego colours, HTML colours, Crayola colours, Wikipedia colours, ColorHexa and Pantone Colours. Most of the Pantone colours did not have common names and Crayola has changed the names of some the crayons over the years. I even found some VBA code that would generate a worksheet of the colour spectrum showing the RGB values at various frequencies. Some of the lists also included hyperlinks to more information. The Colours spreadsheet contains a list of all the colours, but not their hyperlinks.

In the search for lists of colours and names, sometimes I got names and RGB values, sometimes they were names and RGH hex values and even times with there was a name and sample but no values. Luckily the colour samples, when copied into a worksheet. The colour samples became the cell fill colour. So I needed a toolbox of VBA code and cell formulas to manage the list.

The aim was to get a worksheet with a column for the colour name, columns for the R,G&B values and a column for the hex of the RG&B value. I also wanted to colour the cells with the hex value as a sample of what the colour looks like.

So I used the following code to extract the RGB values from the cells fill colour.

Function getRGB(rCell As Range, Optional opt As Integer) As Long
' =getRGB(D14,1)=getRGB(D14,2) =getRGB(D14,3)
Dim C As Long
Dim R As Long
Dim G As Long
Dim B As Long
C = rCell.Interior.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256

If opt = 1 Then
getRGB = R
ElseIf opt = 2 Then
getRGB = G
ElseIf opt = 3 Then
getRGB = B
getRGB = C
End If
End Function

If you want to recreate the hex value of the RGB code, use

The following code will set the fill colour of a column based on the R, G and B values in other columns.

Public Sub ColourCells()
Dim LRow As Long
Dim rCell As Range
Dim rng As Range
Dim SH As Worksheet
Dim Threshold As Integer

Set SH = ActiveSheet
With SH
LRow = .Cells(Rows.Count, "B").End(xlUp).Row
Set rng = .Range("B2:B" & LRow)
End With
For Each rCell In rng.Cells
With rCell
.Offset(0, 3).Interior.Color = RGB(.Value, .Offset(0, 1).Value, .Offset(0, 2).Value)
End With
Next rCell
End Sub

What I noticed was that the text in the colour sample did not show up when the colour was dark. So I added another column called Obverse to indicate that with that colour, the text colour must be black or white. I did change the code in the ColourCells macro to set the value for Obverse and also apply it to the text. The results were okay. The first attempt set the value if the R,G&B values were all less than a certain threshold. Since this was done in code, I will take more time tuning the values.

One of the things I was trying to do with the list was to get the colours sorted in a pleasing manner. Initially I tried to sort by RGB, but there was a banding effect as the values of RG&B changed, especially when a value jumped from 255 to 0. I also tried to match the spectrum as can be seen in the Spectrum worksheet. It is a pleasing result, but it does not map to the named colours. I also tried mapping to the Hue with some results, but again there were banding issues. I even tried a fudge by grouping values together. Again,  there was only slight improvements. So for now I am leaving sorting alone.

I could leave the lists as a mixture of values and formulas, but I was concerned with performance from all the recalculations. So all the columns were copied to new columns  and pasted as values. 

I have asked one of the Excel MVPs to host the workbook. I will update this post when it is live.

John Marshall… Visio MVP


Written by johnvisiomvp

November 14, 2017 at 11:05 am

Posted in Excel, Shapes, Uncategorized, VBA, Visio

Tagged with , ,

Friendly Visio Stencil

with 2 comments

I am working with a stencil that contains almost 400 shapes and it is far from friendly. Normally a new stencil will show the icon for the shape and its’ name. With this project, the name is far from helpful. Undecypherable is more appropriate. If you right click the band at the top of the stencil window, you are given a choice for View of Icons and Names, Names Under ICons, Icons Only, Names Only and  Icons and Details. The last looks tempting, but there is no such cell as Details. What it is, is … Icons & Names over a prompt.
Luckily there is something in the master called prompt. Now, of course, populating that field for almost 400 masters is not trivial, or is it? Back in the days of Visio 3.0, Visio was the first non Microsoft company to fully implement VBA, including the macro recorder. So, it should just be a matter of a few lines of VBA code. It does help if the shape does contain some text that can be used. In this case, I have a Shape Data field called Description that contains the necessary text.

Public Sub EditMaster()
Dim vsoMaster As Visio.Master
Dim vsoCell As Visio.Cell
Dim vsoShapes As Visio.Shapes
Dim vsoShape As Visio.Shape

For Each vsoMaster In ActiveDocument.Masters
If vsoMaster.Shapes(1).CellExists(“Prop.Description”, 0) Then
Set vsoCell = vsoMaster.Shapes(1).Cells(“Prop.Description”)
Replace(Replace(vsoCell.ResultStr(Visio.visNone), “””, “IN”), “&”, “&”)
End If
Next vsoMaster
End Sub

A few things to note;
– The original text for the field came from XML, so there is a little playing to handle the abbreviation for inches and the ampersand. Luckily I did not have to deal with feet. 😉
– Only masters that have a shape data of Description are changed, all others are left alone.
– for those familiar to using VBA with masters, you do NOT need to wrap the code with an Open / Close that is required when modifying other fields in a master.

John Marshall… Visio MVP

Written by johnvisiomvp

October 9, 2016 at 4:52 pm

Posted in Shapes, VBA, Visio

Tagged with , ,

Open Shape Surgery for the Visio Developer

leave a comment »

One of the first gotchas for new Visio developers is the UnGroup command.

Visio shapes are simple, they can have one colour and one line type. To make a traffic light, you need to group a red circle, yellow circle and a green circle. When grouped, Visio will take a collection of shapes and create a new shape with a group section and a shape collection of the shapes selected. To  ungroup, the shapes are released from the collection and the group shape is deleted. The process is reversible, some times. Shape developers learned early on, that this new shape was like any other shape and could have other sections added to it. Shape Data, User Data, Connections, Control Handles etc. Unfortunately, when these shapes are ungrouped, these extra sections are not preserved. So knowing this why would you ever want to ungroup one of these these?  You can always subselect or use the Drawing Explorer to get access to one of the sub shapes. Welcome to one of the other gotchas, the bounding box. Sometimes you have to ungroup, and create a group with a more appropriate bounding box. You can go bigger by adding a temporary shape of the desired size, lock the group from recalculating the bounding box and delete the temporary shape, but you can not go smaller without Ungrouping. You would then create a shape of the correct size, group it and before adding the other shapes to the group, lock the group shape from recalculating. The final cleanup would be to add back the component shapes and delete the temporary shape.

Early on Graham Wideman showed me a way to remove VBA projects from a Visio solution. Deleting the VBA only got you part of the way, there was still a Project stub that made Visio think the file contained a VBA solution. His solution was to edit the XML version of the file and delete the stub.





Written by johnvisiomvp

April 17, 2016 at 4:22 pm

Posted in Shapes, VBA, Visio

Tagged with , ,