如何通过名称选择Excel图表数据标签并进行格式化？

BigBen编写的以下代码通过.Caption属性选中特定标签并重命名：

Sub ShortenLabels()
    Dim k As Long, j As Long

    With ActiveChart
        For k = 1 To .SeriesCollection.Count
            For j = 1 To .SeriesCollection(k).Points.Count
                With .SeriesCollection(k).Points(j).DataLabel
                    .Caption = Replace(.Caption, "AAAA", "AA")
                    .Caption = Replace(.Caption, "BBBB", "BB")
                    .Caption = Replace(.Caption, "CCCC", "CC")
                End With
            Next j
        Next k
    End With
End Sub

现在需要实现：针对.Caption为"AA"的数据标签，修改其字体颜色。

最初尝试的代码写法有误：

Sub FormatLabelWithCertainCaption()
    Dim k As Long, j As Long

    With ActiveChart
        For k = 1 To .SeriesCollection.Count
            For j = 1 To .SeriesCollection(k).Points.Count
                With .SeriesCollection(k).Points(j).DataLabel
                    With .Caption = "AA"
                        ' change font color
                    End With
                End With
            Next j
        Next k
    End With
End Sub

以下指定数据点编号的代码可正常运行：

With ActiveChart.FullSeriesCollection(1).Points(6).DataLabel
    With .Format.TextFrame2.TextRange.Font.Fill
        .ForeColor.RGB = vbRed
    End With
End With

但使用If .Caption = "AA" Then判断的版本无效果：

Sub FormatLabelWithCertainCaption_v2()
    Dim k As Long, j As Long

    With ActiveChart
        For k = 1 To .SeriesCollection.Count
            For j = 1 To .SeriesCollection(k).Points.Count
                With .SeriesCollection(k).Points(j).DataLabel
                    If .Caption = "AA" Then                      
                        .Format.TextFrame2.TextRange.Characters. _
                            Font.Fill.ForeColor.RGB = RGB(0, 0, 255)
                    End If
                End With
            Next j
        Next k
    End With
End Sub

解决办法 #

出现无效果的情况，通常有以下几个原因和对应修复方式：

1. 数据标签未启用：确保所有数据点的DataLabel都已开启，否则循环时会跳过未显示的标签。可以在循环内先判断.HasDataLabel是否为True，再进行后续操作：

Sub FormatLabelWithCertainCaption_Fixed()
    Dim k As Long, j As Long

    With ActiveChart
        For k = 1 To .SeriesCollection.Count
            For j = 1 To .SeriesCollection(k).Points.Count
                With .SeriesCollection(k).Points(j)
                    ' 先检查是否有数据标签
                    If .HasDataLabel Then
                        With .DataLabel
                            ' 匹配Caption时注意空格或隐藏字符，可使用Trim()处理
                            If Trim(.Caption) = "AA" Then                      
                                .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 255)
                                ' 也可以用传统的Font.Color属性，兼容性更好
                                '.Font.Color = vbBlue
                            End If
                        End With
                    End If
                End With
            Next j
        Next k
    End With
End Sub

2. Caption存在隐藏字符或空格：有些情况下，数据标签的Caption可能包含看不见的空格、换行符等，直接用=匹配会失败，使用Trim()去除首尾空格，或者用InStr()判断是否包含目标文本：

' 用InStr判断包含"AA"的情况
If InStr(Trim(.Caption), "AA") > 0 Then
    ' 执行格式修改
End If

3. 使用传统Font属性替代TextFrame2：TextFrame2是Office 2007及以后的新对象，部分旧版本或特定图表类型可能支持不佳，改用传统的.Font.Color属性兼容性更强：

If Trim(.Caption) = "AA" Then
    .Font.Color = RGB(0, 0, 255) ' 等同于vbBlue
End If

内容的提问来源于stack exchange，提问作者user23636411