Runtime error 1004 when adding a seriescole to a chart
Hi all, this is a very stubborn problem of VBA and I have had to deal with it for years already:
Excel version 2003
Error: Runtime error '1004'
Unable to set the name property of the series class
the macro creates several graphs with multiple seriescollections.
Strangely, the first couple of graphs work perfectly! only after a couple of graphs the macro stops at the line ".Seriescollection(ibatch).name = batchfile(ibatch).charseriesname". I can't figure out why, as batchfile(ibatch).charseriesname contains a perfectly fine string
Now I have to mention that this problem can also occur on other moments, when assiging other properties, such as the scaletype for the x-axis.
Any help is highly appreciated!
Cheers, Siebe
For iBatch = 1 To nBatch
If iBatch = 1 Then
Charts.Add
With ActiveChart
.ChartType = xlXYScatter
.Axes(xlCategory).CategoryType = xlTimeScale
.SetSourceData Source:=Union(xDataRange(iBatch), yDataRange(iBatch)), PlotBy:=xlColumns
.SeriesCollection(iBatch).Name = BatchFile(iBatch).CharSeriesName
.Axes(xlCategory).BaseUnit = xlDays
.Axes(xlCategory).Crosses = xlCustom
.Axes(xlCategory).CrossesAt = 0.001
.HasTitle = True
.ChartTitle.Text = Title
.ChartTitle.Font.Size = 18
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Herhalingstijd (jaren)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Maximale Waterstand (m NAP)"
.Axes(xlCategory).ScaleType = xlLogarithmic
End With
Else
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(iBatch).Name = BatchFile(iBatch).CharSeriesName
.SeriesCollection(iBatch).XValues = xDataRange(iBatch)
.SeriesCollection(iBatch).Values = yDataRange(iBatch)
End With
End If
Next iBatch
'export the graph to picture file
Charts(1).Export FileName:=Worksheets(Settings.shtStart).txtResultatenDir.Value & "\" & Title & ".gif", FilterName:="GIF"
'remove the graph
Application.DisplayAlerts = False
Charts.Delete
Application.DisplayAlerts = True
End Sub
Runtime error 1004 when adding a seriescole to a chart
HowardRichards
Hi Siebe,
This isn't a solution sorry.
I have the exact same problem. Sometimes it's with the chart title and axis title, sometimes with the series name. The strange thing is it sometimes works and then for no reason at all it doesn't work, and then for no reason it works again.
Sometimes the code works on one machine and not on another even though they are the same build. Other times it works on all machines for one spreadsheet and then doesn't work on all machines for another spreadsheet.
If anyone from Microsoft reads this problem I've made a similar post here.
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=391049&SiteID=1
merivel
I am also experiencing the same problem with valid data in a normal scatter plot. This is an ongoing problem and has frustrated a number of us while we try to develop tool sets uning Excel/VBA.
Plotloc1 = Array("V6:V23", "W6:W23", "X6:X23", "Y6:Y23", "Z6:Z23")
Plotloc2 = "U6:U23"
Indexseries = 1
Set ch = ActiveSheet.ChartObjects.Add(1, 434, 430, 215)
ch.Chart.ChartType = xlXYScatter
Do While Indexseries < numplots + 1
ch.Chart.SeriesCollection.NewSeries
Data = Plotloc1(Indexseries - 1)
ch.Chart.SeriesCollection(Indexseries).Values = Worksheets("PGFT_YAW1").Range(Data)
ch.Chart.SeriesCollection(Indexseries).XValues = Worksheets("PGFT_YAW1").Range(Plotloc2)
Indexseries = Indexseries + 1
Loop
ericjn
If you read my prior post, you know to check whether any of your ranges have no valid plottable data.
Which line raises the error Does it always fail, or only sometimes
What if you set the chart type after defining the data
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
windyweather
Hi,
Just a few minutes back I had created a similar thread for same problem. Although I had gone through previous two posts regarding this problem, I was not able to solve the problem I was facing. However, I found a solution for my problem and wanted to share what was happening.
In my case, the data was completely valid and plottable, but it was still giving me this error. I found that the reason was that it was considering too many digits after decimal point. So instead of having a ratio, I represented it in percentage and that solved the problem. I am not sure if this is exactly the cause of the problem but for my case it worked.
Swanand.
BioSlayer
Is the series populated with valid data Often if a data range is blank or filled with errors, VBA will not recognize or be able to access all properties of a series collection. I know for sure that VBA cannot access the .Values and .XValues of an XY series without valid data. Curiously, an Area chart type is okay.
If the chart type is XY, and data is valid, you should be able to set the X axis as logarithmic. The only thing I wonder is whether the annoying UI message about trying to plot a negative or zero on a log scale instead causes a RTE in VBA.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______