如何在Excel/VBA中获得RGB颜色对应的十六进制值?

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

我试图在VBA代码中设置一个颜色的公共属性。通常情况下,我可以使用:

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

然而，由于RGB功能，没有办法公开它。我使用在线转换器将RGB值转换为十六进制，得到了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.

结果是完全不同的颜色。我想这可能是RGBA颜色，我试过B7DEE8__，把颜色画得很近(最后一个数字是B8)，但是我想知道如何找到正确的值。

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.

注意:我并不需要代码将它转换为十六进制，我只需要知道如何找到它，因为我在Excel表格上使用了五种常量颜色，我想设置它们。

5 个解决方案

#1

You'll have to reverse the bytes into order

您将不得不将字节反转成顺序

BLUE = &HE8DEB7

to get the correct color value.

得到正确的颜色值。

#2

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

出现明显反转的原因是RGB()函数实际上创建了一个BGR值。

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.

注意，“full”字节(255或FF)和“half full”字节(128或80)在每个结果中都位于相反的两边。这就是为什么你需要以相反的顺序指定十六进制常数与你期望得到相同值的顺序。

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:

同样，不需要使用在线转换器。函数的Hex()提供给它的数字的十六进制值，Int将取十六进制格式的字符串并返回十进制值:

? 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:

因此，要使用这些信息来创建十六进制常量，只需在当前窗口中运行RGB()和十六进制()语句(输入Ctrl+G打开和关闭它)，然后使用生成的十六进制值作为常量。如果这个值小于6位，你可以用0来填充它，但这在技术上是不必要的:

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

then copy that last line into your code.

然后将最后一行复制到代码中。

#3

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)

注意，excel RGB值是反向的(BGR)

#4

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

好的，下面将取Excel 2010中的一个单元格的颜色，并提供一个有效的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

#5

-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

#1