Posted to microsoft.public.excel.charting
|
|
Excel Chart Series Values Property Size Limitation
Hi Peter T,
A Name Array sounds like the way to go but I have to admit that I'm not
familiar with these. Can you point me in the direction of some sample code or
relevant article?
Thanks,
Pete
"Peter T" wrote:
Each 'segment' of the Series formula is limited to an absolute maximum of
255 characters (can be a bit less), incl curly brackets, commas and values.
Depending on your data, rounding dp may help reduce the overall length
sufficiently.
Otherwise, one way to put large amounts of data into a series is with 'Name
arrays'. The limit in XL2000 is 5000+ points (much more in later versions).
Cells are not required, indeed the workbook could contain only chart-sheets.
If not, is
it possible to create an invisible range?
Of course, chart data could be in cells in hidden columns, outside the
ScrollArea, or on a hidden sheet (eg xlSheetVeryHidden).
Regards,
Peter T
"Pete" wrote in message
...
Hi,
I am trying to create a new series in an Excel chart using code (it's
actually VB.Net but I think it's almost identical in VBA). After creating
a
series using Series1 = Chart1.SeriesCollection.NewSeries, I am trying to
set
the values property of the series by assigning an array (in VB.Net this is
an
array of Doubles) e.g. Series1.Values = DataArray1. For charts containing
many data points (30 depending on the size of the numbers), I get the
error
message "Unable to set the values property of the series class". I
understand
this is because there is a limit to the size of an array, or the values
definition string, that the Values property can accept. The data for the
new
series is not contained in a range within a Worksheet.
Is there a workaround that doesn't involve writing the data to the
worksheet
and setting the Values property to the range containing the data? If not,
is
it possible to create an invisible range? Alternatively, is there a way of
dynamically adding datapoints to a series in a program loop so I can
gradually build up the array of data points without getting this error? Is
there any other way to create a chart series from an array of numbers
without
hitting this size limit?
Thanks,
Pete
|