如何使用 VBA 从 Excel 图表中删除一个系列

How delete a series from an Excel chart using VBA

提问by getting-there

I'm trying to delete the empty series from a chart in Excel 2003 using VBA. I've seen that others have had this issue in the past and I have tried all methods mentioned in their posts but have been unable to find anything that works consistently.

我正在尝试使用 VBA 从 Excel 2003 中的图表中删除空系列。我已经看到其他人过去遇到过这个问题，我已经尝试了他们帖子中提到的所有方法，但一直无法找到任何一致的方法。

The chart has 14 series in it and anywhere between 3 or 9 of them can be empty. The empty ones are always between Series 4 - 12.

图表中有 14 个系列，其中 3 到 9 个可以是空的。空的总是在系列 4 - 12 之间。

I've tried a few variations of code but this is primarily it:

我尝试了一些代码变体，但主要是这样：

Sheets("chart-1").Select
ActiveChart.PlotArea.Select
For i = 12 To 4 Step -1
    Dim theSeries As Series
    MsgBox (ActiveChart.SeriesCollection(i).Name)
    Set theSeries = ActiveChart.SeriesCollection(i)
    MsgBox (theSeries.Name)
    theSeries.Delete
Next

I can run it successfully once for a chart, but all subsequent cycles fail with a Unable to get the Name property of the Series classerror. It fails on the call to .Name.

我可以为图表成功运行一次，但所有后续周期都失败并出现Unable to get the Name property of the Series class错误。它在调用 .Name 时失败。

I been able to get it work by inserting integers directly, but it will only run once for all integers except 1. It run it multiple times for Series(1).

我能够通过直接插入整数来使其工作，但它只会对除 1 以外的所有整数运行一次。它为Series(1).

For instance if I simply call: ActiveChart.SeriesCollection(1).Delete, then the series is deleted, but if I then run it with another integer (4, 9, 12) it won't run. It will work again for 1, but only 1. It will also work once with other integers (say 4), but all subsequent calls will fail even if I change the integer to 1 or keep it as 4, or change it to some other number.

例如，如果我简单地调用：ActiveChart.SeriesCollection(1).Delete，那么该系列将被删除，但如果我再用另一个整数 (4, 9, 12) 运行它，它就不会运行。它将再次适用于 1，但仅适用于 1。它也适用于其他整数（例如 4），但即使我将整数更改为 1 或将其保留为 4，或者将其更改为其他一些其他整数，所有后续调用也会失败数字。

The behaviour is really quite strange.

这种行为真的很奇怪。

Any ideas would be greatly appreciated. I can't simply call ActiveChart.SeriesCollection(1).Deleterepeatedly because the first 3 series are always non-empty.

任何想法将不胜感激。我不能简单地ActiveChart.SeriesCollection(1).Delete重复调用，因为前 3 个系列总是非空的。

Thanks.

谢谢。

** Update **

** 更新 **

I just ran a test manually executing the following:

我只是手动运行了一个测试，执行以下操作：

Sheets("ch-v2-12mth").Select
ActiveChart.PlotArea.Select
MsgBox (ActiveChart.SeriesCollection(1).Name)

I cycled through the SeriesCollection trying the numbers 1 - 16 (there are only 14 Series in the chart) to see the result. 1 - 3 worked fine 4 - 13 errored with Unable to get the Name property of the Series class14 worked fine 15 - 16 errored with Method 'SeriesCollection' of object '_Chart' failed<- not surprising given the number of series in the chart.

我在 SeriesCollection 中循环尝试数字 1 - 16（图表中只有 14 个系列）以查看结果。1 - 3 工作正常 4 - 13 错误Unable to get the Name property of the Series class14 工作正常 15 - 16 错误Method 'SeriesCollection' of object '_Chart' failed<- 鉴于图表中的系列数量，这并不奇怪。

This type of behaviour makes me think that there is a bug with Excel. Any other ideas?

这种行为让我认为 Excel 存在错误。还有其他想法吗？

回答by steveo40

There are bugs in Excel when you delete allthe series from a chart. My workaround is to always leave at least one series (even if it has no data in it) in the chart. That seems to work for me.

当您从图表中删除所有系列时，Excel 中存在错误。我的解决方法是始终在图表中至少保留一个系列（即使其中没​​有数据）。这似乎对我有用。

Just thought of another thing. When you delete a series, the indexes of all the remaining series get reduced by one, so you can't delete them by looping from 1 to the number of series. What you can do instead is have a do loop that deletes them until the SeriesCollection.Count = 0(or 1, see my comments earlier). Or a for loop that iterates backwards and always deletes the last series (i.e. SeriesCollection(SeriesCollection.Count).Delete

只是想到了另一件事。当您删除一个系列时，所有剩余系列的索引都会减一，因此您不能通过从 1 到系列数循环来删除它们。您可以做的是使用 do 循环删除它们，直到SeriesCollection.Count = 0（或 1，请参阅我之前的评论）。或者向后迭代并始终​​删除最后一个系列的 for 循环（即SeriesCollection(SeriesCollection.Count).Delete

回答by Orin Moyer

You cannot remove all series or the chart will remove itself. what I do to work around this is to rename all exisiting series; then enter your code to build new stuff; then run another snippet to remove the series you renamed

您不能删除所有系列，否则图表会自行删除。我为解决这个问题所做的是重命名所有现有系列；然后输入你的代码来构建新的东西；然后运行另一个片段以删除您重命名的系列

'rename existing series
With ActiveChart
 DoEvents
    For i = .FullSeriesCollection.Count To 1 Step -1
        .FullSeriesCollection(i).Name = "remove" & i
    Next i
End With

'your code here to build new charts

'last piece of code to remove the earlier series marked for deletion
With ActiveChart
 DoEvents
    For c = .SeriesCollection.Count To 1 Step -1
        If .SeriesCollection(c).Name Like "*Series*" Then .SeriesCollection(c).Delete
    Next c
End With
'also, you need to step backwards because each time you remove a series it will re-index

回答by Jon Peltier

You can simplify your code to this:

您可以将代码简化为：

Sheets("chart-1").Select
For i = 12 To 4 Step -1
    MsgBox "Series " & i & ": """ ActiveChart.SeriesCollection(i).Name & """"
    ActiveChart.SeriesCollection(i).Delete
Next

I don't know why the code was not working for you, but simpler is usually better. And you don't need to know the series name to delete a series.

我不知道为什么代码不适合你，但通常更简单更好。并且您无需知道系列名称即可删除系列。