Creating a XY chart from data arrays

[spurs]

I am trying to create a XY chart from values calculated in 2 arrays X & Y

See code below

Sub Createadeadchart()
Dim X(10), Y(10) As Variant
For i = 1 To 10
X(i) = 10 + i
Y(i) = 10 * i
Next i
Charts.Add
With ActiveChart
.SeriesCollection.NewSeries
.ChartType = xlXYScatterSmoothNoMarkers
.SeriesCollection(1).XValues = X
.SeriesCollection(1).Values = Y
.Deselect
End With
End Sub

When the chart is created, the XY point values are not as I intended them to be. ie for Data point I=1 i wanted the x,y value to be (11,10), but instead the first point plotted is Point 11 with a value of (2,10). The second point plotted is Point 12 value (3,20) etc.

What code do i have to use to end up with (11,10), (12,20), (13,30) etc. after plotting.

Thanks

 

[spurs]

Follow up question

As a follow up question

Is there a limit to the number of data points that can be plotted using this technique. For some reason it wont plot more than 49 points but I need to plot about 1500 points

 

[pgc01]

Hi spurs

Try a lower limit of 1 for the arrays:

Dim X(1 To 10), Y(1 To 10) As Variant

Hope this helps
PGC

 

[spurs]

Thank you
That solved the problem.

I would now like to significantly increase the number of data points - hopefully to over 2000, but I am finding that this method only allows me to use a maximum of 61 data points (ie. I=61)

Ultimately what I am trying to do is avoid writing all of the values into a workbook as i really dont need the values - all i want is the chart, and I am finding that writing values to a workkbook slows the running of the VB code. Any suggestions?

Thanks

 

[pgc01]

Hi again

Unfortunately I don't think the problem is the number of points but the length of the string that defines them, which has a limit of 256 characters.

However, writing values to a workkbook only slows the running of the VB code if written one by one.

Try filling the array and then write the values as a block.

Run this code with 2000 points. It writes the points to the worksheet Sheet1, columns A:B as a block. It's instantaneous in my PC.

Sub Createadeadchart()
Dim XY(1 To 2000, 1 To 2) As Variant

For i = 1 To 2000
    XY(i, 1) = 10 + i
    XY(i, 2) = 10 * i
Next i
Worksheets("Sheet1").Range("A1:B2000") = XY

Charts.Add
With ActiveChart
    .SeriesCollection.NewSeries
    .ChartType = xlXYScatterSmoothNoMarkers
    .SeriesCollection(1).XValues = Worksheets("Sheet1").Range("A1:A2000")
    .SeriesCollection(1).Values = Worksheets("Sheet1").Range("B1:B2000")
    .Deselect
End With
End Sub

hope this helps
PGC

 

[spurs]

Thank you for the help.

This has resulted in my particular code in saving a lot of running time.

Previous was 15 seconds
With this change it is now 8.5 seconds

Thanks again