HEXADECIMAL and XL color values are equivalent systems for representing RGB colors, but they start at different ends. In XL, pure red–RGB(255,0,0)–is 255₁₀. In HEX, pure red–FF000–is 16711680₁₀. XL has red as the least significant digits, HEX has it as the most significant digits. But we’re using computers, so this version of endianness is a manageable problem.

To go from XL representation to web-friendly HEX, turn the XL color to HEX, pad it with leading zeroes, and then swap the end pairs. Green stays in the middle. Red, 255₁₀, becomes FF, then 0000FF, and then FF0000. This is the XL2HEX() function.

Function XL2HEX(xlclr As Long, Optional Prefix As String) As String
Dim Temp As String
Temp = Hex(xlclr)
If Len(Temp) < 6 Then Temp = String(6 - Len(Temp), "0") & Temp XL2HEX = Prefix & Right$(Temp, 2) & Mid$(Temp, 3, 2) & Left$(Temp, 2) End Function
It includes an optional prefix. Usual ones are # for web colors, and &H or &h to indicate a hexadecimal number. There are many more. You can pass the VB color constants to XL2HEX():

The flip function needs to strip off the prefix, make sure it's six characters long, read the red, green, and blue, and then create the XL color. Since CSS allows #ABC to represent #AABBCC we'll special case it.

Function HEX2XL(hexclr As String) As Long
Dim i As Long
Dim Red As Long, Green As Long, Blue As Long
Dim Temp As String

Function CheckCSS(hexclr As String) As String
Dim Temp As String, i As Long
If Len(hexclr) = 4 And Left$(hexclr, 1) = "#" Then
For i = 2 To 4
Temp = Temp & Mid$(hexclr, i, 1) & Mid$(hexclr, i, 1)
Next i
Else
For i = 1 To Len(hexclr)
If Mid$(hexclr, i, 1) Like "[A-Fa-f0-9]" Then Temp = Temp & Mid$(hexclr, i, 1)
Next i
If Len(Temp) < 6 Then Temp = String(6 - Len(Temp), "0") & Temp End If CheckCSS = Temp End Function
The CLng() conversion turns hex into longs as part of its business. The HEX-to-color functions are more of the same.

Function HEX2RED(hexclr As String) As Long
Dim i As Long
Dim Temp As String

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

The HEX2RGB() function is array-entered. The function I use the most is XL2HEX(). I pass it cell or font color properties, such as FontColor = XL2HEX(.Color)

Thus ends the XL Color Functions. The picture at the top? It's hexclr'd.

… mrt
©¿©¬

Your monitor was sold to you as being able to display more than 16 million colors. How many, exactly? 256³ 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 256³ 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

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
©¿©¬