vba 从 Range.Interior.Color（或任何其他颜色属性）返回 RGB 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24132665/
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
Return RGB values from Range.Interior.Color (or any other Color Property)
提问by CodeJockey
I was trying to incrementally change the background color of a cell to black, and I found that the Range.Interior.Color method returns a Long which is seemingly arbitrary. Looking at the documentation on MSDN, there is nearly nothing about what this number represents. Is there a way to return the RGB value from this long. I effectively need the opposite of the RGB(red, green, blue) function.
我试图逐步将单元格的背景颜色更改为黑色，但我发现 Range.Interior.Color 方法返回一个看似任意的 Long。查看 MSDN 上的文档，几乎没有关于这个数字代表什么的信息。有没有办法从这个长返回RGB值。我实际上需要与 RGB(red, green, blue) 函数相反的函数。
采纳答案by CodeJockey
Short Answer:
简答：
There is no built in functionality for this. You must write your own function.
没有为此内置功能。您必须编写自己的函数。
Long Answer:
长答案：
The long that is returned from the Interior.Color property is a decimal conversion of the typical hexidecimal numbers that we are used to seeing for colors in html e.g. "66FF66". Additionally the constant xlNone (-4142) can be passed to set cell to have no color in the background, however such cells are marked white RGB(255, 255, 255)from the Getproperty. Knowing this, we can write a function that returns one or all of the appropriate RGB values.
从 Interior.Color 属性返回的 long 是我们习惯于在 html 中看到颜色的典型十六进制数的十进制转换，例如“66FF66”。此外，可以传递常量 xlNone (-4142) 以将单元格设置为背景中没有颜色，但是此类单元格RGB(255, 255, 255)从Get属性中标记为白色。知道了这一点，我们可以编写一个函数来返回一个或所有适当的 RGB 值。
Luckily, a kind Mr. Allan Wyatt has done just that here!
幸运的是，一位好心的艾伦·怀亚特先生在这里做到了！
回答by Mark Balhoff
That "arbitrary" number is a mathematical combination of the RGB values (B*256^2 + G*256 + R) and a conversion of the hex color value to a decimal number (base 16 to base 10), depending on which way you want to look at it. Just different bases. Below is the method I use in the XLAM addin file I wrote for Excel. This method has come in handy many times. I have included the documentation in my addin file.
该“任意”数字是 RGB 值 (B*256^2 + G*256 + R) 和十六进制颜色值到十进制数（基数为 16 到基数 10）的转换的数学组合，具体取决于哪种方式你想看看它。只是基数不同。下面是我在为 Excel 编写的 XLAM 插件文件中使用的方法。这个方法已经派上用场很多次了。我已将文档包含在我的插件文件中。
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Function Color
' Purpose Determine the Background Color Of a Cell
' @Param rng Range to Determine Background Color of
' @Param formatType Default Value = 0
' 0 Integer
' 1 Hex
' 2 RGB
' 3 Excel Color Index
' Usage Color(A1) --> 9507341
' Color(A1, 0) --> 9507341
' Color(A1, 1) --> 91120D
' Color(A1, 2) --> 13, 18, 145
' Color(A1, 3) --> 6
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function Color(rng As Range, Optional formatType As Integer = 0) As Variant
Dim colorVal As Variant
colorVal = Cells(rng.Row, rng.Column).Interior.Color
Select Case formatType
Case 1
Color = Hex(colorVal)
Case 2
Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
Case 3
Color = Cells(rng.Row, rng.Column).Interior.ColorIndex
Case Else
Color = colorVal
End Select
End Function
回答by Harry S
good to see that Mr Wyatt uses the fast method of color to RGB
很高兴看到 Wyatt 先生使用了快速的颜色到 RGB 的方法
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256
which is many times faster than those using hex str with left mid right that some recommend
这比使用 hex str 和一些人推荐的左中右快很多倍
回答by kraft hannes
The other answer did not work for me. I found that:
另一个答案对我不起作用。我找到：
R = C And 255
G = C \ 256 And 255
B = C \ 256 ^ 2 And 255
and it worked properly.
它工作正常。
回答by dumpguy
Mark Balhoff′s VBA script works fine. All credits go to him.
Mark Balhoff 的 VBA 脚本运行良好。所有的功劳都归于他。
In case you′d like to get the color codes/indexes of conditionally formatted cells as well, the code may be amended like this:
如果您还想获取条件格式单元格的颜色代码/索引，可以像这样修改代码：
'----------------------------------------------------------------
' Function Color
' Purpose Determine the Background Color Of a Cell
' @Param rng Range to Determine Background Color of
' @Param formatType Default Value = 0
' 0 Integer color of cell, not considering conditional formatting color
' 1 Hex color of cell, not considering conditional formatting color
' 2 RGB color of cell, not considering conditional formatting color
' 3 Excel Color Index color of cell, not considering conditional formatting color
' 4 Integer "real" visible color of cell (as the case may be the conditional formatting color)
' 5 Hex "real" visible color of cell (as the case may be the conditional formatting color)
' 6 RGB "real" visible color of cell (as the case may be the conditional formatting color)
' 7 Excel Color Index "real" visible color of cell (as the case may be the conditional formatting color)
' Usage Color(A1) --> 9507341
' Color(A1, 0) --> 9507341
' Color(A1, 1) --> 91120D
' Color(A1, 2) --> 13, 18, 145
' Color(A1, 3) --> 6
'-----------------------------------------------------------------
Function Color(rng As Range, Optional formatType As Integer = 0) As Variant
Dim colorVal As Variant
Select Case formatType
Case 0 To 3
colorVal = Cells(rng.Row, rng.Column).Interior.Color
Case 4 To 7
colorVal = Cells(rng.Row, rng.Column).DisplayFormat.Interior.Color
End Select
Select Case formatType
Case 0
Color = colorVal
Case 1
Color = Hex(colorVal)
Case 2
Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
Case 3
Color = Cells(rng.Row, rng.Column).Interior.ColorIndex
Case 4
Color = colorVal
Case 5
Color = Hex(colorVal)
Case 6
Color = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
Case 7
Color = Cells(rng.Row, rng.Column).DisplayFormat.Interior.ColorIndex
End Select
End Function
回答by Johnny MMXVII
Should note, for the hex values, if you're exporting out to HTML you're going to get quirks too.
应该注意，对于十六进制值，如果您要导出为 HTML，您也会遇到怪癖。
Ideally you'd create the hex string from the individual colours, rather than returning a hex from the ColorVal number.
理想情况下，您应该从各个颜色创建十六进制字符串，而不是从 ColorVal 数字返回十六进制。
The reason being you can get some invalid hex numbers if the cell is a 'pure' colour like green/blue
原因是如果单元格是绿色/蓝色等“纯”颜色，您可以获得一些无效的十六进制数字
RED - RGB(255,0,0) returns 'FF' - it should return 'FF0000'
RED - RGB(255,0,0) 返回 'FF' - 它应该返回 'FF0000'
BLUE - RGB(0,0,255) returns 'FF00000' - it should return '0000FF'
蓝色 - RGB(0,0,255) 返回 'FF00000' - 它应该返回 '0000FF'
If you used these to create HTML/CSS colour output, you'd get RED for any blue cells.
如果您使用这些来创建 HTML/CSS 颜色输出，您将获得任何蓝色单元格的红色。
I modified the script to assemble each two character hex 'chunk' based on the RGB values, with a UDF that just pads with a leading 0 where output of one character is returned ( hopefully if you're reading this, you can make something similar )
我修改了脚本以根据 RGB 值组合每两个字符的十六进制“块”，使用一个 UDF 填充前导 0，其中返回一个字符的输出（希望如果你正在阅读这篇文章，你可以做类似的事情)
Color = ZeroPad(Hex((colorVal Mod 256)), 2) & ZeroPad(Hex(((colorVal \ 256) Mod 256)), 2) & ZeroPad(Hex((colorVal \ 65536)), 2)
--Edit : forgot to include the code for the UDF...
--编辑：忘记包含 UDF 的代码...
Function ZeroPad(text As String, Cnt As Integer) As String
'Text is the string to pad
'Cnt is the length to pad to, for example ZeroPad(12,3) would return a string '012' , Zeropad(12,8) would return '00000012' etc..
Dim StrLen As Integer, StrtString As String, Padded As String, LP As Integer
StrLen = Len(Trim(text))
If StrLen < Cnt Then
For LP = 1 To Cnt - StrLen
Padded = Padded & "0"
Next LP
End If
ZeroPad = Padded & Trim(text)
ENDOF:
End Function
BTW - If you want the hex codes as displayed in the form editor ( which inexplicably has it's own standard , apart from the normal HTML Hex Colours )
顺便说一句 - 如果您希望在表单编辑器中显示十六进制代码（除了正常的 HTML 十六进制颜色之外，它莫名其妙地有自己的标准）
Case 4 ' ::: VBA FORM HEX :::
Color = "&H00" & ZeroPad(Hex((colorVal \ 65536)), 2) & ZeroPad(Hex(((colorVal \ 256) Mod 256)), 2) & ZeroPad(Hex((colorVal Mod 256)), 2) & "&"
