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
Else
getRGB = C
End If
End Function
If you want to recreate the hex value of the RGB code, use
="#"&RIGHT("00"&DEC2HEX(B2),2)&RIGHT("00"&DEC2HEX(C2),2)&RIGHT("00"&DEC2HEX(D2),2)
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.
Enjoy.
John Marshall… Visio MVP Visio.MVPs.org
john Visio MVP 