vba - 不使用 .Activate 循环遍历图表上的点

Question

我正在尝试遍历条形图并将任何高于 2 的值设为红色。下面的代码目前正在运行，但我想使用 .Activate

Sub Works()

    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim x As Integer

    Set wbk = ThisWorkbook
    Set ws = wbk.Worksheets(1)

    With ws
        .ChartObjects("Chart 1").Activate
        For x = 1 To ActiveChart.SeriesCollection(1).Points.Count
            If ActiveChart.SeriesCollection(1).Points(x).DataLabel.Caption > 2 Then
                'If above 2 make Red
                ActiveChart.SeriesCollection(1).Points(x).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            Else
                'If below or equal to 2 make Blue
                ActiveChart.SeriesCollection(1).Points(x).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
            End If
        Next x
    End With
End Sub

这是我提出的解决方案，但是当我尝试启动 For 循环时出现运行时 438 错误。我假设这只是一个语法错误，但我不知道如何在没有 .Activate 的情况下做到这一点

Sub Fails()

    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim x As Integer

    Set wbk = ThisWorkbook
    Set ws = wbk.Worksheets(1)

    With ws.ChartObjects("Chart 1")
        For x = 1 To .SeriesCollection(1).Points.Count
            If .SeriesCollection(1).Points(x).DataLabel.Caption > 2 Then
                'If above 2 make Red
                .SeriesCollection(1).Points(x).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            Else
                'If below or equal to 2 make Blue
                .SeriesCollection(1).Points(x).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
            End If
        Next x
    End With
End Sub

Answer 1

@A.S.H 在您的帖子的评论中已经向您描述了您的错误原因。 Series是 ChartObject.Chart 的属性而不是 ChartObject .

试试下面的代码，您可以利用 VBA 的图表功能来定义以下类型的变量:

Dim ChtObj As ChartObject
Dim Ser As Series
Dim SerPoint As Point

代码

Option Explicit

Sub Fails()

    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim ChtObj As ChartObject
    Dim Ser As Series
    Dim SerPoint As Point

    Set wbk = ThisWorkbook
    Set ws = wbk.Worksheets(1)
    Set ChtObj = ws.ChartObjects("Chart 1") '<-- set chart object

    With ChtObj
        Set Ser = .Chart.SeriesCollection(1)
        For Each SerPoint In Ser.Points
            If SerPoint.DataLabel.Caption > 2 Then 'If above 2 make Red
                SerPoint.Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            Else 'If below or equal to 2 make Blue
                SerPoint.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
            End If
        Next SerPoint
    End With

End Sub