A Suite of XL Color Functions, Part 1

Posted on by Michael

RGB Color Cube
Your monitor was sold to you as being able to display more than 16 million colors. How many, exactly? 2563 or 16,777,216. One color for every combination possible of reds, greens, and blues in the range of 0 to 255 each. (Of course, unless you have 2563 pixels, you can’t see all those colors at once.) The image is of the RGB color cube, 255x255x255, with black at the origin, diagonally opposite the white corner. Excel keeps track of its colors as a long integer, the same as returned by the VBA.RGB(Red, Green, Blue) function.

RGB(1,0,0) is 1, RGB(0,1,0) is 256, and RBG(0,0,1) is 65536.
RGB(2,0,0) is 2, RGB(0,2,0) is 512, and RGB(0,0,2) is 131072.
RGB(3,3,3) is 3*1 + 3*256 + 3*65536, or 197379.

To go backwards, we can get the blue component of Excel’s colors by:

Function XL2BLU(xlclr As Long) As Long
XL2BLU = xlclr \ 65536
End Function

or INT(xlclr/65536) in a spreadsheet. The green component is doing integer division on the remainder by 256.

Function XL2GRN(xlclr As Long) As Long
XL2GRN = (xlclr – XL2BLU(xlclr) * 65536) \ 256
End Function

Red is what’s left after subtracting Blue and Green (a whole new meaning to subtractive colors):

Function XL2RED(xlclr As Long) As Long
XL2RED = xlclr - XL2BLU(xlclr) * 65536 - XL2GRN(xlclr) * 256
End Function

To go from the XL color to RGB in one line:

Function XL2RGB(xlclr As Long) As Variant
XL2RGB = Array(XL2RED(xlclr), XL2GRN(xlclr), XL2BLU(xlclr))
End Function

XL2RGB() must be array-entered across 3 cells. That version does compute XL2BLU 3 times and XL2GRN twice, but the calculations are quick ;) In even quicker form, it’s like this:

Function XL2RGB(xlclr as long) as Variant
Dim Red as Long, Green as Long, Blue as Long

Blue = xlclr\65536
Green = (xlclr - Blue*65536)\256
Red = xlclr - Blue* 65536 - Green*256

XL2RGB = Array(Red,Green,Blue)
End Function

The flip function, RGB2XL is this:

Function RGB2XL(Rng As Range) As Long
Dim Red As Long, Green As Long, Blue As Long
Red = Rng.Cells(1, 1)
Green = Rng.Cells(1, 2)
Blue = Rng.Cells(1, 3)
RGB2XL = RGB(Red, Green, Blue)
End Function

And as it must, xlclr = RGB2XL(XL2RGB(xlclr))

Up next, Part 2: XL2CMYK() and CMYK2XL() where the identity will only be “close enough.”

… mrt
©¿©¬

17 thoughts on “A Suite of XL Color Functions, Part 1”

  2. Typo: “…and RBG(0,0,1) is 65536.”
    Should be: “…and RGB(0,0,1) is 65536.”

  4. Here is a single function (variations of which I have posted in the past) that will return the requested color value. The first argument is the numerical (Long) color value and the second argument is either the quoted letter “R”, “G” or “B” for the color value desired…

    Function RGorB(RGBvalue As Long, R_G_B As String) As Variant
    RGorB = RGBvalue \ 256 ^ (InStr(1, “RGB”, R_G_B, vbTextCompare) – 1) Mod 256
    End Function

    In addition, here are some alternates for the blog article’s XL2BLU, XL2GRN and XL2RED functions…

    Function XL2RED(xlclr As Long) As Long
    XL2RED = CLng(“&H” & Right(Hex(xlclr), 2))
    End Function

    Function XL2GRN(xlclr As Long) As Long
    XL2GRN = CLng(“&H” & Mid(Hex(xlclr), 3, 2))
    End Function

    Function XL2BLU(xlclr As Long) As Long
    XL2BLU = CLng(“&H” & Left(Hex(xlclr), 2))
    End Function

  5. What about a color picker in Excel ? There’s one for Access. I need users to select a set of font and interior cell colors for their application.
    Providing them 3 DROP downs of 0 to 255, just doesn’t “hack” it.

  6. Rick –

    XL2HEX() and HEX2XL() are planned as Part 3 ;) XL has red as least significant and Hex has Red as most significant. Once the hex number is front padded to six characters, I use your functions exactly. Well, almost exactly…I lower cased the “H” ;-)

    M-

    My topic after color functions is to show how to get a list of installed fonts into a form’s combo box. Will that help? J-Walk’s site has an old-fashioned color picker that gives you your choice of 56. Still works–I used it last week. It’s on my list to figure out how to access the new color picker. J-Walk also has the method I modify to get the font list. Search his site. Once John shows the way, this is straight forward stuff. ;-0

    … mrt

  7. Color is an integer between 0 and 255 * 255 * 255. There are perfectly good arithmetic operators to get the result in a straightforward manner. {grin}

    Other points to consider. R, G, and B are integer values between 0 and 255. The appropriate data type is Byte.

    Also, I stay away from variables named xl… since Microsoft uses xl… for Excel related constants and mnemonics.

    So,

    Option Explicit

    Function RGBComponents(ByVal aColor As Long)
    Dim R As Byte, G As Byte, B As Byte
    R = aColor Mod 256
    G = (aColor \ 256) Mod 256
    B = aColor \ (256& * 256)
    RGBComponents = Array(R, G, B)
    End Function
    Function VBARGB(ByVal R As Byte, ByVal G As Byte, _
    ByVal B As Byte) As Long
    VBARGB = RGB(R, G, B)
    End Function

    The name of the last function is a convention I've used for several years. When I create a UDF to export a VBA function for use in Excel, I use the name VBA{VBA function anme}

    An alternative that also provides individual colors...

    Option Explicit

    Function RGB_R(ByVal aColor As Long) As Byte
    RGB_R = aColor Mod 256
    End Function
    Function RGB_G(ByVal aColor As Long) As Byte
    RGB_G = (aColor \ 256) Mod 256
    End Function
    Function RGB_B(ByVal aColor As Long) As Byte
    RGB_B = aColor \ (256& * 256)
    End Function
    Function RGBComponents(ByVal aColor As Long)
    RGBComponents = Array(RGB_R(aColor), RGB_G(aColor), RGB_B(aColor))
    End Function
    Function VBARGB(ByVal R As Byte, ByVal G As Byte, _
    ByVal B As Byte) As Long
    VBARGB = RGB(R, G, B)
    End Function

    To get the fill color of a cell, keeping in mind that it will not automatically recompute when the color changes.

    Function CellColor(aCell As Range)
    CellColor = RGBComponents(aCell.Interior.Color)
    End Function

    Finally, if there is a compelling need to get individual colors through a single function with the desired color passed in as a 1 character string, I would use a Select Case statement. This will also allow for error handling.

  8. Hi Tushar –

    Thank you for the powerful and insightful functions. Using MOD gives those one-liners I appreciate. I use “xlclr” actually for just the reasons you advise against: I know where it’s coming from. I’m stealing your naming tip.

    Our method of finding blue is the same, I think. Considering that it might just be ctrl-V run amok (you used it twice), is there an “&*” operation? Talking to my wife, I’d say no. Talking to you, I’d not be surprised.

    … mrt

  9. I’m sorry Rick Rothstein but it’s impossible to use LEFT RIGHT MID functions.

    With only red, you have only 2 chars…
    Mid(Hex(xlclr), 3, 2)) failed

    SVBA

  10. SVBA –

    No, Red is FF0000. Note that you can’t take the xlclr to HEX directly. The Reds are at opposite ends.

    … mrt

  11. Hi Michael,

    True, the function argument can come from an Excel cell, but it is not limited to use in an Excel cell. {grin} When I first had to work with colors, the need was for functions called from other functions/subs. The fact that they met UDF requirements was intentional but not the primary reason for the development of the functions.

    256& causes the VBA compiler to treat 256 as a long rather than an integer. Otherwise (256 * 256) will cause an overflow error! I could have been more explicit and used CLng(256) * 256, I suppose, or I could have used aColor \ 256 \ 256.

  12. Hi Tushar –

    Thanks. I get it now. I knew there was a reason I declared the colors as Long. It’s just not the one I thought it was ;-)

    … mrt

  13. Hi Michael,

    i would say, you can’t use like that:
    Image1.BackColor = VBARGB(XL2RED(ActiveCell.Interior.Color), _
    XL2GRN(ActiveCell.Interior.Color), _
    XL2BLU(ActiveCell.Interior.Color))

    Because with black -> &h0 -> 1char
    red -> &HFF -> 2 chars
    …

  14. Hi SVBA –

    Please see Part 3, just posted. You “know” the ActiveCell.Interior.Color. It’s a XL long integer. If you use my functions, why do you want to break it down into its red, green, and blue parts, (all decimal values) feed it to RGB() and get it back as the XL long integer you started with? If you use Rick’s functions, Rick assumed you padded the hexclr with leading zeros to get it six characters long. Black is &h000000. Still, for your example, you’re taking something to hex, breaking it down, and putting it back together. All you need to say is Image1.BackColor = ActiveCell.Interior.Color

    Red, RGB(255,0,0) = 255, and 255 is FF (base 16), that true, but that’s not the hexadecimal color red. That’s XL’s red expressed in hex. These are two different numbering schemes. If you want to take red to the web, it’s FF0000. If you just take FF to the web, you get 0000FF and that’s blue.

    If Part 3 doesn’t clear this up, let’s continue the conversation over there.

    … mrt

  15. This is great, but what about a color picker for Excel ?
    3 RGB combo boxes just doesn’t “hack it” !

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.