Excel VBA：将散点图作为对象添加到工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13794210/
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
Excel VBA: Add a scatter chart to worksheet as object
提问by Josh I
I am looking to create a scatter chart that, on button press, creates a scatter chart in Sheet 1and uses A2:A11as the xvalues and B2:B11as the yvalues.
我期待创造散点图的是，按下按钮，在创建散点图Sheet 1和用途A2:A11的x值，并B2:B11为y值。
Using code at the bottom allows me to create a scatter chart based off of the values in A1:B3(got this from here). Its close, but not exactly what I'm looking for. How can I tweak this to suit my needs?
使用底部的代码允许我根据中的值创建一个散点图A1:B3（从这里得到这个）。它很接近，但不完全是我正在寻找的。我该如何调整它以满足我的需求？
I got it set up now so the chart is made, based on the values I want, but I can not get it to appear as an object in Sheet 1. How do I do this? .Location xlLocationAsObjectdoesn't seem to work.
我现在已经设置好了，所以图表是根据我想要的值制作的，但我无法让它在Sheet 1. 我该怎么做呢？.Location xlLocationAsObject似乎不起作用。
Private Sub chartButton_Click()
ActiveWorkbook.Charts.Add
With ActiveWorkbook.ActiveChart
'Data?
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "=""Scatter Chart"""
.SeriesCollection(1).XValues = "=Sheet1!$A:$A"
.SeriesCollection(1).Values = "=Sheet1!$B:$B"
'Location
'DON'T KNOW WHAT TO PUT HERE
'.location xlLocationAsObject doesn't work!
'Titles
.HasTitle = True
.ChartTitle.Characters.Text = "Scatter Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X values"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y values"
.Axes(xlCategory).HasMajorGridlines = True
'Formatting
.Axes(xlCategory).HasMinorGridlines = False
.Axes(xlValue).HasMajorGridlines = True
.Axes(xlValue).HasMinorGridlines = False
.HasLegend = False
End With
End Sub
回答by chris neilsen
Your code as written adds a chart as a Chart Sheet, not as a chart on a Worksheet
您编写的代码将图表添加为Chart Sheet，而不是作为图表Worksheet
Try this:
尝试这个：
Replace
代替
ActiveWorkbook.Charts.Add
With ActiveWorkbook.ActiveChart
with
和
Dim sh As Worksheet
Dim chrt As Chart
Set sh = ActiveWorkbook.Worksheets("Sheet1")
Set chrt = sh.Shapes.AddChart.Chart
With chrt
Then you can control its position and size with
然后你可以控制它的位置和大小
.ChartArea.Left
.ChartArea.Top
.ChartArea.Height
.ChartArea.Width
回答by Jon Peltier
When you use Chart.Location, you need to specify the location:
使用时Chart.Location，需要指定位置：
.Location Where:=xlLocationAsObject, Name:="Sheet1"
