确定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.

在

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屋！