VBA：使用默认颜色提取图表中的线条的RGB值 [英] VBA: Extracting the RGB value of lines in a chart with default colors

问题描述

我想知道如何读取图表中自动指定颜色的当前RGB值，即使这会将颜色冻结到其当前

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值明确地设置系列的颜色，这很容易，例如

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.

' 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

't白色，但是从主题自动分配的颜色。

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

我认为颜色不存储在系列中是有意义的。甚至将索引存储到颜色方案通常不会工作，因为Excel需要更改颜色，如果另一个数据系列添加或有人重新排序数据。不过，我会喜欢它，如果有一些方法来自动识别当前的RGB值。

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.

对于拥有6个或更少条目的图表，一个简单的解决方法是利用主题颜色按顺序分配，因此我可以做（例如）

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.)

chrt.SeriesCollection(1).DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.ObjectThemeColor _
= msoThemeColorAccent1

一旦主题已经用尽，用于区分条目的 TintAndShade ，但这是一个丑陋的黑客。

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.

有人提出了基本相同的问题（如何提取主题颜色）这里，但它从来没有回答。有几种来源建议将已知主题颜色转换为RGB值（例如此处和在这里），但只是提出了问题;我不知道先前的颜色，除了此线当前是什么颜色。

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. here and here) but that just begs the question; I don't know the color a priori, other than "whatever color this line currently is."

推荐答案

所以这很有趣。我使用所有默认值创建折线图，然后运行此过程：

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

立即窗口随后显示：

Line Colors   
Series1 : 16777215
Series2 : 16777215
Series3 : 16777215

但显然不是这样。显然，它们都是不同的颜色。如果，而不是 .RGB 我做 .ObjectThemeColor ，那么我得到所有通过观察图表同样可以证明是错误的code>。

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!

Line Colors   
Series1 : 0
Series2 : 0
Series3 : 0

它变得有趣：

如果在创建图表后，我更改了系列颜色（甚至保持不变，分配给相同的ThemeColors），则该函数显示有效的RGBs：

If, after having created the chart I change the series colors (or even leave them unchanged, by assigning to the same ThemeColors), then the function shows valid RGBs:

Line Colors   
Series1 : 5066944
Series2 : 12419407
Series3 : 5880731

。）完全无法识别线形图上的自动分配的颜色。一旦您指定了颜色，它就可以读取颜色。

It is as if Excel (and PowerPoint/etc.) are completely unable to recognize the automatically assigned colors, on Line Charts. Once you assign a color, then it may be able to read the color.

注意：折线图很挑剔，因为你不有一个 .Fill ，而是一个 .Format.Line.ForeColor （和 .BackColor ）和IIRC也有一些其他怪癖，像你可以选择一个单独的点并更改它的填充颜色，然后影响前一个线段的视觉外观，etc ...

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 point and change it's fill color, and then that affects the visual appearance of the preceding line segment, etc...

这只限于折线图吗？也许。我过去的经验说可能，虽然我不能说这是一个错误，它肯定似乎是一个错误。

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

然后我得到看起来像是有效的RGB值：

Then I get what appear to be valid RGB values:

Column Colors 
Series1 : 12419407
Series2 : 5066944
Series3 : 5880731

然而：它仍然不能识别有效的 ObjectThemeColor 。如果我改变 .RGB ，则输出：

HOWEVER: It still doesn't recognize a valid ObjectThemeColor. If I change .RGB then this outputs:

Column Colors 
Series1 : 0
Series2 : 0
Series3 : 0

对这些观察，肯定有一些不能自动访问的 ObjectThemeColor 和/或 .RGB 属性 - 分配的颜色格式。

So based on these observations, there is certainly some inability to access the ObjectThemeColor and/or .RGB property of automatically-assigned color formats.

正如蒂姆·威廉姆斯所证实的，这是一个错误，早在2005年，至少因为它属于RGB，使用ObjectThemeColor等等传递到Excel 2007+的bug）...它不可能很快被解决，所以我们需要一个hack解决方案：）

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

UPDATED SOLUTION

结合以上两种方法！将每个系列从行转换为 xlColumnClustered ，然后从 .Fill 查询color属性，然后更改系列图键入回原始状态。这可能比尝试利用顺序索引（如果用户已重新排序该系列将不可靠，例如，使得Series1在索引3等）更可靠。

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.)

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

这篇关于VBA：使用默认颜色提取图表中的线条的RGB值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！