从图表系列获取主题颜色信息 [英] Getting Theme Color info from Chart Series

问题描述

我有一个图表系列使用一种颜色（它可以是一个 msoThemeColorAccent<＃> 或任何其他）的标记线，另一种颜色的标记填充它可以是 msoThemeColorAccent <＃> ，更轻的x％或任何其他），并且没有行。我想确定图表系列是否使用 msoThemeColorAccent<＃> 作为标记行， msoThemeColorAccent <＃>



我尝试返回一个系列的一些属性，没有运气。在执行具有引用 sr 到图表系列的Sub的断点处（为了测试目的，将其设置为具有其颜色与标记线和标记填充，设置在Accent1），并在和sr 结构内部，我在即时窗口中测试：

 ？ .Format.Line.BackColor.ObjectThemeColor，.Format.Line.ForeColor.ObjectThemeColor 
 0 0 
？ .Format.Fill.BackColor.ObjectThemeColor，.Format.Fill.ForeColor.ObjectThemeColor 
 0 -2 
  

我希望以某种方式获得 msoThemeColorAccent1 （= 5）的值，这在我的系列中使用。有没有办法做到这一点？

我发现使用 msoThemeColorIndex 设置颜色的所有示例，没有作为被读取的值。

PS：我在这个第一部分没有成功，一个系列的颜色一致。我预见如果我克服这个障碍，我可能仍然有问题在检测正确的 TintAndShade ，甚至在没有线的目标情况下的重音颜色，不同的标记填充vs 。标记线。

解决方案

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

  Sub getLineCOlors（）
 Dim cht As Chart 
 Dim srs As Series 
 Dim colors As String 
 Dim pt As Point 
 
设置cht = ActiveSheet.ChartObjects（1）.Chart 
 
对于每个srs在cht.SeriesCollection 
使用srs.Format.Line 
 colors = colors& vbCrLf& srs.Name& ：& _ 
 .ForeColor.RGB 
结束于
 
下一页
 
 Debug.Print线颜色，颜色
 
结束Sub 
  

立即窗口随后显示：

 线条颜色
系列1：16777215 
系列2：16777215 
系列3：16777215 
  

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

 线颜色
 Series1：0 
 Series2：0 
 Series3：0 
  

它变得有趣：

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

 线颜色
 Series1：5066944 
 Series2：12419407 
 Series3：5880731 
  

就像Excel（和PowerPoint等）完全无法识别自动分配的颜色，在线图上。一旦您指定了颜色，它就可以读取颜色。

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

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

如果我运行类似的过程在柱形图上 - 仅使用自动分配的默认颜色

  Sub getCOlumnColors（）
 
 Dim cht As Chart 
 Dim srs As Series 
 Dim colors As String 
 Dim pt As Point 
 
设置cht = ActiveSheet.ChartObjects（2） .Chart 
 
对于每个srs在cht.SeriesCollection 
 
使用srs.Format.Fill 
 colors = colors& vbCrLf& srs.Name& ：& _ 
 .ForeColor.RGB 
结束于
 
下一页
 
 Debug.Print列颜色，颜色
 
结束Sub 
  

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

 列颜色
系列1：12419407 
系列2：5066944 
系列3：5880731 
  

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

  $ b Series1：0 
 Series2：0 
 Series3：0 
  

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

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

UPDATED SOLUTION

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

  Sub getLineColors（）
 Dim cht As Chart 
 Dim chtType As Long 
 Dim srs As Series 
 Dim colors As String 
 
设置cht = ActiveSheet.ChartObjects（1）.Chart 
 
对于每个srs在cht.SeriesCollection 
 chtType = srs.ChartType 
'暂时将其转为柱形图：
 srs.ChartType = 51 
 colors = colors& vbCrLf& srs.Name& ：& _ 
 srs.Format.Fill.ForeColor.RGB 
将图表类型重置为其原始状态：
 srs.ChartType = chtType 
下一页
 
 Debug.Print线颜色，颜色
 
结束子
  

David Zemens

I have a chart series using one color (it can be an msoThemeColorAccent<#> or any other) for the marker line, another color for the marker fill (it can be an msoThemeColorAccent<#>, lighter x%, or any other), and no lines. I want to determine if the chart series is using an msoThemeColorAccent<#> for the marker line, an msoThemeColorAccent<#>, lighter x%, for the marker fill, and if so which specific accent color in each case.

I tried returning some properties of a series, with no luck. At a breakpoint during execution of a Sub that has a reference sr to a chart series (which was, for testing purposes, set to have lines, with its color being the same as marker lines and marker fill, set at Accent1), and inside a With sr construction, I tested in the immediate window:

? .Format.Line.BackColor.ObjectThemeColor, .Format.Line.ForeColor.ObjectThemeColor
0             0
? .Format.Fill.BackColor.ObjectThemeColor, .Format.Fill.ForeColor.ObjectThemeColor
0            -2

I expect to obtain in some way a value of msoThemeColorAccent1 (=5), which is used in my series. Is there any way to do this?

All the examples that I found used a msoThemeColorIndex for setting a color, none as a value that was read.

PS: I did not succeed in this first part, with a series with uniform color. I foresee that if I overcome this hurdle, I may still have problems in detecting the correct TintAndShade, or even the accent color in the target case of no line, different marker fill vs. marker line.

解决方案

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!

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:

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.

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

Then I get what appear to be valid RGB values:

Column Colors 
Series1 : 12419407
Series2 : 5066944
Series3 : 5880731

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

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.

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

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

credit: David Zemens

这篇关于从图表系列获取主题颜色信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！