Excel Chart Series Values Property Size Limitation

[Pete]

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

 

[Peter T]

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

 

[Jon Peltier]

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

 

[Peter T]

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

 

[Pete]

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

 

[Pete]

Hi Jon,

Security isn't actually my concern here - I just want to keep the worksheet
looking pretty. So my code needs to add a line to the chart but not dump data
all over the place. Adding a hidden sheet, putting my data there and
referencing the range as chart source data is one way to go but not my
preference. I think Peter T might be onto something though...

Thanks,

Pete

 

[Peter T]

Hi Pete,

I only mentioned Named arrays as you had asked if was possible to hold
'large' series data not in cells. However you implied an 'invisible range'
might would be OK, which I took to mean 'hidden' and I gave various
possibilities. That, with "just want to keep the worksheet looking pretty"
as the objective you gave in reply to Jon, strongly suggests the best
approach for your purposes would be to store your data in cells out of
sight.

If you are not familiar with how to make, in particular, 'vertical' named
arrays and how to apply them safely as source data there is a lot of work in
front of you, together with pre-checking and maintenance stuff (can only be
done with code).

If I haven't put you off (I'm trying to) I have a Com-addin that does all
the above and quite a lot more (also 'ordinary' arrays, re-source chart data
to new cell range, 'manage' the Names etc).
Normally it's UI driven but some functions are exposed and callable from say
your app or VBA, eg remove all cell links and reapply data as named arrays.
Contact me if interested (address below).

Regards,
Peter T
pmbthornton gmail com

 

[Jon Peltier]

All I'm saying is that, if you want to keep it easy, use another sheet; this
allows you to see the data, so you can (a) validate the array construction,
and (b) validate the accurate depiction of the data in the chart. I've used
the technique Peter describes, and it's not too bad, but it's more
complicated than your requirements dictate.

- Jon

 

[Pete]

Hi Peter & Jon,

Thank you both for your input. You have given me some ideas for a way
forward. (I also think I have a lot more to learn about Excel.)

Pete

 

[Jon Peltier]

I also think I have a lot more to learn about Excel.

The more you learn about Excel, the more you feel this way.

- Jon