从图表系列获得主题颜色信息。-GettingThemeColorinfofromChartSeries

2  

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!

但事实显然并非如此。很明显，它们都是不同的颜色。如果我使用.ObjectThemeColor而不是.RGB，那么我将得到所有的0，通过观察图表，这同样是错误的。

Line Colors   
Series1 : 0
Series2 : 0
Series3 : 0

Now here is where it gets interesting:

有趣的是:

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:

如果在创建了图表之后，我更改了系列颜色(或者通过分配到相同的主题颜色来保持不变)，那么该函数将显示有效的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.

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

注意:线形图很挑剔，因为您没有. fill，而是. format .Line。ForeColor(和.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.

这仅限于线图吗?也许。我过去的经验告诉我“可能”，虽然我不能说这是一个bug，但它确实是一个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-assigned color 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 :)

正如Tim Williams所证实的那样，这是一个早在2005年就已经存在的bug，至少它与RGB有关，并且很可能是那个bug与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.)

结合以上两种方法!将每个系列从行转换为xlcolumnclusters，然后从. 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

credit: David Zemens

信贷:大卫Zemens

1  

It is one of solutions DeerSpotter. But after you change your chart type to some temp ChartType and then changing it back to original one. You are losing some format. For an example, you get second axis after it, what is not always need.

这是一种解决方法。但是在您将图表类型更改为一些临时的ChartType之后，然后将其更改为原始的图表类型。你正在丢失一些格式。例如，你在它之后得到第二个轴，它并不总是需要。

So, when your chart line color set automatically, you can not simply get color index of it. You would always receive 0 or 16777215 - white color. The best solution is to just before you try to change or get your chart line color, to do this little trick with Line.Visible tur off and then trun on. After it you can simply read and chnage your line color.

因此，当你的图表线自动设置颜色时，你不能简单地得到它的颜色索引。你会得到0或16777215 -白色。最好的解决方案是在您尝试更改或获取图表线颜色之前，对线条进行这个小技巧。可以看见，然后继续前进。在它之后，你可以简单地阅读和记录你的线条颜色。

ppSeries.Format.Line.Visible = msoFalse 
ppSeries.Format.Line.Visible = msoTrue