johnvisiomvp

Life with Visio and other Microsoft Toys!

Archive for the ‘Excel’ Category

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

Advertisements

Written by johnvisiomvp

November 14, 2017 at 11:05 am

Posted in Excel, Shapes, Uncategorized, VBA, Visio

Tagged with , ,

Converting from PowerPoint to Visio

leave a comment »

Every so often I get requests about copying from PowerPoint to Visio and confusion about why it is not easy. The problem is that PowerPoint, like several of the other Office apps (Word, Excel and Publisher) have the ability to create Visio like drawings. These are not Visio drawings, just drawings that have Visio features (drag and drop, control handles and connected shapes). Actually, these apps have two methods for creating drawings. The classic method is to select an MSO shape from a drop down list (you can not add shapes) and the other is to use SmartArt. SmartArt is XML based and for those daring enough, they can create their own shapes.

http://msdn.microsoft.com/en-us/library/gg583880.aspx#o14ta_creatingcustomsmartartlayouts__CustomizingSmartArtGraphicsUsing

The usual question was: So,  I can manipulate the MSO shapes in PowerPoint (or Excel, or Publisher or Word) why can I not continue doing this when I copy the drawing to Visio? What is copied to Visio, is just a collection of shapes with none of the connection smarts. The only thing you can do is ungroup, not a great user experience.

At one point, I did create a Visio stencil of the MSO shapes and was able to convert the  control handles of the more popular shapes, but it turns out that though the various Office apps used the same MSO shapes, how they were implemented in each app differed.

John Marshall… Visio MVP       Visio.MVPs.org

Written by johnvisiomvp

July 9, 2012 at 5:00 pm

Posted in Excel, PowerPoint, Shapes, Visio

Tagged with , , ,