VBA修改图表系列值报错：对象不支持该属性或方法

错误原因 #

你添加的series.Values(point.Index) = newValue触发错误的核心原因：

• Series.Values是返回数组的属性，不支持通过单个索引直接赋值修改
• 更关键的是，直接修改系列值会联动修改工作表中的原始数据源，这通常不是合理选择——我们只需要改变显示效果，而非实际数据

正确实现思路 #

不用修改系列的实际数值，而是通过设置数值轴的数字格式，让坐标轴自动显示带单位的简化数值，同时同步调整数据标签格式，这样既保留原始数据，又能实现PPT展示需要的简洁效果。另外原代码中currencySymbol和decimalPlaces变量未赋值，需要补充输入逻辑。

修改后的完整代码 #

Sub FormatSpecificChartSeries()
    Dim ws As Worksheet
    Dim cht As Chart
    Dim chtName As String
    Dim seriesName As String
    Dim levelChoice As Integer
    Dim multiplier As Double
    Dim currencySymbol As String
    Dim levelSymbol As String
    Dim decimalPlaces As Integer
    Dim series As Series
    Dim point As Point
    Dim numFormat As String

    Set ws = ActiveSheet
    chtName = InputBox("Enter the name of the chart:")
    If chtName = "" Then Exit Sub
    
    On Error Resume Next
    Set cht = ws.ChartObjects(chtName).Chart
    On Error GoTo 0
    If cht Is Nothing Then
        MsgBox "Chart not found."
        Exit Sub
    End If
    
    seriesName = InputBox("Enter the name of the series:")
    If seriesName = "" Then Exit Sub
    
    Set series = Nothing
    For Each s In cht.SeriesCollection
        If s.Name = seriesName Then
            Set series = s
            Exit For
        End If
    Next s
    
    If series Is Nothing Then
        MsgBox "Series not found in the specified chart."
        Exit Sub
    End If
    
    ' 补充货币符号和小数位数的输入
    currencySymbol = InputBox("Enter currency symbol (e.g., $, €):", , "$")
    decimalPlaces = Application.InputBox("Enter number of decimal places:", Type:=1, Default:=0)
    
    levelChoice = Application.InputBox("Select a Level:" & vbCrLf & _
                                       "1: Thousand" & vbCrLf & _
                                       "2: Million" & vbCrLf & _
                                       "3: Billion", Type:=1)
    
    Select Case levelChoice
        Case 1
            multiplier = 1000
            levelSymbol = "K"
            ' 设置坐标轴数字格式：自动将数值除以1000并显示K
            numFormat = currencySymbol & "#,##0." & String(decimalPlaces, "0") & ",K"
        Case 2
            multiplier = 1000000
            levelSymbol = "MM"
            numFormat = currencySymbol & "#,##0." & String(decimalPlaces, "0") & ",,MM"
        Case 3
            multiplier = 1000000000
            levelSymbol = "B"
            numFormat = currencySymbol & "#,##0." & String(decimalPlaces, "0") & ",,,B"
        Case Else
            MsgBox "Invalid level selection."
            Exit Sub
    End Select
    
    ' 设置数值轴的数字格式，实现坐标轴显示简化值
    cht.Axes(xlValue).TickLabels.NumberFormat = numFormat
    
    ' 更新数据标签格式
    For Each point In series.Points
        If IsNumeric(point.DataLabel.Text) Then
            Dim originalValue As Double
            originalValue = CDbl(point.DataLabel.Text)
            Dim newValue As Double
            newValue = originalValue / multiplier
            point.DataLabel.Text = currencySymbol & Format(newValue, "0." & String(decimalPlaces, "0")) & " " & levelSymbol
        End If
    Next point
End Sub

关键修改说明 #

• 补充必要输入：添加了货币符号和小数位数的输入框，完善用户交互逻辑
• 坐标轴格式设置：通过cht.Axes(xlValue).TickLabels.NumberFormat设置数值轴的数字格式，Excel的数字格式支持用逗号自动缩放数值：
  • 1个逗号：除以1000（千位）
  • 2个逗号：除以1000000（百万位）
  • 3个逗号：除以1000000000（十亿位）
• 移除错误赋值代码：删掉了series.Values(point.Index) = newValue，避免修改原始数据，同时解决了属性不支持的错误
• 数据标签同步：保持数据标签的格式和坐标轴一致，确保显示统一

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