图表系列集合的变更单VBA代码

Sub ManageChartSeries()
    Dim ws As Worksheet
    Dim ch As Chart
    Dim srs As Series
    Dim newSeriesName As String
    Dim newSeriesSource As String
    Dim seriesToDelete As String
    
    ' 设置工作表和图表对象
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称
    Set ch = ws.ChartObjects("Chart 1").Chart ' 修改为你的图表名称
    
    ' 示例 1：添加一个新的系列
    newSeriesName = "新系列"
    newSeriesSource = "Sheet1!$B$2:$B$6" ' 数据源范围，根据需要修改
    AddSeries ch, newSeriesName, newSeriesSource
    
    ' 示例 2：删除一个现有的系列
    seriesToDelete = "现有系列名称" ' 要删除的系列名称
    DeleteSeries ch, seriesToDelete
    
    ' 示例 3：更新一个现有的系列的数据源
    seriesToUpdate = "要更新的系列名称" ' 需要更新的系列名称
    newSourceRange = "Sheet1!$C$2:$C$6" ' 新的数据源范围
    UpdateSeriesSource ch, seriesToUpdate, newSourceRange
    
End Sub

Sub AddSeries(ch As Chart, seriesName As String, sourceRange As String)
    With ch.SeriesCollection.NewSeries
        .Name = seriesName
        .Values = sourceRange
        .XValues = "Sheet1!$A$2:$A$6" ' X轴数据源，根据需要修改
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerSize = 8
        .Line.ForeColor.RGB = RGB(255, 0, 0) ' 线条颜色
    End With
    MsgBox "已添加系列：" & seriesName, vbInformation
End Sub

Sub DeleteSeries(ch As Chart, seriesName As String)
    Dim srs As Series
    On Error Resume Next ' 如果系列不存在，忽略错误
    Set srs = ch.SeriesCollection(seriesName)
    If Not srs Is Nothing Then
        srs.Delete
        MsgBox "已删除系列：" & seriesName, vbInformation
    Else
        MsgBox "未找到系列：" & seriesName, vbExclamation
    End If
    On Error GoTo 0
End Sub

Sub UpdateSeriesSource(ch As Chart, seriesName As String, newSourceRange As String)
    Dim srs As Series
    On Error Resume Next ' 如果系列不存在，忽略错误
    Set srs = ch.SeriesCollection(seriesName)
    If Not srs Is Nothing Then
        srs.Values = newSourceRange
        MsgBox "已更新系列 '" & seriesName & "' 的数据源到：" & newSourceRange, vbInformation
    Else
        MsgBox "未找到系列：" & seriesName, vbExclamation
    End If
    On Error GoTo 0
End Sub