Hello,

I'm having troubles with creating some graphs with an Excel VBA Macro.

I would like to create about 35 graphs from a set of data stored in a unique
datasheet. As the set of data can change (length, numbers, etc ..) I created
a macro to generate the graph starting from a template graph.

The macro runs correctly a few times but after a variable number of graph
(from 5 to 15, it depends on the weather), it crashes with :
Run-Time Error '-2147417848 (80010108)' : Methode 'XValues' of object
'series' failed

If i go in debug mode and repeat the error, I get :
Run-Time Error '1004' Unable to set the XValues property of the Series
class.

After this error, excel has always a strange behavior (unable to select
cells), and it always finishes by an excel crash.

I've tried many many things but nothing works :
I'm running Office XP SP2 under Windows 2000. It doesn't work
neither on office 2000 nor office 97 (win95).
I read the Kb article Q210684 about copying sheets . None of the
workaround worked
Save and close the workbook after each graph <- not
working
Import the graph from a template file (sheets.add
type:=template.xlt) <- not working
The codename looks ok (chart1, chart2, chart3, etc .)

I can't create a user-defined style as this worksheet will be
spread on many computers. Even more, the user-defined style can't hold all
information I need (logos, textbox, graph size, ..)

Here is what I do ..

startR is a variable which holds the row number of the dataset's beginning
startC is a variable which holds the colomn number of the dataset's
beginning


For i = 1 To nperfgraph
Sheets("ModelePerf").Copy Befo=Sheets(1)
Set NewGraph = Sheets(1)
NewGraph.Name = "Perf Wet-Dry (" & i & ")"
NewGraph.Axes(1).MinimumScale = Sheets("Data").Cells(startR,
StartC - 2).Value
NewGraph.Axes(1).MaximumScale = Sheets("Data").Cells(startR +
nperfpts, StartC - 2).Value
For j = 1 To nperfcurve
DoEvents
Call BuildSerie(NewGraph, startR, nperfpts, StartC - 2, StartC +
(j + ((i - 1) * nperfcurve)) - 1)
Next j
NewGraph.SeriesCollection(1).Delete
Set NewGraph = Nothing
DoEvents
Next i


Sub BuildSerie(NewGraph, Row, Npts, AxeXC, StartC)

Dim Line As Variant

Data = "=Data!"
Set serie = NewGraph.SeriesCollection.NewSeries
x = Data & "r" & Row & "c" & AxeXC & ":r" & Row + Npts & "c" & AxeXC
Valeur = Data & "r" & Row & "c" & StartC & ":r" & Row + Npts & "c" & StartC
nom = Data & "r" & Row - 2 & "c" & StartC
serie.XValues = x <--------------------- Macro crashes here.
serie.Values = Valeur
serie.Name = nom
Set serie = Nothing
DoEvents
End Sub


Any help would be grately appreciate.

I'm sure that there is a way to workaround this; I couldn't believe that no
one has ever done an auto-generation graph macro.


Thanks,
Vincent

Also see the responses to the same problem posted in .charting under
the title "graph troubles in VBA macros"

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello,

I'm having troubles with creating some graphs with an Excel VBA Macro.

I would like to create about 35 graphs from a set of data stored in a unique
datasheet. As the set of data can change (length, numbers, etc ..) I created
a macro to generate the graph starting from a template graph.

The macro runs correctly a few times but after a variable number of graph
(from 5 to 15, it depends on the weather), it crashes with :
Run-Time Error '-2147417848 (80010108)' : Methode 'XValues' of object
'series' failed

If i go in debug mode and repeat the error, I get :
Run-Time Error '1004' Unable to set the XValues property of the Series
class.

After this error, excel has always a strange behavior (unable to select
cells), and it always finishes by an excel crash.

I've tried many many things but nothing works :
I'm running Office XP SP2 under Windows 2000. It doesn't work
neither on office 2000 nor office 97 (win95).
I read the Kb article Q210684 about copying sheets . None of the
workaround worked
Save and close the workbook after each graph <- not
working
Import the graph from a template file (sheets.add
type:=template.xlt) <- not working
The codename looks ok (chart1, chart2, chart3, etc .)

I can't create a user-defined style as this worksheet will be
spread on many computers. Even more, the user-defined style can't hold all
information I need (logos, textbox, graph size, ..)

Here is what I do ..

startR is a variable which holds the row number of the dataset's beginning
startC is a variable which holds the colomn number of the dataset's
beginning


For i = 1 To nperfgraph
Sheets("ModelePerf").Copy Befo=Sheets(1)
Set NewGraph = Sheets(1)
NewGraph.Name = "Perf Wet-Dry (" & i & ")"
NewGraph.Axes(1).MinimumScale = Sheets("Data").Cells(startR,
StartC - 2).Value
NewGraph.Axes(1).MaximumScale = Sheets("Data").Cells(startR +
nperfpts, StartC - 2).Value
For j = 1 To nperfcurve
DoEvents
Call BuildSerie(NewGraph, startR, nperfpts, StartC - 2, StartC +
(j + ((i - 1) * nperfcurve)) - 1)
Next j
NewGraph.SeriesCollection(1).Delete
Set NewGraph = Nothing
DoEvents
Next i


Sub BuildSerie(NewGraph, Row, Npts, AxeXC, StartC)

Dim Line As Variant

Data = "=Data!"
Set serie = NewGraph.SeriesCollection.NewSeries
x = Data & "r" & Row & "c" & AxeXC & ":r" & Row + Npts & "c" & AxeXC
Valeur = Data & "r" & Row & "c" & StartC & ":r" & Row + Npts & "c" & StartC
nom = Data & "r" & Row - 2 & "c" & StartC
serie.XValues = x <--------------------- Macro crashes here.
serie.Values = Valeur
serie.Name = nom
Set serie = Nothing
DoEvents
End Sub


Any help would be grately appreciate.

I'm sure that there is a way to workaround this; I couldn't believe that no
one has ever done an auto-generation graph macro.


Thanks,
Vincent