Re: Excel Chart Series Values Property Size Limitation

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Jon,

I first started using named arrays as a method to 'delink' large series data
from cells in another workbook. I posted the basic idea some while ago and
understand some now use the method to deliberately turn what you correctly
describe as problematic to their advantage!

The chart can remain user re-formatable and, if the right code is to hand,
still possible to retrieve, amend the data and/or re-source to cells.

Regards,
Peter T


"Jon Peltier" <jonxlmvpNO@xxxxxxxxxxxxxxxxxxx> wrote in message
news:eI9M1xEVIHA.2464@xxxxxxxxxxxxxxxxxxxxxxx
It seems like it would be "nice" if you could bury chart data in the chart
formula, though it's much harder to edit in the formula, and harder to see
where something may be wrong. Using Names also seems like a "nice"
solution,
but Names populated with static arrays also suffer from the above
problems.

Excel charts were designed to work with worksheet data. Worksheets are
cheap, they are easy to edit and validate, and they can be hidden. If you
want to hide the data completely, sending a chart with data isn't secure,
as
a simple macro can be used to extract the data. A more secure approach is
to
make a picture copy of the chart: hole Shift, select the Edit menu, choose
Copy Picture, and use the Picture and On Screen options. Then select a
cell
and paste.

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


"Peter T" <peter_t@discussions> wrote in message
news:ekK$zVDVIHA.5816@xxxxxxxxxxxxxxxxxxxxxxx
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" <Pete@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8DF712EA-CC55-4D8C-83D0-B07F539F16E2@xxxxxxxxxxxxxxxx
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 Work***.

Is there a workaround that doesn't involve writing the data to the
work***
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






.

Quantcast