troubles with graphs and VBA macros...

[Jean-Marie BOUDRENGHIEN]

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 :

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

 

[Tushar Mehta]

Instead of creating one chart for each column in your dataset, have you
considered adapting one of the techniques mentioned in the Excel |
Tutorials | Dynamic Charts page of my web site?

--
Regards,

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