VBA循环遍历所有图表中的所有系列 [英] VBA looping through all series within all charts

问题描述

我有一个问题，在我的VBA代码中的几个图表的循环。我99.7％肯定这是一个很容易和快速的修复，但我的大脑是不工作今天。

我想代码循环通过ActiveSheet，对于图表包含的每个数据系列，我希望它添加系列的最后一个值。在我的例子中，我有9个图表，每个有3个系列在其中（绑定更改，有些有2但我离题）。

我有以下代码

  Sub AddLastValue（）
 Dim myChartObject As ChartObject 
 Dim myChart As Chart 
 Dim mySrs As Series 
 Dim myPts As Points 
 
使用ActiveSheet 
对于每个myChartObject In .ChartObjects 
对于每个myChart In .Chart 
对于每个mySrs在.SeriesCollection 
设置myPts = .Points 
 myPts（myPts.Count）.ApplyDataLabels类型：= xlShowValue 
下一个
下一个
下一个
结束于
 $ b b End Sub 
  

如果我删除循环代码，只是做一个

 设置myPts = ActiveSheet.ChartObjects（1）.Chart。 _ 
 SeriesCollection（1）.Points 
 myPts（myPts.Count）.ApplyDataLabels type：= xlShowValue 
  

b $ b

然后它适用于那个特定的图表和系列，所以我肯定是循环，我正在搞乱。

  Sub AddLastValue（）
 Dim myChartObject As ChartObject 
 Dim mySrs As Series 
 Dim myPts As Points 
 
使用ActiveSheet 
对于每个myChartObject In .ChartObjects 
对于每个mySrs在myChartObject.Chart.SeriesCollection 
设置myPts = mySrs.Points 
 myPts（myPts.Count）.ApplyDataLabels类型：= xlShowValue 
 Next 
 Next 
 End With 
 
 End Sub 
  

I'm having an issue with the looping through of several charts in my VBA code. I'm 99.7% sure that this is a really easy and quick fix but my brain isn't working today.

I want the code to loop through every chart on the ActiveSheet, and for every data series that the chart contains I want it to add the last value of the series. In my example I have 9 charts, each with 3 series in them (bound to change, some have 2 but I digress).

I have the following code

Sub AddLastValue()
Dim myChartObject As ChartObject
Dim myChart As Chart
Dim mySrs As Series
Dim myPts As Points

With ActiveSheet
For Each myChartObject In .ChartObjects
    For Each myChart In .Chart
        For Each mySrs In .SeriesCollection
            Set myPts = .Points
            myPts(myPts.Count).ApplyDataLabels Type:=xlShowValue
        Next
    Next
Next
End With

End Sub

If I remove the looping code and just do a

Set myPts = ActiveSheet.ChartObjects(1).Chart. _
    SeriesCollection(1).Points
myPts(myPts.Count).ApplyDataLabels type:=xlShowValue

Then it works for that specific chart and series, so I'm positive it is the looping that I'm messing up.

Could someone tell me where I mess up the looping code?

解决方案

Try following code:

Sub AddLastValue()
    Dim myChartObject As ChartObject
    Dim mySrs As Series
    Dim myPts As Points

    With ActiveSheet
        For Each myChartObject In .ChartObjects
            For Each mySrs In myChartObject.Chart.SeriesCollection
                Set myPts = mySrs.Points
                myPts(myPts.Count).ApplyDataLabels Type:=xlShowValue
            Next
        Next
    End With

End Sub

这篇关于VBA循环遍历所有图表中的所有系列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！