Excel 宏用 VBA 绘制 xyscatter 图

Excel 宏用 VBA 绘制 xyscatter 图

我正在尝试创建一个 xy 图,其中 x 值是时间轴(A 列),y 值在其他列中。(在示例中只有 C 和 D)当我从下面尝试我的代码时,我得到了一个带有时间轴(即 A 列)的图表,而 y 值是所有其他列(B、C、D、E、...),这是不想要的。

我可以看到 .seriescollection(1) 和 (2) 覆盖了默认的 y 值(因为我的范围较小),但所有其他值(B、D、E 列……)仍然保留在图中。

有什么想法吗?提前致谢!

    Sub grafieken()
'
' grafieken Macro
'
Dim sh As Worksheet
Dim chrt As Chart
Dim naaaam As String

naaam = ActiveWorkbook.ActiveSheet.Name

Set sh = ActiveWorkbook.Worksheets(naaam)
Set chrt = sh.Shapes.AddChart.Chart
With chrt
    'Data?
    .ChartType = xlXYScatter
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = sh.Range("$C$1")
    .SeriesCollection(1).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(1).Values = sh.Range("$C$2:$C$11")
    .SeriesCollection(2).Name = sh.Range("$D$1")
    .SeriesCollection(2).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(2).Values = sh.Range("$D$2:$D$11")

    'Titles?
    .HasTitle = True
    .ChartTitle.Characters.Text = naaam

End With

End Sub

答案1

对我来说,当我添加SetSourceData

Sub grafieken()
Dim sh As Worksheet
Dim chrt As Chart
Dim naaaam As String

naaam = ActiveWorkbook.ActiveSheet.Name

Set sh = ActiveWorkbook.Worksheets(naaam)
Set chrt = sh.Shapes.AddChart.Chart
With chrt
    'Data?
    .SetSourceData Source:=Range("Sheet1!$C$1:$D$11")
    .ChartType = xlXYScatter
    .SeriesCollection(1).Name = sh.Range("$C$1")
    .SeriesCollection(1).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(1).Values = sh.Range("$C$2:$C$11")
    '.SeriesCollection.NewSeries ' removed
    .SeriesCollection(2).Name = sh.Range("$D$1")
    .SeriesCollection(2).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(2).Values = sh.Range("$D$2:$D$11")

    'Titles?
    .HasTitle = True
    .ChartTitle.Characters.Text = naaam
End With
End Sub

相关内容