chart error with: .Values = yArray

  • Thread starter Thread starter Wm
  • Start date Start date
W

Wm

I am trying to chart an array of data but I get an error (sometimes). Here
is an example macro that exhibits the behavior:

Sub Macro1()
Dim i As Integer, n As Integer
Dim xArray() As Variant
Dim yArray() As Variant
n = 14
ReDim xArray(1 To n)
ReDim yArray(1 To n)
For i = 1 To n
xArray(i) = i
yArray(i) = Rnd()
Next i
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = xArray
ActiveChart.SeriesCollection(1).Values = yArray
End Sub

The macro works fine by setting n to any value up to 13 but errors at the
last statement with larger values for n. Can anyone fix this macro so it
works?

TIA,
William Schmidt
 
William -

Your random numbers have way too many digits. If either of these arrays approaches the magic
number of 255 characters, when written out in the series formula as {1,2,3,4} or
{0.112334421134,0.923884722394, etc.}, you will get this error. The trick is to truncate the
values, as described near the end of this web page:

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

A better trick is to put the data into a defined name in the workbook, or even more robust, into
a worksheet range, and lint the chart series to this range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top