Unable to set the Values property of the Series class
 

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"

Unable to set the Values property of the Series class
 

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,2 3,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

rafael garcia wrote:
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"

--

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