遍历所有图表并更改标签格式 VBA

Question

我正在尝试在 excel 中循环浏览所有图表并将所有标签格式更改为整数。 这是我的代码：

Sub LoopThroughCharts()
Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject
Application.ScreenUpdating = False
Application.EnableEvents = False
Set CurrentSheet = ActiveSheet
For Each sht In ActiveWorkbook.Worksheets
For Each cht In sht.ChartObjects
cht.Activate
cht.chart.FullSeriesCollection(1).DataLabels.NumberFormat = "#,##0"
Next cht
Next sht
CurrentSheet.Activate
Application.EnableEvents = True
End Sub

但是，我无法运行它。 给我的错误是运行时错误

"-2147467259(80004005)"：无法获取 的 Count 属性 数据标签类

此外，代码在单个图表上工作正常。有人可以帮忙解决这个问题吗？

Answer 1

仅当未为图表设置数据标签并且尝试更改数据标签的格式时，才会收到该错误。

试试这样...

For Each sht In ActiveWorkbook.Worksheets
For Each cht In sht.ChartObjects
cht.Chart.SetElement msoElementDataLabelOutSideEnd
cht.Chart.FullSeriesCollection(1).DataLabels.NumberFormat = "#,##0"
Next cht
Next sht

Answer 2

您需要检查每个系列HasDataLabels属性(删除不相关的代码)：

Sub LoopThroughCharts()
Dim sht As Worksheet
Dim cht As ChartObject
Dim ser As Series
For Each sht In ActiveWorkbook.Worksheets
For Each cht In sht.ChartObjects
For Each ser In cht.Chart.SeriesCollection
If ser.HasDataLabels Then
ser.DataLabels.NumberFormat = "#,##0"
End If
Next
Next cht
Next sht
End Sub