New to Excel Error 1004 basic chart programming

[El_Pablo]

Hi,

I've been trying to create a very basic chart with some simple values
in it. It works fine for a small quantity of values (let say 100), but
as soon as the number of values gets bigger, I get a 1004 error "Unable
to set the values property of the series class".

I've been trying to work this out for the last 2 hours and usually I'm
pretty good to programming everything else than VBA.

Here's my code:


Code:
--------------------
Private Sub Chart_Activate()
Dim iArray() As Integer
Dim iNbElements As Integer
Dim i As Integer
Dim sSeries As Series

iNbElements = 180

ReDim iArray(1 To iNbElements) As Integer

For i = 1 To iNbElements
iArray(i) = i
Next

For Each sSeries In ActiveChart.SeriesCollection
sSeries.Delete
Next

Set sSeries = ActiveChart.SeriesCollection.NewSeries

sSeries.ChartType = xlXYScatter

sSeries.Values = iArray ' *The error is here!! Making me crazy!!*

Set sSeries = Nothing

End Sub
--------------------


Please someone help me!

Nick

 

[Tom Ogilvy]

when you build a chart with 20 elements using the code, you can then select
the series and look at the formula bar. You see

=SERIES(,,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20},1)

You can sense that this will be limited by the size of a formula that can be
built. A work around is to put the array in a defined range. This worked
for me. (make the array vertical by using 2 dimensions)

Private Sub Chart_Activate()
Dim iArray() As Integer
Dim iNbElements As Integer
Dim i As Integer
Dim sSeries As Series

iNbElements = 180

ReDim iArray(1 To iNbElements, 1 To 1) As Integer

For i = 1 To iNbElements
iArray(i, 1) = i
Next
ThisWorkbook.Names.Add Name:="ListY", _
RefersTo:=iArray
For Each sSeries In ActiveChart.SeriesCollection
sSeries.Delete
Next

Set sSeries = ActiveChart.SeriesCollection.NewSeries

sSeries.ChartType = xlXYScatter

sSeries.Values = "='" & ThisWorkbook.Name & _
"'!ListY" ' *The error is here!! Making me crazy!!*

Set sSeries = Nothing

End Sub

 

[El_Pablo]

It worked! Thank you very much!

But as a real programmer, it still doesn't make me like VBA

Nic