Exceeding Max. (32,000) Data Points in a Series

[Jonnyboy117]

I have a weird problem when trying to plot an XYScatterSmooth series
from imported tabulated data. The chart is generated properly, but it
seems to be drawing itself multiple times, and after several seconds I
get a runtime error 1004 with this description:

Run-time error '1004':

The maximum number of data points you can use in a data series for a
2-D chart is 32,000. If you want to use more than 32,000 data points,
you must create two or more series.


There are actually only 4,332 data points in this instance, so my guess
is that Excel is trying to plot the same data points over and over in
the same series on the same chart...that's why the series is redrawn a
few times, until the number of data points exceeds 32,000. What's odd
is that I have no loop in my macro, so I don't understand why the input
data would be stored multiple times in the series collection. Here is
my macro code for reference:

Sub AltPlot()
'
' AltPlot Macro
' Macro recorded 7/6/2004 by Jonathan G. Metts
'

'
Dim TimeRange As Range
Dim AltRange As Range

ChDir _
"C:\Program Files\Microsoft Visual Studio\Common\MSDEV98\My
Projects\msic2003_mod"
Workbooks.OpenText Filename:= _
"C:\Program Files\Microsoft Visual Studio\Common\MSDEV98\My
Projects\msic2003_mod\rangedata.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:= _
Array(Array(0, 1), Array(12, 1), Array(28, 1), Array(44, 1),
Array(60, 1))

Set TimeRange = Worksheets("rangedata").Range("B:B")
Set AltRange = Worksheets("rangedata").Range("C:C")

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection(1).Select
Selection.Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.SeriesCollection(1).XValues = TimeRange
ActiveChart.SeriesCollection(1).Values = AltRange
ActiveChart.Location Where:=xlLocationAsNewSheet,
Name:="MSIC_PlotSheet"
With ActiveChart
..HasTitle = True
..ChartTitle.Characters.Text = "Missile Altitude Plot"
..Axes(xlCategory, xlPrimary).HasTitle = True
..Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time
(sec)"
..Axes(xlValue, xlPrimary).HasTitle = True
..Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Altitude
(ft.)"
End With
ActiveChart.Axes(xlCategory).Select
' With Selection.Border
' .Weight = xlHairline
' .LineStyle = xlAutomatic
' End With
With Selection
..MajorTickMark = xlNone
..MinorTickMark = xlNone
..TickLabelPosition = xlNextToAxis
End With
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlXValues)
..MinimumScaleIsAuto = True
..MaximumScaleIsAuto = True
..MinorUnitIsAuto = True
..MajorUnitIsAuto = True
..Crosses = xlAutomatic
..ReversePlotOrder = False
..ScaleType = xlLinear
..DisplayUnit = xlNone
End With
End Sub


Any ideas?

 

[Jonnyboy117]

Interesting follow-up:

I tried using the following code:

TimeRange.ClearContents
AltRange.ClearContents

...placed after the series collection definitions. The chart was stil
redrawn several times before stopping with the same error, but the lin
was only drawn once and then the chart was cleared and left blank i
subsequent redraws. So I'm positive that there is some kind of loopin
effect, I just don't understand what could be causing it

 

[Jonnyboy117]

I managed to supress the runtime error by commenting out the section o
code which manipulates the X-axis scale and other properties. The cod
is still mysteriously looping, but I guess now it doesn't loop enoug
times to exceed 32,000 data points. This is a tenuous solution a
best, and I'd still very much appreciate some insight as to why thi
code is acting so strangely

 

[Jon Peltier]

You're telling Excel to plot the whole column, which is twice the limit
for a chart series. Change your range definitions to:

Set TimeRange = Worksheets("rangedata").Range("B1")
Set TimeRange = TimeRange.Resize(TimeRange.End(xlDown).Row)

Set AltRange = Worksheets("rangedata").Range("C1")
Set AltRange = AltRange.Resize(AltRange.End(xlDown).Row)

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

 

[Jonnyboy117]

Thanks very much! This code seems to make it run more cleanly, althoug
I can still see the plot being redrawn several times. I guess I don'
understand the plotting functions at all

 

[Jon Peltier]

If you have multiple series in your chart, this will happen. You can
hide the redrawing if you put your code between these two lines:

Application.ScreenUpdating = False

Application.ScreenUpdating = True

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

 

[Jonnyboy117]

Thanks so much Jon, this code worked like a charm

 

[Jon Peltier]

Glad to help.

- Jon