vba 在十六进制和十进制中设置颜色表现不同
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12644456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting colors in Hex and Decimal behaving differently
提问by Anirudh Ramanathan
I am trying to set an orangish color in the following manner:
我试图通过以下方式设置橙色：
WorkSheet.Range("A1:A5").Interior.color = 49407
and
和
WorkSheet.Range("A1:A5").Interior.color = &HC0FF 'Hex value of 49407
Aren't these two supposed to be exactly equivalent? The colors being set are different.
这两个不应该是完全等价的吗？设置的颜色不同。
回答by Erik Eidt
No, those values are not equivalent: &HC0FFis -16129, whereas &HC0FF&is 49407.
不，这些值是不等价的：&HC0FF是 -16129，而是&HC0FF&49407。
VBA, being a rather old language uses 16 bit integers by default. In this day and age, you pretty much want longs always instead of ints, but you have to ask VBA for them.
VBA 作为一种相当古老的语言，默认使用 16 位整数。在这个时代，您几乎总是想要 long 而不是 int，但是您必须向 VBA 询问它们。
&HC0FFis a construct that defines a 16-bit constant from hex. Because the sign bit in this value is set (on, negative) when interpreted as a 16-bit value, hence the conversion to -16129. We may consider that this sucks, but it is not a bug! When you use -16129 (as a signed integer), in 32 bit form, 1's are propagated thru the whole top 16 bits and that results in the blue value of 255.
&HC0FF是一个从十六进制定义一个 16 位常量的结构。因为当解释为 16 位值时，该值中的符号位被设置（开，负），因此转换为 -16129。我们可能会认为这很糟糕，但这不是错误！当您使用 -16129（作为有符号整数）时，在 32 位形式中，1 将通过整个前 16 位传播，从而导致蓝色值为 255。
What you really wanted here is a a 32-bit hex constant: &HC0FF&.
The extra &on the end tells VBA that this is a long constant instead of an int. Interpreted in 32-bits, this is a positive value, so gives the decimal equivalent you're looking for.
您在这里真正想要的是 32 位十六进制常量：&HC0FF&. 最后的额外&内容告诉 VBA 这是一个长常量而不是一个整数。以 32 位解释，这是一个正值，因此给出您正在寻找的十进制等效值。
In short, ask for long hex constants with the trailing &.
简而言之，要求带有尾随的长十六进制常量&。
As an aside, this propensity of VBA toward 16 bit can also bite us when using decimal constants, such as in an expression 16000 * 16000, which will overflow 16 bit arithmetic. So, sometimes one needs to use the trailing &on decimal constants too (or assign one to a long first).
顺便说一句，当使用十进制常量时，VBA 对 16 位的这种倾向也可能会困扰我们，例如在表达式 16000 * 16000 中，这会溢出 16 位算术。因此，有时也需要使用&十进制常量的尾随（或先将一个分配给 long）。
回答by Steve Rindsberg
This will convert the hex-format colors you have to the RGB Long that Office uses for RGB colors:
这会将您拥有的十六进制格式颜色转换为 Office 用于 RGB 颜色的 RGB Long：
Function HexToLongRGB(sHexVal As String) As Long
Dim lRed As Long
Dim lGreen As Long
Dim lBlue As Long
lRed = CLng("&H" & Left$(sHexVal, 2))
lGreen = CLng("&H" & Mid$(sHexVal, 3, 2))
lBlue = CLng("&H" & Right$(sHexVal, 2))
HexToLongRGB = RGB(lRed, lGreen, lBlue)
End Function
回答by Tony Dallimore
I believe the answers from Gimp and Johannes both miss the key issue.
我相信 Gimp 和 Johannes 的回答都忽略了关键问题。
A colour on a computer screen is defined by specifying how much red, blue and green you require. For each of the three colours you specify a number between 0 and 255. These three numbers are normally specified as a single number by concatenating the three separate numbers. With Html you specify a colour as #RRGGBB where RR, GG and BB are hexadecimal numbers or you can replace RRBBGG by the decimal equivalent. With Excel the sequence is reversed so &HBBGGRR or the decimal equivalent.
计算机屏幕上的颜色是通过指定您需要多少红色、蓝色和绿色来定义的。对于三种颜色中的每一种，您都指定一个介于 0 和 255 之间的数字。这三个数字通常通过连接三个单独的数字来指定为一个数字。使用 Html，您可以将颜色指定为 #RRGGBB，其中 RR、GG 和 BB 是十六进制数，或者您可以用十进制等效值替换 RRBBGG。在 Excel 中，顺序是相反的，所以 &HBBGGRR 或十进制等效值。
49407 is the decimal equivalent of 00C0FF which for Excel means Blue = 0, Green = 192 and Red = 255.
49407 是 00C0FF 的十进制等效值，对于 Excel 意味着蓝色 = 0、绿色 = 192 和红色 = 255。
But &HC0FF or &H00C0FF is -16129 or Blue = 255, Green = 192 and Red = 255. This seems to be a flaw in the &H conversion. I cannot find anyway of getting C0FF converted to 49407.
但是 &HC0FF 或 &H00C0FF 是 -16129 或 Blue = 255、Green = 192 和 Red = 255。这似乎是 &H 转换中的一个缺陷。我无论如何都找不到将 C0FF 转换为 49407 的方法。
If you insist on using the &H conversion, the best I can offer is:
如果你坚持使用 &H 转换，我能提供的最好的是：
Range("A1:A5").Interior.color = &H01C0FF
This is Blue = 1, Green = 192 and Red = 255 and I cannot tell the difference from Blue = 0, Green = 192 and Red = 255.
这是蓝色 = 1、绿色 = 192 和红色 = 255，我无法区分蓝色 = 0、绿色 = 192 和红色 = 255。
But I would recommend:
但我会建议：
Range("A1:A5").Interior.color = RGB(255,192,0)
because the RGB function always returns a positive number and you do not have to worry about Excel's reverse sequence.
因为 RGB 函数总是返回一个正数，你不必担心 Excel 的逆序。
回答by Johannes
A Colorcode is usually composed of three values, RED, GREEN, BLUE. Your Hexcode is missing one of the three and excel is autofilling it with FF. So your color C0FF is transformed to be FFC0FF.
颜色代码通常由三个值组成，RED、GREEN、BLUE。您的 Hexcode 缺少三个中的一个，excel 正在使用 FF 自动填充它。所以你的颜色 C0FF 被转换为 FFC0FF。
Here is an Exampleprogram so you can see it in action, make a new sheet and execute it.
这是一个示例程序，因此您可以看到它的运行情况，制作一张新表并执行它。
' C0FF will be changed to be FFC0FF
Range("A1").Interior.Color = &HC0FF
Range("A1").Select
ActiveCell.FormulaR1C1 = Range("A1").Interior.Color
Range("A2").Select
ActiveCell.FormulaR1C1 = "=DEC2HEX(R[-1]C,6)"
' 49407 is actually 00C0FF
Range("B1").Interior.Color = 49407
Range("B1").Select
ActiveCell.FormulaR1C1 = Range("B1").Interior.Color
Range("B2").Select
ActiveCell.FormulaR1C1 = "=DEC2HEX(R[-1]C,6)"
' Use RGB to have better control over your results
' Switch the order when doing so: 00C0FF => 00 Blue C0 Green FF Red
Range("C1").Interior.Color = RGB(&HFF, &HC0, &H0)
Range("C1").Select
ActiveCell.FormulaR1C1 = Range("C1").Interior.Color
Range("C2").Select
ActiveCell.FormulaR1C1 = "=DEC2HEX(R[-1]C,6)"
EDIT: Added a third example to illustrate switching the colors as suggested in a different answer.
编辑：添加了第三个示例来说明如何按照不同答案中的建议切换颜色。
