Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
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 Whe=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? --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
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 < wrote: 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 Whe=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? --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
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 < wrote: Thanks very much! This code seems to make it run more cleanly, although I can still see the plot being redrawn several times. I guess I don't understand the plotting functions at all. --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exceeding Max. (32,000) Data Points in a Series
Glad to help.
- Jon Jonnyboy117 < wrote: Thanks so much Jon, this code worked like a charm. --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Series and Creating data points within a series | Excel Discussion (Misc queries) | |||
how can i color points in a plot with a third data series? | Charts and Charting in Excel | |||
Excel Chart - Too many data points in Series? | Excel Discussion (Misc queries) | |||
PERCENTILE exceeding 8191 points | Excel Worksheet Functions | |||
Points in between Data Series | Charts and Charting in Excel |