excel vba 根据点值更改数据点的条形图颜色 [英] excel vba changing bar chart color for a data point based on point value

问题描述

我有一些图表，其中 X 值是文本，Y 值是数字.如果条的 Y 值小于零，我想将每个条着色为红色，如果大于或等于零，则为绿色.此外，如果条形的 X 值是净变化"，我需要条形为黄色.我按照这里以前的 StackOverflow 线程中的说明进行操作:Changing Bar colors使用基于类别标签的 VBA.

I have a some charts that in which the X Values are text and Y Values are numerical. I want to color each bar Red if the Y value for the bar is less than zero, Green if greater than or equal to zero. Also, if the X value of the bar is "NET CHANGE", I need the bar to be yellow. I followed the directions in a previous StackOverflow thread here: Changing Bar colors using VBA based on category label.

我收到运行时错误 451 未定义属性让过程并且属性获取过程未返回对象.

I'm getting Run-time error 451 Property let procedure not defined and property get procedure did not return an object.

我的代码如下:

For chartIterator = 1 To ActiveSheet.ChartObjects.count

    For pointIterator = 1 To ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator).Chart.SeriesCollection(1).Points.count
        If ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator).Chart.SeriesCollection(1).Values(pointIterator) >= 0 Then
            ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator).Chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
                RGB(146, 208, 80)
        Else
            ActiveWorkbook.Sheets("Due To Chart").ChartObjects(chartIterator).Chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
                RGB(255, 0, 0)
        End If
    Next pointIterator

Next chartIterator

错误出现在 IF 语句中.我还尝试了 .Points(pointIterator).Value，这让我得到了未为此对象定义的属性或方法"错误.

The error comes up at the IF statement. I also tried .Points(pointIterator).Value, which got me a "property or method not defined for this object" error.

对我做错了什么有任何想法吗?

Any thoughts on what I'm doing wrong?

预先感谢您的帮助.

推荐答案

您在使用 SeriesCollection(1).Values 时遇到了问题，您将其视为可以迭代的数组.相反，这是一个返回 SeriesCollection 中点的值的函数.

You are running into trouble in your use of SeriesCollection(1).Values, which you are treating as an array that you can iterate over. Instead, this is a function that return the values of the points in the SeriesCollection.

需要的是将函数的结果赋值给一个数组变量，然后遍历数组来测试数组中的值是大于零还是小于零.然后，您可以为图表点指定颜色.

What's needed is to assign the results of the function to an array variable, then iterate over the array to test whether the values in the array are greater than or less than zero. Then, you can assign the colors to the chart points.

这段代码应该可以解决问题:

This code should do the trick:

    Sub color_chart()

    Dim chartIterator As Integer, pointIterator As Integer, _
        seriesArray() As Variant

    For chartIterator = 1 To ActiveSheet.ChartObjects.Count
        seriesArray =  ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _
                       chart.SeriesCollection(1).Values

        For pointIterator = 1 To UBound(seriesArray)             

           If seriesArray(pointIterator) >= 0 Then
               ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _  
               chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
               RGB(146, 208, 80)
           Else
               ActiveWorkbook.Sheets("Sheet1").ChartObjects(chartIterator). _
               chart.SeriesCollection(1).Points(pointIterator).Interior.Color = _
               RGB(255, 0, 0)
           End If

        Next pointIterator

    Next chartIterator

    End Sub

这篇关于excel vba 根据点值更改数据点的条形图颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！