VBA to return RGB color of text

Question

I have been able to return the RGB colour of text in a word document where the color has been picked using custom colours, but not when the standard colours are used. I initially used the below code:

Function GetRGBTest(Colour As Long) As String

GetRGBTest = "rgb(" & (Colour Mod 256) & "," & ((Colour \ 256) Mod 256) & "," & ((Colour \ 256 \ 256) Mod 256) & ")"

End Function

Sub TestColour()

MsgBox GetRGBTest(Selection.Font.Color)

End Sub

When using a standard colour, selection.font.color returns a negative value and the RGB value is incorrect.

I have tried editing it to have the following (Where Dict is a list of colorindex and respective rgb vals):

Function GetRGBTest(Colour As Long, colInd As Integer) As String

If Colour > 0 Then
    GetRGBTest = "rgb(" & (Colour Mod 256) & "," & ((Colour \ 256) Mod 256) & "," & ((Colour \ 256 \ 256) Mod 256) & ")"
Else
    colInd = LTrim(Str(colInd))
    GetRGBTest = Dict.Item(colInd)
End If
End Function

Sub TestColour()

MsgBox GetRGBTest(Selection.Font.Color, Selection.Font.ColorIndex)

End Sub

Although I think that the ColorIndex returns a value that doesnt relate to the standard colours.

Does anyone have an idea how to transform these values into RGB vals?

Answer 1

Thanks to AndASM for this awesome description of this particular problem. I've been writing a Perl program that was reading a word document using the Win32::OLE library. My goal was to output the document in a different format and found myself deeply troubled when I came across these indexed values. After being guided in the right direction, I ended up building a hash look up in Perl for these indexes. I stripped the hex string of everything else that seemed useless (like the &H00 byte) and constructed a search string that returned RGB values when fed through this hash map:

my %indexedColors = (
    "ff0000" => [0,0,0],
    "dcffff" => [255,255,255],
    "dcf2ff" => [242,242,242],
    "dcd9ff" => [217,217,217],
    "dcbfff" => [191,191,191],
    "dca6ff" => [166,166,166],
    "dc80ff" => [128,128,128],
    "ddffff" => [0,0,0],
    "ddff80" => [127,127,127],
    "ddffa6" => [89,89,89],
    "ddffbf" => [64,64,64],
    "ddffd9" => [38,38,38],
    "ddfff2" => [13,13,13],
    "deffff" => [238,236,225],
    "dee6ff" => [221,217,195],
    "debfff" => [196,188,150],
    "de80ff" => [148,138,84],
    "de40ff" => [74,68,42],
    "de1aff" => [29,27,17],
    "dfffff" => [31,73,125],
    "dfff33" => [198,217,241],
    "dfff66" => [141,179,226],
    "dfff99" => [84,141,212],
    "dfbfff" => [23,54,93],
    "df80ff" => [15,36,62],
    "d4ffff" => [79,129,189],
    "d4ff33" => [219,229,241],
    "d4ff66" => [184,204,228],
    "d4ff99" => [149,179,215],
    "d4bfff" => [54,95,145],
    "d480ff" => [36,64,97],
    "d5ffff" => [192,80,77],
    "d5ff33" => [242,219,219],
    "d5ff66" => [229,184,183],
    "d5ff99" => [217,149,148],
    "d5bfff" => [148,54,52],
    "d580ff" => [99,36,35],
    "d6ffff" => [155,187,89],
    "d6ff33" => [221,217,195],
    "d6ff66" => [214,227,188],
    "d6ff99" => [194,214,155],
    "d6bfff" => [118,146,60],
    "d680ff" => [79,98,40],
    "d7ffff" => [128,100,162],
    "d7ff33" => [229,223,236],
    "d7ff66" => [204,192,217],
    "d7ff99" => [178,161,199],
    "d7bfff" => [95,73,122],
    "d780ff" => [64,49,82],
    "d8ffff" => [75,172,198],
    "d8ff33" => [218,238,243],
    "d8ff66" => [182,221,232],
    "d8ff99" => [146,205,220],
    "d8bfff" => [49,132,155],
    "d880ff" => [33,88,104],
    "d9ffff" => [247,150,70],
    "d9ff33" => [253,233,217],
    "d9ff66" => [251,212,180],
    "d9ff99" => [250,191,143],
    "d9bfff" => [227,108,10],
    "d980ff" => [152,72,6],
);

Hopefully no poor soul ever has to delve into the depths of Microsoft's backwards compatibility but just in case.

Answer 2

I haven't been able to quickly Google up a reference, but if I recall correctly there are three possible data formats that Word will return a font colour in.

RGB Format

If the high byte is &H00 then the remaining three bytes represent red, green, and blue. This is the format you are familiar with and already handling.

Automatic Colour

If the value is &HFF000000 also known as -16777216 then the colour is set to automatic, and is usually black. Otherwise it assumes the default colour for the document.

Mysterious third negative format

If the high nibble of the high byte is &HD, that is to say if the first digit of the hexidecimal representation of the number is D, then it uses the Word colour scheme format.

For example, you might get &D500FFFF The second nibble, &H5 in our example, matches up to a value in the enumeration WdThemeColorIndex. If you create a translation table translating from this enumeration to the MsoThemeColorSchemeIndex enumeration, you can then look up the basic colours in the document's ActiveDocument.DocumentTheme.ThemeColorScheme collection. Why are there are two enumerations with different index numbers for the same thing you ask? Good question! Moving on...

This is not the end of the story however! There are those last three remaining bytes to worry about! The next one, the low byte of the high word, I think it's easy. I believe it's always &H00. If you run into a different value for that byte... well, best of luck to you.

The last two bytes represent percentages that darken or lighten the value (respectively). Where &FF or 255 means no change and &h00 means 100%. The same as in the colour picker where you see things like "Accent 2, Lighter 60%". That by the way would be &HD500FF66, 5 being the index for Accent 2, &H66 being 60% in the lighter byte.

So here is a function that does not account for the lighter and darker values:

Public Function GetBasicRGB(color As Long) As String
  Dim colorIndexLookup
  Dim colorIndex As Integer
  Dim finalColor As Long
  colorIndexLookup = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 2, 1, 4, 3)
  colorIndex = colorIndexLookup( _
      ((color And &HF000000) / &H1000000) _
      + LBound(colorIndexLookup))
  finalColor = ActiveDocument.DocumentTheme.ThemeColorScheme(colorIndex)
  GetBasicRGB = "rgb(" & (finalColor And &HFF) & "," & _
      (finalColor / &H100 And &HFF) & "," & _
      ((finalColor And &HFF0000) / &H10000) & ")"
End Function

To account for the lighter and darker I believe you have to convert the RGB value to an HSL value then modify the L component by the % lighter or darker. Finally convert back to RGB. But I don't care to figure that out right now.