-1

This is a part of my code:

ActiveSheet.Shapes.Addchart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Clear

This works just fine, however, when I use a chart with over 32,000 lines, this results in error. Can I somehow stop excel from trying to chart the data before it actually knows which data I want to use?

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
Birimaratoa
  • 3
  • 6
  • If you're adding a chart while you have a set of data selected on the sheet, excel may think that's what you want to plot, and automatically chart it. Better to make sure you don't have a huge range selected before adding the chart. – Tim Williams Jan 16 '15 at 16:10
  • Not really the issue but thanks for the comment - I also tried manually selecting the data before adding the plot, but it still somhow tries to use all of the data sheet... – Birimaratoa Jan 17 '15 at 13:35

2 Answers2

0

Absolutely there is. You're missing the step where you tell the chart what information you want to use.

Take this code for example. It adds a chart (takes the additional step of sizing it), then sets the source data at the same time, as well as the chart type.

Sub AddChartObject()
'
    With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
        .Chart.ChartType = xlXYScatterLines
    End With
End Sub

Code from Jon Peltier's website:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
  • Yeah, I found this one, but since there is a large number of points, I wanted to add the chart to its own sheet. So I used this code, then used a command to move it to its own sheet, but its very slow when dealing with a large file.... can this be tweaked to create blank chart on its own sheet without any data? – Birimaratoa Jan 17 '15 at 13:38
-1

Well I ended up with this:

 Sheets("List2").Select
        With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.ChartType = xlXYScatter
        .Chart.Location Where:=xlLocationAsNewSheet
        End With

basically just switching to another sheet, the solution above still results in "no more than 32k points in plot" error... But I don't really like this solution :(

Birimaratoa
  • 3
  • 6