The chart SERIES formula has a limit of (just under) 255 characters for
its various components. When you try and set the chart Values (or
XValues) to an array, that limit is easily exceeded.
Put the data in a worksheet range and use that range in the chart
series.
You can also use a named formula that contains a series of constant
values. However, there are two variants to this and the instinctive
one has a limit of 256 elements.
All the various options are shown in code samples below. You must
select an activechart with at least 1 plotted series before running any
of the procedures.
The first creates a chart from an array. The limit is a little under
255 characters. For integers it translates to 87 values 1..87.
Sub ChartSeriesLengthOK()
Dim i As Integer, x() As Integer
ReDim x(1 To 50)
For i = LBound(x) To UBound(x): x(i) = i: Next i
ActiveChart.SeriesCollection(1).Values = x
End Sub
The next demonstrates an approach that will fail because the length of
the SERIES formula becomes too long.
Sub ChartSeriesTooLong()
Dim i As Integer, x() As Integer
ReDim x(1 To 100)
For i = LBound(x) To UBound(x): x(i) = i: Next i
'I believe the threshold is 87
ActiveChart.SeriesCollection(1).Values = x
End Sub
The next one uses a Named formula. The first approach most people are
likely to try works up to 256 elements. The interesting thing is that
if one replaced the 100 in the code below with 1000 there will be no
error or warning. However, the chart series will have only 256
elements in it.
Sub ChartSeriesWithName()
Dim i As Integer, x() As Integer
ReDim x(1 To 100)
For i = LBound(x) To UBound(x): x(i) = i: Next i
ActiveWorkbook.Names.Add Name:="abc", _
RefersTo:=x
ActiveChart.SeriesCollection(1).Values = _
"='" & ActiveWorkbook.Name & "'!abc"
End Sub
I assume the 256 limit has something to do with the max. number of
columns. Using a 2D array as below removes that limitation and the
chart contains 1000 elements.
Sub ChartSeriesWith2DArrayAndName()
Dim i As Integer, x() As Integer
ReDim x(1 To 1000, 1 To 1)
For i = LBound(x) To UBound(x): x(i, 1) = i: Next i
ActiveWorkbook.Names.Add Name:="abc", _
RefersTo:=x
ActiveChart.SeriesCollection(1).Values = _
"='" & ActiveWorkbook.Name & "'!abc"
End Sub
The final approach adds a worksheet that is immediately hidden. That
worksheet is populated with the data in the array and the chart refers
to that range.
Sub ChartSeriesFromRange()
Dim i As Integer, x() As Integer, WS As Worksheet, _
aChart As Chart, TargCells As Range
ReDim x(1 To 1000)
For i = LBound(x) To UBound(x): x(i) = i: Next i
Set aChart = ActiveChart
Set WS = ActiveWorkbook.Worksheets.Add
WS.Visible = xlSheetHidden
'**** optionally xlSheetVeryHidden
Set TargCells = WS.Cells(1, 1). _
Resize(UBound(x) - LBound(x) + 1, 1)
TargCells.Value = Application.WorksheetFunction.Transpose(x)
aChart.SeriesCollection(1).Values = TargCells
End Sub
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article <Chris_Hawkins.1uqiuh_1125669942.5627@excelforum-
nospam.com,
says...
Found several similar threads but nothing that seems to address this
problem.
If I copy an Excel range into a variant, varArray and then use
SeriesCollection.Values = varArray I get Runtime 1004 error "Unable to
set the Values property of the Series class". I've tried first copying
the 2D variant into a 1D double or variant to get a 1D array but same
message results.
However if I just populate varArray locally with random numbers it
works okay. Also I've found that what I originally wanted to do works
up to 14 elements in varArray but fails when I increase the size to
15?
Examples of what work and what doesn't below - any guidance much
appreciated. (I know I can just use Ranges in the chart but I would
prefer not to have to.)
Fails:
varT = Range("T").Offset(1, 0).Resize(, 1).Value
ReDim dT(1 To 15)
ReDim dX(1 To 15)
For i = 1 To 15
dT(i) = varT(i, 1)
dX(i) = i
Next i
varT = dT
varX = dX
Set TheChart = Charts.Add
With TheChart
Name = sChart
ChartType = xlXYScatterLines
Set TheSeries = .SeriesCollection.NewSeries
TheSeries.Values = varT
TheSeries.XValues = varX
....
Works:
varT = Range("T").Offset(1, 0).Resize(, 1).Value
ReDim dT(1 To 14)
ReDim dX(1 To 14)
For i = 1 To 14
dT(i) = varT(i, 1)
dX(i) = i
Next i
varT = dT
varX = dX
Set TheChart = Charts.Add
With TheChart
Name = sChart
ChartType = xlXYScatterLines
Set TheSeries = .SeriesCollection.NewSeries
TheSeries.Values = varT
TheSeries.XValues = varX
....
Works:
ReDim dT(1 To 50)
ReDim dX(1 To 50)
For i = 1 To 50
dT(i) = CDbl(i/2)
dX(i) = i
Next i
varT = dT
varX = dX
Set TheChart = Charts.Add
With TheChart
Name = sChart
ChartType = xlXYScatterLines
Set TheSeries = .SeriesCollection.NewSeries
TheSeries.Values = varT
TheSeries.XValues = varX
....
--
Chris_Hawkins
------------------------------------------------------------------------
Chris_Hawkins's Profile: http://www.excelforum.com/member.php...o&userid=26924
View this thread: http://www.excelforum.com/showthread...hreadid=401454