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:

Please Login or Register  to view this content.

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

--
Regards,
Tom Ogilvy


"El_Pablo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> El_Pablo
> ------------------------------------------------------------------------
> El_Pablo's Profile:
http://www.excelforum.com/member.php...o&userid=33129
> View this thread: http://www.excelforum.com/showthread...hreadid=529411
>

[El_Pablo]

It worked! Thank you very much!

I have another question. How can I generate more than one serie? For now, each time I create a new serie in a loop, the old seems to be replaced. I will try to figure it out, but I was just wandering while I was replying.

Thank you

As a real programmer, I still don't like VBA

Nick