"Excel has stopped working" when building charts

[quandary1337]

Greetings,

I'm working on a macro that does the following:

1. Pull data from the internet
2. Perform calculations on the data
3. Graph the calculated values

- As I run the macro, Excel dies in step 3, where I graph the calculated values.
- It works about 5% of the time and gives me the "Excel has stopped working" error 95% of the time - I can't isolate any difference in the attempts
- I've located the area in the macro where it dies, but I can't identify the exact line since it seems to shift around in this area
- It works perfectly fine when I step through using F8
- I've attempted DoEvents and Application.Wait to debug. Application.Wait for 5 seconds allows the macro to work about 50% of the time and increases in efficiency the longer I wait
- Steps 1 and 2 work perfectly fine (I've used that code in many macros), so I'm pretty sure the issue is in the below code:

Please let me know if you have any idea how to fix this! Even if you don't know the exact problem, please let me know if you have any hints for where to search for help. I'm running Windows 7, Office 2010

    'Chart variables    
    Sheets("Summary").Select
    Dim ChartRange As Range
    
    'Create Chart
    Set ChartRange = Range("C6:N32")
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Data"""
    ActiveChart.SeriesCollection(1).Values = "=Data!$H$8:$H$" & FillLength - Adjustment + 1 & ""
    ActiveChart.SeriesCollection(1).XValues = "=Data!$A$8:$A$" & FillLength - Adjustment + 1 & ""
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "=""Upper"""
    ActiveChart.SeriesCollection(2).Values = "=Data!$I$8:$I$" & FillLength - Adjustment + 1 & ""
    ActiveChart.SeriesCollection(2).XValues = "=Data!$A$8:$A$" & FillLength - Adjustment + 1 & ""
    
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "=""Lower"""
    ActiveChart.SeriesCollection(3).Values = "=Data!$J$8:$J$" & FillLength - Adjustment + 1 & ""
    ActiveChart.SeriesCollection(3).XValues = "=Data!$A$8:$A$" & FillLength - Adjustment + 1 & ""
    'MsgBox "got here"
    'Application.Wait (Now + TimeValue("0:00:03"))
    ActiveChart.ApplyLayout (3)
    
    ActiveChart.ChartTitle.Text = "Title"
    
    ActiveChart.Parent.Left = ChartRange.Left
    ActiveChart.Parent.Width = ChartRange.Width
    ActiveChart.Parent.Top = ChartRange.Top
    ActiveChart.Parent.Height = ChartRange.Height

 

[shg]

I can't tell you what the problem is, but why not just have a pro-forma chart that is formatted as you like, and then just change the data?

 

[quandary1337]

I can't tell you what the problem is, but why not just have a pro-forma chart that is formatted as you like, and then just change the data?

Thank you for the suggestion. I have done this and it works 100% of the time. I guess I'm just a little irked that I can't pinpoint the source of the exact problem. I'll use this solution!

Thanks again

 

[shg]

Glad you got it sorted, good luck.

 

[Jon Peltier]

If I were to guess, I'd say this line is the culprit:

ActiveChart.ApplyLayout (3)

Since Excel 97 I've had migraines over custom chart types and chart templates and the like. As a result I've always gone through the chart element by element and formatted each as desired. Takes a little longer but is lots more reliable. And nowadays I simply reuse existing code, so it hardly takes any time at all.

The solution suggested by shg also bypasses the unreliable steps.