Home Menu

Menu


ExcelBanter » Excel Newsgroups » Charts and Charting in Excel > Help: runtime error - Method seriescollection object_chart failed
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
huangx06
 
Posts: n/a
Default Help: runtime error - Method seriescollection object_chart failed

Hello,

I have a draw chart sub which generates a runtime error "method
seriescollection object_chart failed" from time to time. It doesn't
happen all the time, probaly around 15% of the time. Below is the full
code. I'm running out of ideas now in terms of how to fix the problem.
Please help!!

Thanks a lot
---------------------------------------------------------------------------------------------------
Sub drawChart(nYears As Integer)

Dim TempChartObjA As ChartObject
Dim TempChartObjB As ChartObject
Dim TempChartObjC As ChartObject
Dim TempChartObjD As ChartObject
Dim chartCount As Integer
Dim i As Integer
Dim TempSeriesCollection As SeriesCollection

Call drawCheckBox(nYears)

chartCount = Worksheets("DashBoard").ChartObjects.Count
If chartCount 0 Then
For i = 1 To chartCount
Worksheets("DashBoard").ChartObjects(1).Delete
Next i
End If

'reset the average line check boxes
Worksheets("DashBoard").cb_DailyAverage.Visible = True
Worksheets("DashBoard").cb_DailyAverage.Value = False
Worksheets("DashBoard").cb_DailyAveragePercent.Vis ible = False
Worksheets("DashBoard").cb_DailyAveragePercent.Val ue = False

Set TempChartObjA = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)
Set TempChartObjC = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)
Set TempChartObjB = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)
Set TempChartObjD = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)

TempChartObjA.Name = "chartGDM"
TempChartObjC.Name = "chartGDMPercent"
TempChartObjB.Name = "chartIFERC"
TempChartObjD.Name = "chartIFERCPercent"

TempChartObjA.Activate
ActiveChart.chartType = xlLineMarkers
TempChartObjC.Activate
ActiveChart.chartType = xlLineMarkers

If ifercHasCharts Then
Worksheets("DashBoard").cb_MonthlyAverage.Visible = False
Worksheets("DashBoard").cb_MonthlyAverage.Value = False
Worksheets("DashBoard").cb_MonthlyAveragePercent.V isible =
False
Worksheets("DashBoard").cb_MonthlyAveragePercent.V alue = False

TempChartObjB.Activate
ActiveChart.chartType = xlLineMarkers
TempChartObjD.Activate
ActiveChart.chartType = xlLineMarkers
End If


'Add new series data to the series collection to charts
For i = 0 To nYears
' chartGDM
TempChartObjA.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!dateAxisAll"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!DataYear" & Year(Now()) - i


ActiveChart.SeriesCollection(i + 1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With

' chartGDMPercent
TempChartObjC.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!dateAxisAll"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!DataYearPercent" & Year(Now()) - i

ActiveChart.SeriesCollection(i + 1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With

If ifercHasCharts Then
'chartIFERC
TempChartObjB.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!ifercChartMonthAxis"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!IFERCDataYear" & Year(Now()) - i
ActiveChart.SeriesCollection

'chartIFERCPercent
TempChartObjD.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!ifercChartMonthAxis"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!IFERCDataYearPercent" & Year(Now()) - i
ActiveChart.SeriesCollection
End If
Next i

TempChartObjA.Activate

With ActiveChart
.HasTitle = True
.ChartTitle.Text = "Gas Daily Average Spread: Receiving " &
Worksheets("SymbolMap").Range _
("longLoc") & "- Delivery " &
Worksheets("SymbolMap").Range("shortLoc")
End With

ActiveChart.Axes(xlValue).TickLabels.NumberFormat =
"$#,##0.0_);[Red]($#,##0.0)"

ActiveChart.Axes(xlCategory).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.TickLabelSpacing = 20
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlGray50
End With

ActiveChart.PlotArea.Select
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Spread"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlGray50
End With
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 20
.TickMarkSpacing = 20
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With

'################################################# ###
TempChartObjC.Activate

With ActiveChart
.HasTitle = True
.ChartTitle.Text = "GDA Spread: Receiving " &
Worksheets("SymbolMap").Range _
("longLoc") & "- Delivery " &
Worksheets("SymbolMap").Range("shortLoc") _
& " As a Percentage of Receiving Location "
& Worksheets("SymbolMap").Range _
("longLoc")
End With

ActiveChart.Axes(xlValue).TickLabels.NumberFormat =
"#,##0.0%;[Red](#,##0.0%)"

ActiveChart.Axes(xlCategory).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.TickLabelSpacing = 20
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlGray50
End With

ActiveChart.PlotArea.Select
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "%
Spread"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlGray50
End With
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 20
.TickMarkSpacing = 20
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
'################################################# ###
'################################################# #'
TempChartObjB.Activate
'code deleted
' TempChartObjD.Activate
'code deleted
'################################################
TempChartObjA.BringToFront
TempChartObjC.Visible = False
If ifercHasCharts Then
TempChartObjB.Visible = False
TempChartObjD.Visible = False
End If


Worksheets("DashBoard").OLEObjects("ChartType_List ").Object.Selected(0)
= True
Application.ScreenUpdating = True
End Sub


--
huangx06
------------------------------------------------------------------------
huangx06's Profile: http://www.excelforum.com/member.php...o&userid=25014
View this thread: http://www.excelforum.com/showthread...hreadid=385402

Reply With QuoteReply With Quote
  #2   Report Post  
Jon Peltier
 
Posts: n/a
Default
That's almost 300 lines of code to wade through. Any hints about which
line throws the error?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


huangx06 wrote:

Hello,

I have a draw chart sub which generates a runtime error "method
seriescollection object_chart failed" from time to time. It doesn't
happen all the time, probaly around 15% of the time. Below is the full
code. I'm running out of ideas now in terms of how to fix the problem.
Please help!!

Thanks a lot
---------------------------------------------------------------------------------------------------
Sub drawChart(nYears As Integer)

Dim TempChartObjA As ChartObject
Dim TempChartObjB As ChartObject
Dim TempChartObjC As ChartObject
Dim TempChartObjD As ChartObject
Dim chartCount As Integer
Dim i As Integer
Dim TempSeriesCollection As SeriesCollection

Call drawCheckBox(nYears)

chartCount = Worksheets("DashBoard").ChartObjects.Count
If chartCount 0 Then
For i = 1 To chartCount
Worksheets("DashBoard").ChartObjects(1).Delete
Next i
End If

'reset the average line check boxes
Worksheets("DashBoard").cb_DailyAverage.Visible = True
Worksheets("DashBoard").cb_DailyAverage.Value = False
Worksheets("DashBoard").cb_DailyAveragePercent.Vis ible = False
Worksheets("DashBoard").cb_DailyAveragePercent.Val ue = False

Set TempChartObjA = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)
Set TempChartObjC = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)
Set TempChartObjB = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)
Set TempChartObjD = Worksheets("DashBoard").ChartObjects.Add(0, 0,
700, 300)

TempChartObjA.Name = "chartGDM"
TempChartObjC.Name = "chartGDMPercent"
TempChartObjB.Name = "chartIFERC"
TempChartObjD.Name = "chartIFERCPercent"

TempChartObjA.Activate
ActiveChart.chartType = xlLineMarkers
TempChartObjC.Activate
ActiveChart.chartType = xlLineMarkers

If ifercHasCharts Then
Worksheets("DashBoard").cb_MonthlyAverage.Visible = False
Worksheets("DashBoard").cb_MonthlyAverage.Value = False
Worksheets("DashBoard").cb_MonthlyAveragePercent.V isible =
False
Worksheets("DashBoard").cb_MonthlyAveragePercent.V alue = False

TempChartObjB.Activate
ActiveChart.chartType = xlLineMarkers
TempChartObjD.Activate
ActiveChart.chartType = xlLineMarkers
End If


'Add new series data to the series collection to charts
For i = 0 To nYears
' chartGDM
TempChartObjA.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!dateAxisAll"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!DataYear" & Year(Now()) - i


ActiveChart.SeriesCollection(i + 1).Select
With Selection.Border
Weight = xlThin
LineStyle = xlAutomatic
End With
With Selection
MarkerBackgroundColorIndex = xlAutomatic
MarkerForegroundColorIndex = xlAutomatic
MarkerStyle = xlSquare
Smooth = False
MarkerSize = 3
Shadow = False
End With

' chartGDMPercent
TempChartObjC.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!dateAxisAll"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!DataYearPercent" & Year(Now()) - i

ActiveChart.SeriesCollection(i + 1).Select
With Selection.Border
Weight = xlThin
LineStyle = xlAutomatic
End With
With Selection
MarkerBackgroundColorIndex = xlAutomatic
MarkerForegroundColorIndex = xlAutomatic
MarkerStyle = xlSquare
Smooth = False
MarkerSize = 3
Shadow = False
End With

If ifercHasCharts Then
'chartIFERC
TempChartObjB.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!ifercChartMonthAxis"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!IFERCDataYear" & Year(Now()) - i
ActiveChart.SeriesCollection

'chartIFERCPercent
TempChartObjD.Activate
'On Error Resume Next
'Worksheets("DataMap").ShowAllData
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i + 1).XValues =
"=DataMap!ifercChartMonthAxis"
ActiveChart.SeriesCollection(i + 1).Name = "Year " &
Year(Now()) - i
ActiveChart.SeriesCollection(i + 1).Values =
"=DataMap!IFERCDataYearPercent" & Year(Now()) - i
ActiveChart.SeriesCollection
End If
Next i

TempChartObjA.Activate

With ActiveChart
HasTitle = True
ChartTitle.Text = "Gas Daily Average Spread: Receiving " &
Worksheets("SymbolMap").Range _
("longLoc") & "- Delivery " &
Worksheets("SymbolMap").Range("shortLoc")
End With

ActiveChart.Axes(xlValue).TickLabels.NumberFormat =
"$#,##0.0_);[Red]($#,##0.0)"

ActiveChart.Axes(xlCategory).Select
With Selection.Border
ColorIndex = 57
Weight = xlMedium
LineStyle = xlContinuous
End With
With Selection
TickLabelSpacing = 20
MajorTickMark = xlNone
MinorTickMark = xlNone
TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
ColorIndex = 57
Weight = xlHairline
LineStyle = xlGray50
End With

ActiveChart.PlotArea.Select
With ActiveChart
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Date"
Axes(xlValue, xlPrimary).HasTitle = True
Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Spread"
End With
With ActiveChart.Axes(xlCategory)
HasMajorGridlines = True
HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
HasMajorGridlines = True
HasMinorGridlines = False
End With
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
ColorIndex = 57
Weight = xlHairline
LineStyle = xlGray50
End With
With ActiveChart.Axes(xlCategory)
CrossesAt = 1
TickLabelSpacing = 20
TickMarkSpacing = 20
AxisBetweenCategories = True
ReversePlotOrder = False
End With

'################################################# ###
TempChartObjC.Activate

With ActiveChart
HasTitle = True
ChartTitle.Text = "GDA Spread: Receiving " &
Worksheets("SymbolMap").Range _
("longLoc") & "- Delivery " &
Worksheets("SymbolMap").Range("shortLoc") _
& " As a Percentage of Receiving Location "
& Worksheets("SymbolMap").Range _
("longLoc")
End With

ActiveChart.Axes(xlValue).TickLabels.NumberFormat =
"#,##0.0%;[Red](#,##0.0%)"

ActiveChart.Axes(xlCategory).Select
With Selection.Border
ColorIndex = 57
Weight = xlMedium
LineStyle = xlContinuous
End With
With Selection
TickLabelSpacing = 20
MajorTickMark = xlNone
MinorTickMark = xlNone
TickLabelPosition = xlLow
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
ColorIndex = 57
Weight = xlHairline
LineStyle = xlGray50
End With

ActiveChart.PlotArea.Select
With ActiveChart
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Date"
Axes(xlValue, xlPrimary).HasTitle = True
Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "%
Spread"
End With
With ActiveChart.Axes(xlCategory)
HasMajorGridlines = True
HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
HasMajorGridlines = True
HasMinorGridlines = False
End With
ActiveChart.Axes(xlCategory).MajorGridlines.Select
With Selection.Border
ColorIndex = 57
Weight = xlHairline
LineStyle = xlGray50
End With
With ActiveChart.Axes(xlCategory)
CrossesAt = 1
TickLabelSpacing = 20
TickMarkSpacing = 20
AxisBetweenCategories = True
ReversePlotOrder = False
End With
'################################################# ###
'################################################# #'
TempChartObjB.Activate
'code deleted
' TempChartObjD.Activate
'code deleted
'################################################
TempChartObjA.BringToFront
TempChartObjC.Visible = False
If ifercHasCharts Then
TempChartObjB.Visible = False
TempChartObjD.Visible = False
End If


Worksheets("DashBoard").OLEObjects("ChartType_List ").Object.Selected(0)
= True
Application.ScreenUpdating = True
End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
huangx06
 
Posts: n/a
Default

Hi Jon,

Thank you for looking at my problem. I have deleted those lines I think
are not related to the problem. Can you take a look again? Again, my
problem is totally random. It happens no more than 20% of the time.

Thanks again.


--
huangx06
------------------------------------------------------------------------
huangx06's Profile: http://www.excelforum.com/member.php...o&userid=25014
View this thread: http://www.excelforum.com/showthread...hreadid=385402

Reply With QuoteReply With Quote
  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default
I think you deleted all of them <g

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


huangx06 wrote:

Hi Jon,

Thank you for looking at my problem. I have deleted those lines I think
are not related to the problem. Can you take a look again? Again, my
problem is totally random. It happens no more than 20% of the time.

Thanks again.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT +1. The time now is 04:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement