Problem with .Values

[garnote]

Hi all,

I have a Sub for construct graphic but
if the number of vx is too large, ERROR
at .Values = Y. Why ?

Thank you for your help,

Serge

Sub ConstruireGraphiqueParTableauxVBA()
Dim X() As Double
Dim Y() As Double, i As Long, vx As Double
ReDim X(i), Y(i)
Application.ScreenUpdating = False
ici = ActiveSheet.Name
'*********************************************
For vx = -15 To 15
'If the number of vx is too large, ERROR
'at .Values = Y. Why ?
'*********************************************
X(i) = vx
Y(i) = vx ^ 2
i = i + 1
ReDim Preserve X(i), Y(i)
Next vx
Charts.Add
With ActiveChart
.ChartType = xlXYScatter
.Location Where:=xlLocationAsObject, Name:=ici
End With
Set ns = ActiveChart.SeriesCollection.NewSeries
With ns
.XValues = X
.Values = Y
End With
Application.ScreenUpdating = True
End Sub

 

[Jon Peltier]

Allo Serge -

You can assign an array to .Values (or .XValues), but eventually Excel
converts it into a string literal in the series formula. There's a
limit of around 255 characters (a little shorter, maybe 248 to 252, I
used to know) for each part of the series formula.

You have a few choices. Easiest is to dump the array into a worksheet
range, and plot the worksheet range. Another option is to build your
own literal array, limiting the significant digits in your values, as I
describe near the bottom of this page:

http://www.geocities.com/jonpeltier/Excel/ChartsHowTo/DelinkChartData.html

Finally, you could put the array into a defined name in the worksheet,
and chart the name. You're still linked to the workbook, but not to a
particular worksheet range.

- Jon