.Seriescolection(n).formula - size limitation?

[David]

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 T]

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

 

[Jon Peltier]

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

 

[David]

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

 

[Peter T]

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]

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