Thought I might use arrays to collect chart series X and Y values from a mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140 values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)

Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
With myChtObj.Chart
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
End With

The Series formula is limited to an absolute maximum of the 256 (or a tad
less) characters in each of the four (or five if Bubble) sections of the
formula.

Take this array -
{123.456789,123.456789,123.456789}
That's 34 characters, including the commas and curly brackets, from the
maximum 256.

There is a different way to accommodate virtually unlimited data in a Series
without use of cells, it involves defining the values as Named "vertical"
arrays. It's not straightforward.

Regards,
Peter T


"David" wrote in message
...
Thought I might use arrays to collect chart series X and Y values from a
mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140
values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)

Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
With myChtObj.Chart
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
End With

Easier than trying to manipulate Names is to dump the data into a
worksheet, and plotting from the range. This is how Excel charts were
designed, and while they are flexible, this flexibility comes at the
expense of reliability.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
774-275-0064
http://peltiertech.com/


On 5/13/2010 4:58 AM, Peter T wrote:
The Series formula is limited to an absolute maximum of the 256 (or a tad
less) characters in each of the four (or five if Bubble) sections of the
formula.

Take this array -
{123.456789,123.456789,123.456789}
That's 34 characters, including the commas and curly brackets, from the
maximum 256.

There is a different way to accommodate virtually unlimited data in a Series
without use of cells, it involves defining the values as Named "vertical"
arrays. It's not straightforward.

Regards,
Peter T


wrote in message
...
Thought I might use arrays to collect chart series X and Y values from a
mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140
values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)

Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
With myChtObj.Chart
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
End With

Thanks John,
Your comments are appreciated.
btw thanks for the wealth of Excel Charting resources you provide on the web
I have found them to be a great benefit
Dave

"Jon Peltier" wrote:

Easier than trying to manipulate Names is to dump the data into a
worksheet, and plotting from the range. This is how Excel charts were
designed, and while they are flexible, this flexibility comes at the
expense of reliability.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
774-275-0064
http://peltiertech.com/


On 5/13/2010 4:58 AM, Peter T wrote:
The Series formula is limited to an absolute maximum of the 256 (or a tad
less) characters in each of the four (or five if Bubble) sections of the
formula.

Take this array -
{123.456789,123.456789,123.456789}
That's 34 characters, including the commas and curly brackets, from the
maximum 256.

There is a different way to accommodate virtually unlimited data in a Series
without use of cells, it involves defining the values as Named "vertical"
arrays. It's not straightforward.

Regards,
Peter T


wrote in message
...
Thought I might use arrays to collect chart series X and Y values from a
mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140
values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)

Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
With myChtObj.Chart
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
End With


.

Peter,
Thanks for that..
'Named Vertical Arrays' sounds interesting
I'll google
Meanwhile, do you have any example code?
Thanks

"Peter T" wrote:

The Series formula is limited to an absolute maximum of the 256 (or a tad
less) characters in each of the four (or five if Bubble) sections of the
formula.

Take this array -
{123.456789,123.456789,123.456789}
That's 34 characters, including the commas and curly brackets, from the
maximum 256.

There is a different way to accommodate virtually unlimited data in a Series
without use of cells, it involves defining the values as Named "vertical"
arrays. It's not straightforward.

Regards,
Peter T


"David" wrote in message
...
Thought I might use arrays to collect chart series X and Y values from a
mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140
values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)

Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
With myChtObj.Chart
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
End With


.

Following Jon's comment I hesitate to give you any example code <g

Oh well, just as a teaser then -

Sub test2()
' create an XY scatter chart with one series
' make it big, and select the chart
Dim i As Long, k As Long
Dim s as string
Dim arrX(), arrY()

k = 1000

ReDim arrX(1 To k, 1 To 1)
ReDim arrY(1 To k, 1 To 1)

For i = 1 To k
arrX(i, 1) = Rnd(i)
arrY(i, 1) = Rnd(i)
Next

With ActiveWorkbook.Names
.Add "xData", arrX
.Add "yData", arrY
End With

s = "='" & ActiveWorkbook.Name & "'!"

With ActiveChart.SeriesCollection(1)
.XValues = s & "xData"
.Values = s & "yData"
End With

End Sub

If you leave k=1000 the series should end up with a thousand data points,
which if converted to a pair of arrays as strings would have a length of
about 17,000 characters in each.

Jon is quite right of course, normally the best place for data is in cells.
However for those limited scenerios when data is not wanted in cells this
method works very well. That said, there's a lot of work involved to manage
it all, for all chart types, manipulating / editing the data, switching the
chart from Named Data to cells and back and forth, managing the names, etc.
I have ComAddin to do all that if interested.

Regards,
Peter T


"David" wrote in message
...
Peter,
Thanks for that..
'Named Vertical Arrays' sounds interesting
I'll google
Meanwhile, do you have any example code?
Thanks

"Peter T" wrote:

The Series formula is limited to an absolute maximum of the 256 (or a tad
less) characters in each of the four (or five if Bubble) sections of the
formula.

Take this array -
{123.456789,123.456789,123.456789}
That's 34 characters, including the commas and curly brackets, from the
maximum 256.

There is a different way to accommodate virtually unlimited data in a
Series
without use of cells, it involves defining the values as Named "vertical"
arrays. It's not straightforward.

Regards,
Peter T


"David" wrote in message
...
Thought I might use arrays to collect chart series X and Y values from
a
mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140
values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)

Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
With myChtObj.Chart
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
End With


.

Thanks Peter

I will probably dump the chart data into a worksheet as usual...
I suppose I'm hooked on this stuff and I am always keen to explore and learn
new techniques so thanks for your information.
Of course I've taken on board John's comments, I'd be a fool not to.


"Peter T" wrote:

Following Jon's comment I hesitate to give you any example code <g

Oh well, just as a teaser then -

Sub test2()
' create an XY scatter chart with one series
' make it big, and select the chart
Dim i As Long, k As Long
Dim s as string
Dim arrX(), arrY()

k = 1000

ReDim arrX(1 To k, 1 To 1)
ReDim arrY(1 To k, 1 To 1)

For i = 1 To k
arrX(i, 1) = Rnd(i)
arrY(i, 1) = Rnd(i)
Next

With ActiveWorkbook.Names
.Add "xData", arrX
.Add "yData", arrY
End With

s = "='" & ActiveWorkbook.Name & "'!"

With ActiveChart.SeriesCollection(1)
.XValues = s & "xData"
.Values = s & "yData"
End With

End Sub

If you leave k=1000 the series should end up with a thousand data points,
which if converted to a pair of arrays as strings would have a length of
about 17,000 characters in each.

Jon is quite right of course, normally the best place for data is in cells.
However for those limited scenerios when data is not wanted in cells this
method works very well. That said, there's a lot of work involved to manage
it all, for all chart types, manipulating / editing the data, switching the
chart from Named Data to cells and back and forth, managing the names, etc.
I have ComAddin to do all that if interested.

Regards,
Peter T


"David" wrote in message
...
Peter,
Thanks for that..
'Named Vertical Arrays' sounds interesting
I'll google
Meanwhile, do you have any example code?
Thanks

"Peter T" wrote:

The Series formula is limited to an absolute maximum of the 256 (or a tad
less) characters in each of the four (or five if Bubble) sections of the
formula.

Take this array -
{123.456789,123.456789,123.456789}
That's 34 characters, including the commas and curly brackets, from the
maximum 256.

There is a different way to accommodate virtually unlimited data in a
Series
without use of cells, it involves defining the values as Named "vertical"
arrays. It's not straightforward.

Regards,
Peter T


"David" wrote in message
...
Thought I might use arrays to collect chart series X and Y values from
a
mass
of data (simplified example code below)
I found that with simple integers I can load a maximum of about 140
values.
With DateTime numbers this comes down to about 16 maximum.
Is this to be expected? (Excel 2003)

Dim myChtObj As ChartObject
Set myChtObj = Sheets("chart").ChartObjects("Chart 1")
With myChtObj.Chart
.SeriesCollection(1).Values = Array(4,5,6,...,n)
.SeriesCollection(1).XValues = Array(1,2,3,...,n)
End With


.



.