从图表系列获取主题颜色信息 [英] Getting Theme Color info from Chart Series
问题描述
我有一个图表系列使用一种颜色(它可以是一个 msoThemeColorAccent<#>
或任何其他),用于标记线,标记填充的另一种颜色它可以是 msoThemeColorAccent<#>
,更轻的x%或任何其他),没有行。我想确定图表系列是否在标记行中使用 msoThemeColorAccent<#>
, msoThemeColorAccent<#>
,更轻x%,用于标记填充,如果是,则在每种情况下都使用特定的重音颜色。
我尝试返回一系列的一些属性,没有运气。在执行Sub期间的断点处,其具有引用 sr
到图表系列(这是为了测试目的,设置为具有行,其颜色与标记线和标记填充,设置在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
Set cht = ActiveSheet.ChartObjects(1).Chart
对于每个srs在cht.SeriesCollection
用srs.Format.Line
colors = colors& vbCrLf& srs.Name& :& _
.ForeColor.RGB
结束
下一张
调试打印线条颜色,颜色
结束Sub
然后立即窗口显示:
行颜色
系列1:16777215
系列2:16777215
系列3:16777215
但显然不是这样。很明显,它们都是不同的颜色。如果,而不是 .RGB
我做 .ObjectThemeColor
,那么我得到所有的 0
,通过观察图表同样显示为假!
线条颜色
Series1:0
系列2:0
系列3:0
它变得有趣:
如果在创建图表后,我更改了系列颜色(甚至保持不变,通过分配给相同的ThemeColors),那么该功能将显示有效的RGBs:
行颜色
系列1:5066944
系列2:12419407
系列3:5880731
就好像Excel(和PowerPoint / etc。)完全无法识别在线图上自动分配的颜色。分配颜色后,可能会读取颜色。
注意:折线图很挑剔,因为您不有一个 .Fill
,而是一个 .Format.Line.ForeColor
(和 .BackColor
)和IIRC也有一些其他的怪癖,就像你可以选择一个点,改变它的填充颜色,然后影响前一行段的视觉外观等。 p>
这是限于折线图吗?也许。我过去的经历说可能,虽然我不能说这是一个错误,但它似乎是一个错误。
如果我运行相似的程序在柱形图上 - 再次仅使用自动分配的默认颜色,
Sub getCOlumnColors()
Dim cht As Chart
Dim srs As Series
Dim colors As String
Dim pt As Point
Set cht = ActiveSheet.ChartObjects(2) .Chart
对于每个srs在cht.SeriesCollection
用srs.Format.Fill
colors = colors& vbCrLf& srs.Name& :& _
.ForeColor.RGB
结束
下一个
调试打印列颜色,颜色
结束Sub
然后我看到有效的RGB值:
列颜色
/ pre>
系列1:12419407
系列2:5066944
系列3:5880731
然而,仍然无法识别有效的
ObjectThemeColor
。如果我更改.RGB
,则输出:列颜色
系列1:0
系列2:0
系列3:0
在这些观察中,肯定有一些无法访问自动分配颜色的
ObjectThemeColor
和/或.RGB
属性格式。
正如蒂姆·威廉姆斯所说,这是一个早在2005年的bug,至少与RGB有关,而且可能是错误转载到Excel 2007 +与ObjectThemeColor等...它不可能在任何时候很快解决,所以我们需要一个黑客解决方案:)
更新解决方案
结合上述两种方法!将每个系列从一行转换为xlColumnClustered,然后从.Fill中查询颜色属性,然后将系列图类型更改回原始状态。这可能比尝试利用顺序索引更可靠(如果用户已经重新排序了这个系列,那么这一点是不可靠的,例如,Series1是索引3等)。
Sub getLineColors()
Dim cht As Chart
Dim chtType As Long
Dim srs As Series
Dim颜色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线条颜色,颜色
End Sub
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 anmsoThemeColorAccent<#>
, lighter x%, or any other), and no lines. I want to determine if the chart series is using anmsoThemeColorAccent<#>
for the marker line, anmsoThemeColorAccent<#>
, 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 aWith 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 all0
, 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屋!