vba 我如何获得 charts.add 命令以提供空图表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19538322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get charts.add command to deliver an empty chart?
提问by JazZeus
The command charts.addin VBA adds a new chart object to the workbook. I think hitting F11in Excel does exactly the same.
charts.addVBA 中的命令将新图表对象添加到工作簿。我认为F11在 Excel 中击球的效果完全相同。
The problem is, that this command uses the data around the at that point selected worksheet and cell to populate the chart. If there is no data or no usable data, it returns an empty chart.
问题是，此命令使用当时所选工作表和单元格周围的数据来填充图表。如果没有数据或没有可用数据，则返回一个空图表。
My question is: "How can I force the command to deliver an empty chart?". I intend to populate the chart with VBA code thereafter.
我的问题是：“如何强制命令提供空图表？” . 我打算此后用 VBA 代码填充图表。
A simple answer to the question is to create a new empty worksheet and select cell A1 on that worksheet and then create a new chart, but that is a rather ugly solution. Any help on elegant solutions would be appreciated.
该问题的一个简单答案是创建一个新的空工作表并选择该工作表上的单元格 A1，然后创建一个新图表，但这是一个相当丑陋的解决方案。任何有关优雅解决方案的帮助将不胜感激。
回答by Kazimierz Jawor
Try to add additional line which will immediately remove data right after the chart appears:
尝试添加额外的行，它会在图表出现后立即删除数据：
Charts.Add
ActiveChart.ChartArea.Clear
EDITAlternative solution, but this will jump back to data sheet:
编辑替代解决方案，但这将跳回数据表：
'select last cell in the sheet which is usually empty
Dim tmpSel As Range
Set tmpSel = Selection
Cells(Rows.Count, Columns.Count).Select
'add chart
Charts.Add
'back to base sheet and select range previously selected
tmpSel.Parent.Activate
tmpSel.Select
回答by TheDiesel
I had the same issue. I tried using the ActiveChart.ChartArea.Clearproperty and it resulted in crashes. Then I tried ActiveChart.ChartArea.ClearContentsafter adding the chart and it gave me the desired result, which was a blank chart that I could add series to.
我遇到过同样的问题。我尝试使用该ActiveChart.ChartArea.Clear属性，但导致崩溃。然后我ActiveChart.ChartArea.ClearContents在添加图表后尝试，它给了我想要的结果，这是一个我可以添加系列的空白图表。
回答by PavDub
I have just spent a pretty tough time with solving this. Whatever I did, the bloody excel took some data from somewhere, which was really anoying. The .ChartArea.Clear method resulted in a crash whenever I manipulated the chart afterwards (even filled with new series)
我刚刚花了很长时间来解决这个问题。无论我做什么，该死的 excel 都会从某个地方获取一些数据，这真的很烦人。.ChartArea.Clear 方法在我之后操作图表时导致崩溃（甚至填充了新系列）
Here is my final solution from a real working project (a fragment, sorry for some user-specific variables)
这是我来自一个真实工作项目的最终解决方案（一个片段，对于某些用户特定的变量感到抱歉）
Dim Chrt as Chart
' This is "the elegant" method
' Parameters of Add do not actually matter, we will specify all details later
Set Chrt = MySheet.ChartObjects.Add(0, 0, 100, 100).Chart
' And here we start to fill the empty (!) Chart with some useful series... :)
With Chrt
' Specifying chart type
.ChartType = xlXYScatterLinesNoMarkers
' Positioning (Page is a range - fragment of my code)
With .Parent
.Top = Page(3, 1).Top
.Left = Page(3, 1).Left
.Width = Page.Width
.Height = (Page.Height - (Page(4, 1).Top - Page(1, 1).Top)) ' / 2
End With
' Adding a new serie
With .SeriesCollection.NewSeries
.Name = "D" & Work.Cells(iFileRow, 2)
.XValues = Range(MySheet.Cells(2, 1), MySheet.Cells(DataEnd, 1))
.Values = Range(MySheet.Cells(2, 10), MySheet.Cells(DataEnd, 10))
.MarkerStyle = -4142
' Formating the series
With .Format.Line
...
回答by Jon Peltier
Excel tries to populate a brand new chart with the selected data, or with the block of data that contains the active cell.
Excel 尝试使用所选数据或包含活动单元格的数据块填充全新的图表。
Select a blank cell in the middle of an unused range. Without data to plot, Excel will insert a blank chart.
在未使用的范围中间选择一个空白单元格。如果没有要绘制的数据，Excel 将插入一个空白图表。
回答by ron
i like the ActiveChart.ChartArea.Clearanswer.
我喜欢这个ActiveChart.ChartArea.Clear答案。
I had been doing the following, back when i did a macro in excel 2003. It will manually delete all series in the selected chart.
当我在 excel 2003 中执行宏时，我一直在执行以下操作。它将手动删除所选图表中的所有系列。
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet
' manually delete every series that may be in a new chart
On Error GoTo 30
yy = ActiveChart.SeriesCollection.Count
For xx = yy To 1 Step -1
ActiveChart.SeriesCollection(xx).Delete
30 Next xx
