ExcelVBA：将散点图作为对象添加到工作表-ExcelVBA:Addascattercharttoworksheetasobject

I am looking to create a scatter chart that, on button press, creates a scatter chart in Sheet 1 and uses A2:A11 as the x values and B2:B11 as the y values.

我打算创建一个散点图，按下按钮，在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 xlLocationAsObject doesn'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$2:$A$11"
        .SeriesCollection(1).Values = "=Sheet1!$B$2:$B$11"

        '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

2 个解决方案

ActiveWorkbook.Charts.Add
With ActiveWorkbook.ActiveChart

Dim sh As Worksheet
Dim chrt As Chart

Set sh = ActiveWorkbook.Worksheets("Sheet1")
Set chrt = sh.Shapes.AddChart.Chart
With chrt

    .ChartArea.Left
    .ChartArea.Top 
    .ChartArea.Height
    .ChartArea.Width 

.Location Where:=xlLocationAsObject, Name:="Sheet1"