If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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" |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|