使用 VBA 遍历工作簿中的所有图表 [英] Loop through all charts in a workbook with VBA

问题描述

我正在尝试遍历工作簿中的所有图表.为什么选项 1 有效，而选项 2 无效?

I am trying to loop through all charts in a workbook. Why is option 1 working, but option 2 not?

'选项 1

For Each sht In ActiveWorkbook.Worksheets
    For Each cht In sht.ChartObjects
        MsgBox (cht.Name)
    Next cht
Next sht

'选项 2

Dim oChart As Chart
    For Each oChart In Application.Charts
        MsgBox (oChart.Name)
    Next oChart
End Sub

推荐答案

图表有两种风格:

1. 大"图表 - 整个图表表
2. 小"图表 - 嵌入在工作表中的图表对象

此代码:

Sub dural()
    Dim oChart As Chart
    For Each oChart In Application.Charts
        MsgBox oChart.Parent.Name & vbCrLf & oChart.Name
    Next oChart
End Sub

将显示有关大"的信息种类繁多.

will display information about the "big" variety.

如果您想了解有关小"的信息图表:

and if you want information on the "little" charts:

Sub dural2()
    Dim sh As Worksheet, i As Long
    For Each sh In Worksheets
        If sh.ChartObjects.Count > 0 Then
            For i = 1 To sh.ChartObjects.Count
                MsgBox sh.ChartObjects(i).Chart.Name
            Next i
        End If
    Next sh
End Sub

请注意，我们需要一个显式的 If 语句来处理没有图表的工作表，并且 .Chart 用于访问每个 ChartObjects 列表中的图表.

Note that we need an explicit If statement to handle sheets with no charts and .Chart is used to access the Chart within each of the ChartObjects list.

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