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

发布于 2024-11-07 06:04:54 字数 421 浏览 0 评论 0原文

我正在尝试在我的 VBA 代码中设置颜色的公共常量。通常，我可以使用：

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

但是，由于 RGB 函数，没有办法 public const that 。我使用在线转换器将此 RGB 值转换为十六进制，然后我得到了 B7DEE8

使用：

BLUE = &HB7DEE8

结果完全不同的颜色。我认为这实际上可能是 RGBA 颜色，我尝试过 B7DEE8__ 并得到了非常接近的颜色（最后一位数字是 B8），但我想知道如何实际找到正确的值。

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

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情独悲 2024-11-14 06:04:54

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

更具体地说，红色字节是低位字节，蓝色字节是高位字节（或至少四个字节中的第三个）。

在“立即”窗口中尝试此示例：

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

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

请注意，“全”字节（255 或 FF）和“半满”字节（128 或 80）最终位于每个结果的相反两侧。这就是为什么您需要以与您期望获得相同值相反的顺序指定十六进制常量。

此外，无需使用在线转换器。 Hex() 函数提供给定数字的十六进制值，Int 将采用十六进制格式的字符串并返回十进制值：

? Int("&hff0000") 
 16711680

更新：

因此，要使用此信息来创建十六进制常量，您只需在上面的立即窗口中运行 RGB() 和 Hex() 语句（按 Ctrl+G 打开和关闭它），然后使用生成的十六进制值作为常量。如果该值的长度小于 6 位数字，您可以在左侧填充零，但这在技术上是不必要的：

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

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

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.

回复收藏 0 原文

陈甜 2024-11-14 06:04:54

您必须反转字节才能

BLUE = &HE8DEB7

获得正确的颜色值。

You'll have to reverse the bytes into order

BLUE = &HE8DEB7

to get the correct color value.

回复收藏 0 原文

累赘 2024-11-14 06:04:54

好的，下面将采用 Excel 2010 中单元格的颜色并提供有效的十六进制代码：

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

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

回复收藏 0 原文

梦太阳 2024-11-14 06:04:54

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

请注意，Excel RGB 值是向后的 (BGR)

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)

回复收藏 0 原文

野心澎湃 2024-11-14 06:04:54

下面是另一个在 MS Access 中也适用的函数，它解释了相反的 RGB 顺序：

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

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

回复收藏 0 原文

记忆で 2024-11-14 06:04:54

抱歉我迟到了。

ActiveX 元素（例如工作表上的按钮）可以更改其背景和字体颜色。它们使用类似于 &H00B5752F& 的特定十六进制格式。它基于蓝、绿、红顺序，而不是 RGB 类型的红、绿、蓝顺序。

如果您知道 RGB 值并想要转换为 ActiveX 颜色十六进制格式，则可以使用以下过程。

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

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

回复收藏 0 原文

月光色 2024-11-14 06:04:54

我测试了这段代码，不能真正遵循霍华德的答案

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

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

回复收藏 0 原文

~没有更多了~