# A Suite of XL Color Functions, Part 3

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

## 3 thoughts on “A Suite of XL Color Functions, Part 3”

1. Steve says:

What’s all this color stuff good for?

2. Michael says:

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

3. Steve says:

Thanks for the application examples.

Reminds me of Calculus. Without examples of application, it’s just math.

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