10

I'm trying to set a public const of a color in my VBA code. Normally, I can use:

Dim BLUE As Long
BLUE = RGB(183, 222, 232)

However, there's no way to public const that because of the RGB function. I converted this RGB value to Hex using an online converter, and I got back B7DEE8

Using:

BLUE = &HB7DEE8

results in a completely different color. I think this may actually be an RGBA color, and I've tried B7DEE8__ and got the color pretty close (with the last digit being B8), but I'd like to know how to actually find the correct value.

Note: I don't really need code to convert this to hex, I just need to know how to find it, because I have five constant colors I use on my Excel sheet, and I'd like to set them up.

Brandon
  • 779
  • 5
  • 9
  • 28

7 Answers7

12

You'll have to reverse the bytes into order

BLUE = &HE8DEB7

to get the correct color value.

Howard
  • 38,639
  • 9
  • 64
  • 83
  • 1
    @Brandon I don't know if there can by any answer to this question. Nevertheless it's at least documented e.g. [here](http://msdn.microsoft.com/en-us/library/dd952824%28v=office.12%29.aspx). – Howard May 14 '11 at 17:17
12

The reason for the apparent reversal is that the RGB() function actually creates a BGR value.

More specifically, the red byte is the low order byte and the blue byte is the high order byte (or third of four at least).

Try this example in the Immediate window:

x = RGB(255, 0, 128) ' full red, half blue
? hex(x)
8000FF

x = RGB(128, 0, 255) ' half red, full blue
? hex(x)
FF0080

Note that the "full" byte (255 or FF) and the "half-full" byte (128 or 80) end up on the opposite sides in each result. That's why you need to specify the hex constant in the reverse order from what you'd expect to get the same value.

Also, no need to use an online converter. The Hex() function provides the hex value of the number given to it, and Int will take a string in hex format and return the decimal value: 

? Int("&hff0000") 
 16711680

Update:

So to use this information to create your hex constants, you just run your RGB() and Hex() statements in the Immediate window as above (type Ctrl+G to open and close it), then use the resulting Hex value as your constant. If the value is less than 6 digits long, you can pad it on the left with zeros, but that's technically not necessary:

x = RGB(183, 222, 232)
? "Public Const MyBlue = &h" & hex(x)
Public Const MyBlue = &hE8DEB7

then copy that last line into your code.

Todd
  • 5,999
  • 2
  • 21
  • 21
  • Unfortunately, since I'm trying to set the colors as public constants, I can't use Hex() or RGB() to define them, as they are not constants. But very useful information, thank you! – Brandon May 15 '11 at 14:21
5

OK, the following will take the color of a cell in Excel 2010 and provide a valid Hexcode:

Public Function getHexCol(a As Range)

' In excel type in for example getHexCol(A1) to get the hexcode of the color on     A1.
Dim strColour As String
Dim hexColour As String
Dim nColour As Long
Dim nR As Long, nB As Long, nG As Long

strColour = a.Interior.Color
If Len(strColour) = 0 Then Exit Function

nColour = Val(strColour) ' convert string to decimal number
hexColour = Hex(nColour) ' convert decimal number to hex string
While Len(hexColour) < 6 ' pad on left to 6 hex digits
hexColour = "0" & hexColour
Wend

nB = CLng("&H" & Mid(hexColour, 1, 2))
nG = CLng("&H" & Mid(hexColour, 3, 2))
nR = CLng("&H" & Mid(hexColour, 5, 2))

getHexCol = Hex(RGB(nB, nG, nR))
End Function
Amit Kohli
  • 2,860
  • 2
  • 24
  • 44
2
Function GetRGB(ByVal cell As Range) As String

Dim R As String, G As String
Dim b As String, hexColor As String
hexCode = Hex(cell.Interior.Color)

'Note the order excel uses for hex is BGR.
b = Val("&H" & Mid(hexCode, 1, 2))
G = Val("&H" & Mid(hexCode, 3, 2))
R = Val("&H" & Mid(hexCode, 5, 2))

GetRGB = R & ":" & G & ":" & b
End Function

note that excel RGB values are backwards (BGR)

John Smith
  • 7,243
  • 6
  • 49
  • 61
  • Just tried this out... the `hex(interior.color)` doesn't get you there... Try this for example. The excel color `10192433` corresponds to RGB '49,134,155' which SHOULD correspond to hex `31869b`.... but hex(10192433) = `9BD7C4`. – Amit Kohli Jun 10 '15 at 14:07
  • I'm not encountering this problem. I tried to reproduce what you said, but even using `MsgBox (Hex(10192433))` correctly shows 9B8631 to me... (I'm on Excel 2013) – SourceSeeker May 27 '16 at 01:59
  • @SourceSeeker yes, this color (10192433) is R-49 G-134 B-155 on the color chart, ie. R-31 G- 86 B- 9b in hex (31 86 9b together, a tourquoise like colour, because XL displays RGB values actually reversed, in BGR (possibly due to endianness)); but, the hex value of 10192433 is actually 9b 86 31. notice how the bits are flipped- it's in BGR instead of RGB. if XL displayed it as RGB, the decimal number would actually be 3245723 instead of 10192433. – John Smith May 27 '16 at 17:08
  • @iliketocode I already noticed the viced versa RGB values returned by Excel function `hex(...)`, but I still do not understand where Amit Kohli's hex value 9BD7C4 comes from: "but hex(10192433) = `9BD7C4`". `9BD7C4 = 9B D7 C4 = 155 215 196 = 12900251 (XL: VALUE OF RGB(155,215,196)) = light tourquoise = C4D79B (XL: VALUE OF HEX(12900251) = BGR)` >LINE BREAK< `C4D79B = C4 D7 9B = 196 215 155 = 10213316 (XL: VALUE OF RGB(196,215,155)) = light green = 9BD7C4 (XL: VALUE OF HEX(10213316) = BGR)` – SourceSeeker May 28 '16 at 19:17
0

Here is another function that also works in MS Access and accounts for the reverse RGB order:

Function VBA_RGB_To_HEX(iRed As Integer, iGreen As Integer, iBlue As Integer) As String
    Dim sHex As String
    sHex = "#" & VBA.Right$("00" & VBA.Hex(iBlue), 2) & VBA.Right$("00" & VBA.Hex(iGreen), 2) & VBA.Right$("00" & VBA.Hex(iRed), 2)
    VBA_RGB_To_HEX = sHex
End Function
RBILLC
  • 170
  • 2
  • 6
0

Sorry I'm late to the thread.

ActiveX elements like buttons on worksheets can have their background and font colors changed. They use a particular HEX format similar to &H00B5752F&. Its based on Blue, Green, Red order instead of the Red, Green, Blue order of the RGB type

If you know your RGB values and want to convert to ActiveX color HEX format, you can use the following procedure.

Sub RGB_to_ActiveX_HEX_Color()
'       This macro allows the user to input numeric RGB values
'       to get the HEX format for Activex element Hex Colors
'
'       Result will be printed in the immediate window
'
'       Example: RGB(47,117,181)  =  &H00B5752F&


Dim mySplitArr
Dim myRGB_numbers As Variant
Dim newStr As String
Dim commaTally As Long
        
        myRGB_numbers = InputBox("Enter RGB values separated by commas ", "RGB to HEX")
        
        On Error GoTo error_Handler_main
        mySplitArr = Split(myRGB_numbers, Chr(44))
        
        For i = LBound(mySplitArr) To UBound(mySplitArr)
            If Not IsNumeric(mySplitArr(i)) Or mySplitArr(i) < 0 Or mySplitArr(i) > 255 Then GoTo error_Handler_main
        Next i
        
        If UBound(mySplitArr) < 2 Then GoTo error_Handler_main
        
        Debug.Print "RGB(" & mySplitArr(0) & ", " & mySplitArr(1) & ", " & mySplitArr(2) & ")  =  " & "&H00" & VBA.Right$("00" & VBA.Hex(mySplitArr(2)), 2) & VBA.Right$("00" & VBA.Hex(mySplitArr(1)), 2) & VBA.Right$("00" & VBA.Hex(mySplitArr(0)), 2) & "&"

Exit Sub
error_Handler_main:
    MsgBox "There was an error in your input" & vbNewLine & vbNewLine & _
            "Be sure to separate RGB values with commas." & vbNewLine & vbNewLine & _
            "example:   123,255,0" & vbNewLine & vbNewLine & _
            "Also values must be between 0 and 255 for each color"
    Exit Sub
    

End Sub
John Muggins
  • 1,198
  • 1
  • 6
  • 12
-1

I tested this code, cant realy follow Howard's answer

Dim rd, gr, bl As Integer
rd = 183
gr = 222
bl = 232
BLUE = RGB(rd, gr, bl)
hexclr = Format(CStr(Hex(rd)), "00") +
Format(CStr(Hex(gr)), "00") + 
Format(CStr(Hex(bl)), "00")
MsgBox hexclr 'B7DEE8
Arnoud Kooi
  • 1,588
  • 4
  • 17
  • 25
  • Just add another line: `MsgBox Hex(BLUE) 'E8DEB7` and you'll see that the color bytes are reversed. – Howard May 14 '11 at 17:20