读取图表标签值运行时错误（Excel VBA） [英] Reading Chart Label Values Runtime-Error (Excel VBA)

问题描述

我有下面的表格，我正在创建一个图表：

表http://i57.tinypic.com/2ptp7yq.png

添加图表后，我想为图表着色基于标签值的条形，如果标签的值为0，则标签应更改为OFF。

以下是我的代码： / p>

  Dim ChartRng As Range 
 
 Set ChartRng = Worksheets（Overview）。Range（A1： C19）
 
 Dim oChtObj As ChartObject 
 Set oChtObj = Worksheets（Overview）。ChartObjects.Add（Left：= 48，Width：= 570，Top：= 1000，Height： = 367）
 
带有oChtObj.Chart 
 .Parent.Name =Performance
 .ChartType = xlColumnClustered 
 .ApplyLayout（1）
 .SetSourceData ChartRng 
 .HasLegend = True 
 .SeriesCollection（1）.HasDataLabels = True 
 .SeriesCollection（2）.HasDataLabels = False 
 .HasTitle = True 
 .ChartTitle.Caption =调用面向时间（KPI：75％）每个代理
 .ChartTitle.Font.Size = 16 
 .ChartTitle.Font.Color = RGB（84，84，84）
 .SeriesCollection（1）.Name =CFT
 .SeriesCollection（2）.Name =KPI
 .SeriesCollection（2）.ChartType = xlLine 
 .ChartStyle = 26 
 .Axes（xlCategory）.HasMinorGridlines = False 
 .Axes（xlCategory）.HasMajorGridlines = False 
 .Axes（xlValue）.HasMinorGridlines = False 
 .Legend.LegendEntries（1） 。删除
 .SeriesCollection（2）.Border.Color = RGB（37，64，97）
 .SeriesCollection（2）.Format.Line.Weight = 3 
 .Axes（xlValue） .TickLabels.Font.Size = 9 
 .Axes（xlCategory）.TickLabels.Font.Size = 9 
 .Axes（xlValue）.TickLabels.Font.Color = RGB（77，77，77）
 .Axes（xlCategory）.TickLabels.Font.Color = RGB（77，77，77）
 .Legend.Position = xlBottom 
 .Legend.Font.Size = 9 
。 SeriesCollection（1）.DataLabels.Font.Size = 9 
 .ChartArea.Bo rder.Color = RGB（217，217，217）
 .Axes（xlValue）.MajorGridlines.Border.Color = RGB（217，217，217）
结束
 
设置oChtObj =没有
 
 Dim oPoint As Excel.Point 
 Dim sngPercente As Single 
 
对于工作表中的每个oPoint（概述）。ChartObjects（Performance ）.Chart.SeriesCollection（1）.Points 
 sngPercente = CSng（Split（oPoint.DataLabel.Caption，％）（0））
 
带oPoint 
如果sngPercente < 70然后
 .Interior.Color = RGB（255，0，0）
 End If 
如果sngPercente> 75然后
 .Interior.Color = RGB（0，176，80）
 End If 
如果sngPercente> = 70然后sngPercente< = 75然后
 .Interior.Color = RGB（148，208，80）
如果
如果sngPercente = 0则
 .DataLabel.Caption =OFF
如果
结束
 
下一个oPoint 
  

由于某些原因，我在c $ c> sngPercente = CSng（Split（oPoint.DataLabel.Caption，％）（0））：

错误http://i60.tinypic.com/rmiwdh.png

我也尝试使用 Split（oPoint.DataLabel.Text 但仍然收到错误。

值得注意的是，在Excel 2013中查看时，相同的代码运行正常，但是它在2007年提供了上述错误。

任何帮助了解错误或可能的解决方法将被高度赞赏。

解决方案

我我不知道Excel 2007是否有 Datalabel。 Caption 属性，因为我无法测试。

尝试这个

添加此行

工作表（概述）ChartObjects（Performance）。Chart.SeriesCollection（1）.HasDataLabels = True / p>

在之前为每个oPoint在工作表（概述）..... 循环，现在尝试。

如果仍然无效，那么我将删除这篇文章。

编辑

根据这个属性存在于Office 2007中

对TeamViewer的进一步测试显示，在此版本中，必须先选择Datalabel在阅读它的价值之前。所以我们只需要添加

  oPoint.DataLabel.Select 
  

before

  sngPercente = CSng（Split（oPoint.DataLabel .Caption，％）（0））
  

，一切顺利。

I have the below table for which I am creating a chart:

Table http://i57.tinypic.com/2ptp7yq.png

After adding the chart I want to color the chart bars based on the label values and if the value of the label is 0 then the label should be changed to "OFF".

Below is my code to do so:

Dim ChartRng As Range

Set ChartRng = Worksheets("Overview").Range("A1:C19")

Dim oChtObj As ChartObject
Set oChtObj = Worksheets("Overview").ChartObjects.Add(Left:=48, Width:=570, Top:=1000, Height:=367)

With oChtObj.Chart
    .Parent.Name = "Performance"
    .ChartType = xlColumnClustered
    .ApplyLayout (1)
    .SetSourceData ChartRng
    .HasLegend = True
    .SeriesCollection(1).HasDataLabels = True
    .SeriesCollection(2).HasDataLabels = False
    .HasTitle = True
    .ChartTitle.Caption = "Call Facing Time (KPI: 75%) Per Agent"
    .ChartTitle.Font.Size = 16
    .ChartTitle.Font.Color = RGB(84, 84, 84)
    .SeriesCollection(1).Name = "CFT"
    .SeriesCollection(2).Name = "KPI"
    .SeriesCollection(2).ChartType = xlLine
    .ChartStyle = 26
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlCategory).HasMajorGridlines = False
    .Axes(xlValue).HasMinorGridlines = False
    .Legend.LegendEntries(1).Delete
    .SeriesCollection(2).Border.Color = RGB(37, 64, 97)
    .SeriesCollection(2).Format.Line.Weight = 3
    .Axes(xlValue).TickLabels.Font.Size = 9
    .Axes(xlCategory).TickLabels.Font.Size = 9
    .Axes(xlValue).TickLabels.Font.Color = RGB(77, 77, 77)
    .Axes(xlCategory).TickLabels.Font.Color = RGB(77, 77, 77)
    .Legend.Position = xlBottom
    .Legend.Font.Size = 9
    .SeriesCollection(1).DataLabels.Font.Size = 9
    .ChartArea.Border.Color = RGB(217, 217, 217)
    .Axes(xlValue).MajorGridlines.Border.Color = RGB(217, 217, 217)
End With

Set oChtObj = Nothing

Dim oPoint As Excel.Point
Dim sngPercente As Single

For Each oPoint In Worksheets("Overview").ChartObjects("Performance").Chart.SeriesCollection(1).Points
sngPercente = CSng(Split(oPoint.DataLabel.Caption, "%")(0))

With oPoint
If sngPercente < 70 Then
.Interior.Color = RGB(255, 0, 0)
End If
If sngPercente > 75 Then
.Interior.Color = RGB(0, 176, 80)
End If
If sngPercente >= 70 And sngPercente <= 75 Then
.Interior.Color = RGB(148, 208, 80)
End If
If sngPercente = 0 Then
.DataLabel.Caption = "OFF"
End If
End With

Next oPoint

For some reason I get the below error at the line sngPercente = CSng(Split(oPoint.DataLabel.Caption, "%")(0)):

Error http://i60.tinypic.com/rmiwdh.png

I have also tried using Split(oPoint.DataLabel.Text but was still getting an error.

It is noteworthy that the same code was running fine when viewed in Excel 2013, however it gives the above error in 2007.

Any help understanding the reason behind the error or possible workarounds will be highly appreciated.

解决方案

I am not sure if Excel 2007 has Datalabel.Caption property as I cannot test it.

Try this

Add this line

Worksheets("Overview").ChartObjects("Performance").Chart.SeriesCollection(1).HasDataLabels = True

before the For Each oPoint In Worksheets("Overview")..... Loop and now try it.

If it still doesn't work then I will delete this post.

EDIT

As per THIS this property exists in Office 2007

Further testing on Teamviewer showed that in this version you have to select the Datalabel first before reading it's value. So all we had to do was add

oPoint.DataLabel.Select

before

sngPercente = CSng(Split(oPoint.DataLabel.Caption, "%")(0))

and everything went smoothly.

这篇关于读取图表标签值运行时错误（Excel VBA）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！