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 25510. In HEX, pure red–FF000–is 1671168010. 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, 25510, 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():
| ?xl2hex(vbwhite,"#") | ?xl2hex(vbred,"#") | ?xl2hex(vbgreen,"#") | ?xl2hex(vbblue,"#") |
| #FFFFFF | #FF0000 | #00FF00 | #0000FF |
| ?xl2hex(vbyellow,"#") | ?xl2hex(vbcyan,"#") | ?xl2hex(vbmagenta,"#") | ?xl2hex(vbblack,"#") |
| #FFFF00 | #00FFFF | #FF00FF | #000000 |
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
Temp = CheckCSS(hexclr)
Red = CLng("&h" & Left$(Temp, 2))
Green = CLng("&h" & Mid$(Temp, 3, 2))
Blue = CLng("&h" & Right$(Temp, 2))
HEX2XL = RGB(Red, Green, Blue)
End Function
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
Temp = CheckCSS(hexclr)
HEX2RED = CLng("&h" & Left$(Temp, 2))
End Function
Function HEX2GRN(hexclr As String) As Long
Dim i As Long
Dim Temp As String
Temp = CheckCSS(hexclr)
HEX2GRN = CLng("&h" & Mid$(Temp, 3, 2))
End Function
Function HEX2BLU(hexclr As String) As Long
Dim i As Long
Dim Temp As String
Temp = CheckCSS(hexclr)
HEX2BLU = CLng("&h" & Right$(Temp, 2))
End Function
Function HEX2RGB(hexclr As String) As Variant
Dim i As Long, Red As Long, Green As Long, Blue As Long
Dim Temp As String
Temp = CheckCSS(hexclr)
Red = CLng("&h" & Left$(Temp, 2))
Green = CLng("&h" & Mid$(Temp, 3, 2))
Blue = CLng("&h" & Right$(Temp, 2))
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
©¿©¬
What’s all this color stuff good for?
Hi Steve –
Well, 2 small examples…that little table at the bottom of Part 2 exists as four cells in a spreadsheet, and the 2 central colors are the indicated XL colors. Their hex colors, to get them here on the web, are #ABD7AA and #ABD6A9 (XL told me so.) A macro (available from here) took the 4 cells and made the table in CSS, and I pasted it into the DDoE where I wanted it to go. The table at the top here is in the same spreadsheet. I ran the macro again. To color it would only have meant to color the cells. Excel can do the rest.
I post here, Wikipedia, and Blackboard. All my tabular web work uses the functions of parts 1 and 3. Part 2 comes from an earlier life. If you’ve ever prepared a government proposal, you’ll understand the sweat created over pre-press preparations. Whole sections of industry delve in it, but most and probably you, will have no interest.
Admittedly, I only go from XL to the web. I created the flip functions for completeness. But if there is ever a color you want to copy from the web, open the source, copy the hex code, paste it in, and you’ve got some tools.
If you check out http://www.dailydoseofexcel.com/archives/2012/02/15/making-a-wiki-table/ there are links to tables made with these functions.
… mrt
Thanks for the application examples.
Reminds me of Calculus. Without examples of application, it’s just math.