在 Excel VBA 中确定点的值 [英] Determining the value of a point in Excel VBA

问题描述

我试图让图表中的点在某些值参数范围内改变颜色(即，>1 是绿色，<1 是红色，其他任何东西都是蓝色).我无法确定如何让 VBA 为我提供任何给定点的价值.

I am trying to have the points in a chart change color if they are within certain value paramaters (i.e., >1 is green, <1 is red, anything else is blue). I cannot determine how to get VBA to give me the value of any given point.

在这个线程中，之前回答过，答案(在其他方面非常有帮助)表示 points(num).value 将返回该点的值.但是，我在执行此操作时收到一条错误消息，并且在其他任何在线或 VBA 帮助中都找不到与此对应的方法.有没有其他人在这方面取得过任何成功?

In this thread, previously answered, the answer (very helpful in other ways) indicates that points(num).value will return the value at the point. However, I am getting an error message doing this, and nowhere else online or in the VBA help can I find a method that corresponds to this. Has anyone else had any success with this?

这是给我带来麻烦的代码片段:

Here's the snippet of code giving me trouble:

For Count = 1 To 7
    If Worksheets("Sheet1").ChartObjects("ChartName").Chart.SeriesCollection(1).Points(Count).Value > 1 Then
    '... do stuff

由于数据存储在数据集中的方式，直接从图表中获取值肯定会更好.我可以使用数据集本身找出解决方法，但我宁愿避免这种情况.

Because of the way the data are stored in the dataset, it would definitely be better to get the value from the chart directly. I could figure out a workaround using the dataset itself, but I would rather avoid that.

推荐答案

Sub Tester()

Dim cht As Chart, s As Series, p As Point
Dim vals, x As Integer

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set s = cht.SeriesCollection(1)

    vals = s.Values

    For x = LBound(vals) To UBound(vals)
      If vals(x) > 10 Then
        With s.Points(x)
            .MarkerBackgroundColor = RGB(255, 0, 0)
            .MarkerForegroundColor = RGB(255, 0, 0)
        End With
      End If
    Next x

End Sub

这篇关于在 Excel VBA 中确定点的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！