VBA:使用默认颜色提取图表中线条的RGB值
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/25825269/
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
VBA: Extracting the RGB value of lines in a chart with default colors
提问by Alice
Problem
问题
I would like to know how to read the current RGB value of an automatically assigned color in a chart, even if this entails freezing the colors to their current values (rather than updating them as the theme is changed, series are reordered, etc.)
我想知道如何读取图表中自动分配颜色的当前 RGB 值,即使这需要将颜色冻结为其当前值(而不是在主题更改、系列重新排序等时更新它们)
Usecase
用例
My actual usecase is that I would like to make the datalabels match the color of the lines/markers in a line chart. This is easy if I have explicitly set the colors of the series via a scheme or explicit RGB values, e.g.
我的实际用例是我想让数据标签与折线图中线条/标记的颜色相匹配。如果我通过方案或显式 RGB 值显式设置系列的颜色,这很容易,例如
' assuming ColorFormat.Type = msoColorTypeRGB
s.DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB= _
s.Format.Line.ForeColor.RGB
However, doing this when the series color is assigned automatically results in white labels. More specifically, both of the following equalities hold
但是,在自动分配系列颜色时执行此操作会导致白色标签。更具体地说,以下两个等式都成立
s.Format.Line.ForeColor.Type = msoColorTypeRGB
s.Format.Line.ForeColor.RGB = RGB(255,255,255) ' White
And yet the line of course isn't white, but is an automatically assigned color from the theme. This shows that the color is automatically assigned
然而,这条线当然不是白色的,而是从主题中自动分配的颜色。这表明颜色是自动分配的
s.Border.ColorIndex = xlColorIndexAutomatic
I suppose it makes sense that the color isn't stored with the series in question. Even storing the index into the color scheme wouldn't generally work as Excel needs to change the color if another data series is added or someone reorders the data. Still, I would love it if there were some way to identify the current RGB value automatically.
我认为颜色不与相关系列一起存储是有道理的。即使将索引存储到配色方案中通常也不起作用,因为如果添加了另一个数据系列或有人对数据重新排序,Excel 需要更改颜色。不过,如果有某种方法可以自动识别当前的 RGB 值,我会喜欢它。
An Ugly Workaround
一个丑陋的解决方法
For charts with 6 or fewer entries, a simple workaround is to exploit the fact that theme colors are assigned sequentially, so I can do (e.g.)
对于具有 6 个或更少条目的图表,一个简单的解决方法是利用主题颜色按顺序分配的事实,因此我可以这样做(例如)
chrt.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.ObjectThemeColor _
= msoThemeColorAccent1
Presumably this could be extended to account for the TintAndShade
used to differentiate entries once the theme has been exhausted, but this is such an ugly hack.
据推测,TintAndShade
一旦主题用尽,这可以扩展到用于区分条目的原因,但这是一个如此丑陋的黑客。
Research
研究
Someone asked essentially the same question (how to extract theme colors) here, but it was never answered. There are several sources suggesting ways to convert a known theme color into RGB values (e.g. hereand here) but that just begs the question; I don't know the color a priori, other than "whatever color this line currently is."
有人(如何提取主题颜色)主要问同样的问题在这里,但它从来没有回答。有几个来源建议将已知主题颜色转换为 RGB 值的方法(例如此处和此处),但这只是引出了一个问题;除了“这条线目前是什么颜色”之外,我不知道先验的颜色。
采纳答案by David Zemens
So this is interesting. I create a line chart using all defaults, and then I run this procedure:
所以这很有趣。我使用所有默认值创建一个折线图,然后运行以下过程:
Sub getLineCOlors()
Dim cht As Chart
Dim srs As Series
Dim colors As String
Dim pt As Point
Set cht = ActiveSheet.ChartObjects(1).Chart
For Each srs In cht.SeriesCollection
With srs.Format.Line
colors = colors & vbCrLf & srs.Name & " : " & _
.ForeColor.RGB
End With
Next
Debug.Print "Line Colors", colors
End Sub
The Immediate window then displays:
然后立即窗口显示:
Line Colors
Series1 : 16777215
Series2 : 16777215
Series3 : 16777215
But this is clearly not the case. It is obvious that they all are different colors. If, instead of .RGB
I do .ObjectThemeColor
, then I get all 0
, which is equally and demonstrably false by observing the chart!
但显然情况并非如此。很明显,它们都是不同的颜色。如果,而不是.RGB
我做.ObjectThemeColor
,那么我得到了所有0
,通过观察图表,这同样明显是错误的!
Line Colors
Series1 : 0
Series2 : 0
Series3 : 0
Now here is where it gets interesting:
现在这里是它变得有趣的地方:
If, after having created the chart I changethe series colors (or even leave them unchanged, by assigning to the same ThemeColors), then the function shows valid RGBs:
如果在创建图表后我更改了系列颜色(甚至通过分配给相同的 ThemeColors 使它们保持不变),则该函数显示有效的 RGB:
Line Colors
Series1 : 5066944
Series2 : 12419407
Series3 : 5880731
It is as if Excel (and PowerPoint/etc.) are completely unable to recognize the automatically assignedcolors, on Line Charts. Once you assign a color, then it may be able to read the color.
就好像 Excel(和 PowerPoint/等)完全无法识别折线图上自动分配的颜色。一旦你指定了一种颜色,它就可以读取颜色。
NOTE: Line charts are picky, because you don't have a .Fill
, but rather a .Format.Line.ForeColor
(and .BackColor
) and IIRC there are some other quirks, too, like you can select an individual pointand change it's fill color, and then that affects the visual appearance of the preceding line segment, etc...
注意:折线图很挑剔,因为您没有.Fill
, 而是 a .Format.Line.ForeColor
(and .BackColor
) 和 IIRC 还有一些其他的怪癖,例如您可以选择单个点并更改其填充颜色,然后这会影响视觉效果前面线段的出现等...
Is this limited to line charts?Perhaps. My past experience says "probably", although I am not in a position to say that this is a bug, it certainly seems to be a bug.
这仅限于折线图吗?也许。我过去的经验说“可能”,虽然我不能说这是一个错误,但它肯定是一个错误。
If I run a similar procedure on a Column Chart -- again using only the default colors that are automatically assigned,
如果我在柱形图上运行类似的过程——再次仅使用自动分配的默认颜色,
Sub getCOlumnColors()
Dim cht As Chart
Dim srs As Series
Dim colors As String
Dim pt As Point
Set cht = ActiveSheet.ChartObjects(2).Chart
For Each srs In cht.SeriesCollection
With srs.Format.Fill
colors = colors & vbCrLf & srs.Name & " : " & _
.ForeColor.RGB
End With
Next
Debug.Print "Column Colors", colors
End Sub
Then I get what appear to be valid RGB values:
然后我得到了似乎是有效的 RGB 值:
Column Colors
Series1 : 12419407
Series2 : 5066944
Series3 : 5880731
HOWEVER: It still doesn't recognize a valid ObjectThemeColor
. If I change .RGB
then this outputs:
但是:它仍然无法识别有效的ObjectThemeColor
. 如果我改变.RGB
那么这个输出:
Column Colors
Series1 : 0
Series2 : 0
Series3 : 0
So based on these observations, there is certainly some inability to access the ObjectThemeColor
and/or .RGB
property of automatically-assignedcolor formats.
因此,根据这些观察,肯定无法访问自动分配的颜色格式的ObjectThemeColor
和/或.RGB
属性。
As Tim Williams confirms, this was a bug as far back as 2005 at least as it pertains to the RGB, and probably that bug carried over in to Excel 2007+ with the ObjectThemeColor, etc... It is not likely to be resolved any time soon then, so we need a hack solution :)
正如蒂姆·威廉姆斯所确认的那样,这是一个早在 2005 年的错误,至少因为它与 RGB 相关,并且该错误可能通过 ObjectThemeColor 等延续到 Excel 2007+ 中......它不太可能得到解决时间很快,所以我们需要一个黑客解决方案:)
UPDATED SOLUTION
更新的解决方案
Combine the two methods above! Convert each series from line to xlColumnClustered
, then query the color property from the .Fill
, and then change the series chart type back to its original state. This may be more reliable than trying to exploit the sequential indexing (which will not be reliable at all if the users have re-ordered the series, e.g., such that "Series1" is at index 3, etc.)
结合以上两种方法!将每个系列从 line 转换为xlColumnClustered
,然后从查询颜色属性.Fill
,然后将系列图表类型更改回其原始状态。这可能比尝试利用顺序索引更可靠(如果用户重新排序了系列,例如“Series1”位于索引 3 等,则它根本不可靠)
Sub getLineColors()
Dim cht As Chart
Dim chtType As Long
Dim srs As Series
Dim colors As String
Set cht = ActiveSheet.ChartObjects(1).Chart
For Each srs In cht.SeriesCollection
chtType = srs.ChartType
'Temporarily turn this in to a column chart:
srs.ChartType = 51
colors = colors & vbCrLf & srs.Name & " : " & _
srs.Format.Fill.ForeColor.RGB
'reset the chart type to its original state:
srs.ChartType = chtType
Next
Debug.Print "Line Colors", colors
End Sub
回答by Gaetan Lousberg
Here is the code I used in the end.
这是我最后使用的代码。
Sub ShowSeries()
Dim mySrs As Series
Dim myPts As Points
Dim chtType As Long
Dim colors As String
With ActiveSheet
For Each mySrs In ActiveChart.SeriesCollection
'Add label
Set myPts = mySrs.Points
myPts(myPts.Count).ApplyDataLabels ShowSeriesName:=True, ShowValue:=False
'Color text label same as line color
'if line has default color
If mySrs.Border.ColorIndex = -4105 Then
chtType = mySrs.ChartType
'Temporarily turn this in to a column chart:
mySrs.ChartType = 51
mySrs.DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = _
mySrs.Format.Fill.ForeColor.RGB
'reset the chart type to its original state:
mySrs.ChartType = chtType
'if line has a color manually changed by user
Else
mySrs.DataLabels.Font.ColorIndex = mySrs.Border.ColorIndex
End If
Next
End With
End Sub
结束子
回答by Alexandros Antoniou
After half a day I managed to solve this issue:
半天后,我设法解决了这个问题:
Sub ......()
Dim k as Integer
Dim colorOfLine as Long
...............
.................
'Loop through each series
For k = 1 To ActiveChart.SeriesCollection.Count
With ActiveChart.FullSeriesCollection(k)
.HasDataLabels = True
'Put a fill on datalabels
.DataLabels.Format.Fill.Solid
'Get color of line of series
colorOfLine = .Format.Line.ForeColor.RGB
'Assign same color on Fill of datalabels of series
.DataLabels.Format.Fill.ForeColor.RGB = colorOfLine
'white fonts in datalabels
.DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
End With
Next k
..........
End Sub