在图表中动态添加序列

Question

我试图创建一个excel，使用一对数据验证切换列表和一个按钮，允许用户显示模型构建的特定阶段的特定情节。要做到这一点，我需要能够动态地添加序列到图表。我在网上找到了很多关于这个的材料，但是尽管我尽了最大的努力，我还是不能让我的脚本工作。if条件中的行似乎总是生成以下错误&;运行时错误'1004':应用程序定义的或对象定义的错误&;如有任何帮助，我将不胜感激。

Sub UpdateChart()
'declaring variables'
Dim chrt As ChartObject
Dim chrtsercoll As SeriesCollection
Dim chtser As Series
'create the series collection from the chart'
Set chrt = ActiveSheet.ChartObjects(1)
'Get the series collection from the chart'
Set chrtsercoll = chrt.Chart.SeriesCollection
'delete all existing series in chart'
For Each chtser In chrtsercoll
chtser.Delete
Next chtser
'set up series in case of residual plot'
If Range("C21").Value = "residual series" Then
With chrtsercoll.NewSeries()
.Name = "=" & ActiveSheet.Name & "!B15"
.Values = "=" & ActiveSheet.Name & "!" & Evaluate(ActiveSheet.Names("RSr").Value)
End With
ActiveSheet.ChartObjects(1).Chart.ChartType = xlLine
End If
End Sub   

Answer 1

这样应该可以。

• 总是用工作表
限定范围引用等
• 如果工作表名称有空格，则在范围引用中使用引号括住工作表名称
• 你没有给XValues系列分配任何东西
• 不清楚Evaluate(ActiveSheet.Names("RSr").Value)是否有任何问题-也许你可以解释一下你在那里做什么?

Sub UpdateChart()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws.ChartObjects(1).Chart
'remove existing data
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
'set up series in case of residual plot'
If ws.Range("C21").Value = "residual series" Then
With .SeriesCollection.NewSeries
.Name = "='" & ws.Name & "'!B15" 'use quotes in case name has spaces
.XValues = 1 'you need to add something here....
.Values = "='" & ws.Name & "'!" & Evaluate(ActiveSheet.Names("RSr").Value)
End With
.ChartType = xlLine
End If
End With
End Sub

Answer 2

刚刚找到了我的评论的答案，感谢实际上注意我在excel中放入的内容，VBA告诉你当你悬停在它上面时你的函数等于什么。为了完整起见，我想把正常工作的代码放在这里。再次感谢蒂姆!

Sub UpdateChart()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws.ChartObjects(1).Chart
'remove existing data'
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
'set up series in case of residual plot'
If ws.Range("C21").Value = "residual series" Then
With .SeriesCollection.NewSeries
.Name = "='" & ws.Name & "'!B15" 'use quotes in case sheet name has spaces'
.XValues = "=" & ws.Range("AC22").Value
.Values = "=" & ws.Range("AC15").Value
End With
.ChartType = xlLine
End If
End With
End Sub