Unable to set the Values property of the Series class

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rafael garcia - 25 Sep 2006 16:21 GMT

I'm working on a macro to get a chart from a dynamic range of cells
(selected according to the date introduced for the user in the worksheet).
The problem is that I store the selected range of values in variant vectors
(1D arrays), as decimal types. In the below code these arrays are called
PVAC, SRDM, ISRM and TimeData. With some range the code works, but with the
most of them it doesn't it. Any idea? thank you

'-------------------------------
'To put the Chosen Range of Data in arrays
'Selected Data into vectors

RangeSize = endRow - starRow + 1 'Size of the Range of choosen Data

ReDim PVAC(RangeSize) 'vector size
ReDim SRDM(RangeSize) 'vector size
ReDim ISRM(RangeSize) 'vector size
ReDim TimeData(RangeSize) 'vector size
k = 0
Do Until (k = RangeSize)
PVAC(k) = CDec(Sheets("Alldata2").Cells(starRow + k, 14))
SRDM(k) = CDec(Sheets("Alldata2").Cells(starRow + k, 15))
ISRM(k) = CDec(Sheets("Alldata2").Cells(starRow + k, 16))
TimeData(k) = CDec(Sheets("Alldata2").Cells(starRow + k, 6))
k = k + 1
Loop
ReDim Preserve PVAC(RangeSize - 1)
ReDim Preserve SRDM(RangeSize - 1)
ReDim Preserve ISRM(RangeSize - 1)
ReDim Preserve TimeData(RangeSize - 1)

'----------------------------------------------
'Chart from the Selected Data
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
'All de data as Range of Data
ActiveChart.SetSourceData Source:=Sheets("ALLDATA2").Range("N3:P14575"),
PlotBy:=xlColumns
'Display the first serie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = TimeData
ActiveChart.SeriesCollection(1).Values = PVAC
ActiveChart.SeriesCollection(1).Name = "=ALLDATA2!R2C14"
'Display the second serie
ActiveChart.SeriesCollection(2).XValues = TimeData
ActiveChart.SeriesCollection(2).Values = SRDM
ActiveChart.SeriesCollection(2).Name = "=ALLDATA2!R2C15"
'Display the third serie
ActiveChart.SeriesCollection(3).XValues = TimeData
ActiveChart.SeriesCollection(3).Values = ISRM
ActiveChart.SeriesCollection(3).Name = "=ALLDATA2!R2C16"

Reply to this Message

Andy Pope - 25 Sep 2006 16:31 GMT

Hi,

If you are using values rather than cell references you are no doubt
hitting the limit for the series formula length, which is about 1024
characters.

So a series formula references 4 rows looks like this
=SERIES(,Sheet1!$B$3:$B$6,Sheet1!$C$3:$C$6,1)

but when using an array of values looks like this
=SERIES(,{"a","b","c","d"},{1,2,3,4},1)

if you extend the range to row 43 the formula looks more like this
=SERIES(,Sheet1!$B$3:$B$43,{1,2,3,4,1,5,6,7,1,8,9,10,1,11,12,13,1,14,15,16,1,17,18,19,1,20,21,22,1,23,24,25,1,26,27,28,1,29,30,31,1,0,0},1)

And as you appear to be going to row 14575 the formula is going to be
too long. You will hit the limit a lot sooner if you have floating point
values.

Cheers
Andy

> I'm working on a macro to get a chart from a dynamic range of cells
> (selected according to the date introduced for the user in the worksheet).
[quoted text clipped - 46 lines]
> ActiveChart.SeriesCollection(3).Values = ISRM
> ActiveChart.SeriesCollection(3).Name = "=ALLDATA2!R2C16"

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message