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

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

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
...
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

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" wrote in message
...
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
...
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

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

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

"Jon Peltier" wrote:

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
...
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

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

"Pete" wrote in message
...
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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Pete" wrote in message
...
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

"Jon Peltier" wrote:

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
...
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

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

"Peter T" wrote:

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

"Pete" wrote in message
...
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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Pete" wrote in message
...
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

"Peter T" wrote:

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

"Pete" wrote in message
...
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