Excel VBA，根据系列值比较选择图表颜色 [英] Excel VBA, choosing chart color based on series value comparison

问题描述

我已经使用了一些代码来为excel图表着色很多年了，并且效果很好（尽管可能有更好的方法可以做到）。图表包含2个系列，第一个系列带有值，第二个系列带有目标。目标不会变色，但是vba会根据vba中的硬编码目标循环遍历第一个系列和颜色。

I have some code I have used to color excel charts with for quite a few years and it has worked well, (although there are likely better ways to do it). The charts contain 2 series, the first series with a value and the second with a goal. The goal does not get colored but the vba loops through the first series and colors according to hard coded goals in the vba.

我现在遇到的问题是，我添加了一个图表，该图表的目标可以逐月更改，因此进行硬编码是行不通的。如何使用相同的理论，但直接将系列1数据与系列2数据进行比较以确定颜色（案例1系列点>系列2点等）。我尝试了多种方法但均未成功，因此，我们将不胜感激。下面是经过验证的技术的代码。

The problem I have now is that I have added a chart that has a goal that can change month to month so having the hard coding doesn't work. How can I use the same theory but compare series 1 data directly to series 2 data to determine the color, (Case Is series 1 point > series 2 point, etc). I have tried a number of ways without success so any assistance would be greatly appreciated. below is the code for the proven technique.

Private Sub Worksheet_Activate()
Dim cht As Object
Dim p As Object
Dim V As Variant
Dim Counter As Integer

For Each cht In ActiveSheet.ChartObjects
Counter = 0
V = cht.Chart.SeriesCollection(1).Values
For Each p In cht.Chart.SeriesCollection(1).Points
Counter = Counter + 1
Select Case V(Counter)

'Case Is = 1
   'p.Shadow = False
   'p.InvertIfNegative = False
   'p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=3, _
   '    Degree:=0.78
   'p.Fill.Visible = True
   'p.Fill.ForeColor.SchemeColor = 5

Case Is < 0.98
    p.Shadow = False
    p.InvertIfNegative = False
    p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=3, _
        Degree:=0.78
    p.Fill.Visible = True
    p.Fill.ForeColor.SchemeColor = 3

'Case Is < 0.98
    'p.Shadow = False
    'p.InvertIfNegative = False
    'p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=4, _
    '    Degree:=0.38
    'p.Fill.Visible = True
    'p.Fill.ForeColor.SchemeColor = 6

Case Is <= 1
    p.Shadow = False
    p.InvertIfNegative = False
    p.Fill.OneColorGradient Style:=msoGradientVertical, Variant:=3, _
        Degree:=0.78
    p.Fill.Visible = True
    p.Fill.ForeColor.SchemeColor = 10

End Select
Next
Next
End Sub

推荐答案

您是否尝试过以下方法：

Did you try something like:

Case Is> .SeriesCollection（2）.Values（）（计数器）

也进行了修改，以消除一些明显的冗余（如果需要循环，请输入 >和一个计数器变量，例如，当并行循环几个集合/数组时，似乎更好的IMO是按索引循环，而不是 For Each _object _

Also revised to get rid of some apparent redundancy (if need a loop and a counter variable, e.g., when looping several collections/arrays in parallel), it seems better IMO to just loop by index, rather than For Each _object_ with a separate counter.

Private Sub Worksheet_Activate()
Dim cht As Object
Dim p As Object
Dim V As Variant
Dim Counter As Integer

For Each cht In ActiveSheet.ChartObjects
    Counter = 0
    With cht.Chart
        V = .SeriesCollection(1).Values
        For Counter = 1 to.SeriesCollection(1).Points.Count

            'Assign your Point object, if needed elsewhere
            Set p = .SeriesCollection(1).Points(Counter)

            Select Case V(Counter)

                Case Is > .SeriesCollection(2).Values()(Counter)
                'DO STUFF HERE.

                'Add other cases if needed...

            End Select
        Next
    End With
Next
End Sub

并且除非需要数组 V 出于某些其他原因，可以进一步减少该值：

And unless you need the values in an array V for some other reason, this can be further reduced:

Private Sub Worksheet_Activate()
Dim cht As Object
Dim p As Object
Dim val1, val2
Dim Counter As Integer

For Each cht In ActiveSheet.ChartObjects
    Counter = 0
    With cht.Chart
        For Counter = 1 to.SeriesCollection(1).Points.Count

            'Assign your Point object, if needed elsewhere
            Set p = .SeriesCollection(1).Points(Counter)
            ' extract specific point value to variables:
            val1 = .SeriesCollection(1).Values()(Counter)
            val2 = .SeriesCollection(2).Values()(Counter)
            Select Case V(Counter)

                Case  val1 > val2
                'DO STUFF HERE.

                'Add other cases if needed...

            End Select
        Next
    End With
Next
End Sub

这篇关于Excel VBA，根据系列值比较选择图表颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！