Problem using arrays in Excel Charts from VBA

[Chris_Hawkins]

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

.....

[Tushar Mehta]

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>, Chris_Hawkins.1uqiuh_1125669942.5627...rum-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
>
>