Help: runtime error "method seriescollection object_chart failed"

H

huangx06

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.Visible = False
Worksheets("DashBoard").cb_DailyAveragePercent.Value = 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.Visible = False
Worksheets("DashBoard").cb_MonthlyAveragePercent.Value = 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

'################################################# ###
 
W

William Benson

See if this post helps you, you have too much code for me to go through at
this hour, but this thread may provide a clue possibly.

http://groups-beta.google.com/group...+author:Ferrero&rnum=2&hl=en#4cd0e874e0d2baa8


huangx06 said:
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.Visible = False
Worksheets("DashBoard").cb_DailyAveragePercent.Value = 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.Visible = False
Worksheets("DashBoard").cb_MonthlyAveragePercent.Value = 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

'################################################# ###
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

add a chart in a Add-In 1
Help: runtime error - Method seriescollection object_chart failed 3
How to change the row number in macro code? 1
reverse axis not working as before... 0
Floating Chart? 2
Run-time error `91': Object variable . . . not set 2
Please help me with running this macro in Excel 2007 0
Code to Create Chart 0

Top