VBA：使用默认颜色提取图表中的线条的RGB值

本文介绍了VBA：使用默认颜色提取图表中的线条的RGB值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 问题 我想知道如何在图表中读取自动分配的颜色的当前RGB值，即使这需要将颜色冻结到当前的颜色价值观（而不是更改它们，因为主题被改变，系列被重新排列等等） Usecase 我的实际应用是我想使数据标签符合线形图中线/标记的颜色。如果我通过一个方案或显式的RGB值明确地设置了这个系列的颜色，比如 '假设ColorFormat很容易。 Type = msoColorTypeRGB s.DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = _ s.Format.Line.ForeColor.RGB 但是，自动分配系列颜色时，会执行此操作会导致白色标签。更具体地说，以下两个等式均保持 s.Format.Line.ForeColor.Type = msoColorTypeRGB s。 Format.Line.ForeColor.RGB = RGB（255,255,255）'白色 然而，这行当然是不是白色，而是主题中自动分配的颜色。这表明自动分配颜色 s.Border.ColorIndex = xlColorIndexAutomatic 我认为这个颜色并没有与该系列存储在一起是有道理的。即使将索引存储到颜色方案中通常也不会起作用，因为如果添加另一个数据系列或有人重新排序数据，Excel需要更改颜色。不过，如果有一些方法可以自动识别当前的RGB值，我会很喜欢它。 一个丑陋的解决方法 对于6个或更少条目的图表，一个简单的解决方法是利用主题颜色顺序分配，所以我可以做（例如） chrt.SeriesCollection（1）.DataLabels.Format.TextFrame2。 TextRange.Font.Fill.ForeColor.ObjectThemeColor _ = msoThemeColorAccent1 大概这可以扩展为了说明一旦主题已经耗尽，用于区分条目的 TintAndShade ，但这是一个丑陋的黑客。 研究 有人问本质上同样的问题（如何提取主题颜色）这里，但它从来没有回答。有几个来源提供了将已知主题颜色转换为RGB值的方法（例如此处和这里），但只是提出这个问题;我不知道先前的颜色，除了这行目前是什么颜色。解决方案所以这很有趣。我创建一个使用所有默认值的折线图，然后我运行这个过程： 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 结束 下一张 调试打印线条颜色，颜色 结束Sub 然后立即窗口显示： 行颜色系列1：16777215 系列2：16777215 系列3：16777215 但显然不是这样。很明显，它们都是不同的颜色。如果，而不是 .RGB 我做 .ObjectThemeColor ，那么我得到所有的 0 ，通过观察图表同样和可疑的错误！ 线条颜色 Series1：0 系列2：0 系列3：0 它变得有趣： 如果在创建图表之后，我更改系列颜色（或甚至将它们保持不变，由分配给相同的ThemeColors），然后该函数显示有效的RGBs： 行颜色 Series1：5066944 系列2：12419407 系列3：5880731 就好像Excel（和PowerPoint / etc 。）在线图上完全无法识别自动分配的颜色。分配颜色后，可能会读取颜色。 注意：折线图很挑剔，因为您不有一个 .Fill ，而是一个 .Format.Line.ForeColor （和 .BackColor ）和IIRC也有一些其他怪癖，就像您可以选择一个单独的点，并更改其填充颜色，然后影响前一行段的视觉外观，等... 这是限于折线图吗？也许。我过去的经历说可能，虽然我不能说这是一个错误，但它似乎是一个错误。 如果我运行相似的程序在柱状图上 - 再次仅使用自动分配的默认颜色， 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 结束 下一个 Debug.Print列颜色，颜色 结束Sub 然后我得到什么似乎是有效的RGB值： 列颜色系列1：12419407 系列2：5066944 系列3：5880731 / pre> 然而：仍然无法识别有效的 ObjectThemeColor 。如果我更改 .RGB ，则输出： 列颜色系列1：0 系列2：0 系列3：0 对于这些观察结果，肯定有些无法自动访问 c> ObjectThemeColor 和/或 .RGB 属性分配的颜色格式。 正如蒂姆·威廉姆斯所说，这是一个bug，早在2005年，至少它与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 ProblemI 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.)UsecaseMy 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 = msoColorTypeRGBs.DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB= _s.Format.Line.ForeColor.RGBHowever, doing this when the series color is assigned automatically results in white labels. More specifically, both of the following equalities holds.Format.Line.ForeColor.Type = msoColorTypeRGB s.Format.Line.ForeColor.RGB = RGB(255,255,255) ' WhiteAnd yet the line of course isn't white, but is an automatically assigned color from the theme. This shows that the color is automatically assigneds.Border.ColorIndex = xlColorIndexAutomaticI 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. An Ugly WorkaroundFor 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 _= msoThemeColorAccent1Presumably 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.ResearchSomeone 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 ChartDim srs As SeriesDim colors As StringDim pt As PointSet cht = ActiveSheet.ChartObjects(1).ChartFor Each srs In cht.SeriesCollection With srs.Format.Line colors = colors & vbCrLf & srs.Name & " : " & _ .ForeColor.RGB End WithNextDebug.Print "Line Colors", colorsEnd SubThe Immediate window then displays:Line Colors Series1 : 16777215Series2 : 16777215Series3 : 16777215But 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 : 0Series2 : 0Series3 : 0Now 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 : 5066944Series2 : 12419407Series3 : 5880731It 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 ChartDim srs As SeriesDim colors As StringDim pt As PointSet cht = ActiveSheet.ChartObjects(2).ChartFor Each srs In cht.SeriesCollection With srs.Format.Fill colors = colors & vbCrLf & srs.Name & " : " & _ .ForeColor.RGB End WithNextDebug.Print "Column Colors", colorsEnd SubThen I get what appear to be valid RGB values:Column Colors Series1 : 12419407Series2 : 5066944Series3 : 5880731HOWEVER: It still doesn't recognize a valid ObjectThemeColor. If I change .RGB then this outputs:Column Colors Series1 : 0Series2 : 0Series3 : 0So 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 SOLUTIONCombine 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 ChartDim chtType As LongDim srs As SeriesDim colors As StringSet cht = ActiveSheet.ChartObjects(1).ChartFor 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 = chtTypeNextDebug.Print "Line Colors", colorsEnd Sub 这篇关于VBA：使用默认颜色提取图表中的线条的RGB值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！