Solved: VBA modules crash Excel

[craigwg]

I have three modules that are straight forward. The first one deletes the contents of the sheet, the second creates a pivot table and chart and the third creates a second pivot table and chart.

I fire these modules in the order upon Worksheet_Activate. All the modules run great if I fire them one at a time individually, and in my testing about 1/20 times they work just fine when the worksheet is activated. But most the time Excel Crashes.

What is the problem? Obviously having two pivot_tables and charts on the same worksheet is fine. Is there a problem with the way I am deleting the contents of the sheet? Another theory I have is that Excel is running the modules too fast and they are bumping into each other? But that makes no sense, especially considering that they run just fine when I fire them manually.

Below is my code in the order that it is firing on Worksheet_Activate. Thanks for your help!


-------------------------------------
Sub DeleteCharts()
Cells.Delete
End Sub
-------------------------------------
Sub CreateChartOne()
ActiveWorkbook.Worksheets("Day by Day Pivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Charts!R30C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Charts").Select
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Top = 10 ' reposition
.Left = 10 ' reposition
.Height = 310 ' resize
.Width = 350 ' resize
End With
With ActiveChart
.SetSourceData Source:=Range("'Charts'!$A$30:$C$47")
.ChartType = xlLine
.ApplyLayout (3)
.ChartTitle.Text = "Calories, Carbohydrates and Fiber"
End With
ActiveWorkbook.ShowPivotChartActiveFields = False
ActiveWorkbook.ShowPivotTableFieldList = False

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Meal")
.Orientation = xlPageField
.Position = 2
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Calories"), "Count of Calories", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Calories")
.Caption = "Sum of Calories"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(1).Smooth = True
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Carbohydrates"), "Count of Carbohydrates", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Carbohydrates")
.Caption = "Sum of Carbohydrates"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(2).Smooth = True
End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Dietary Fiber"), "Count of Dietary Fiber", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Dietary Fiber")
.Caption = "Sum of Dietary Fiber"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(3).Smooth = True
ActiveChart.SeriesCollection(3).AxisGroup = 2
End With

End Sub
-------------------------------------
Sub CreateChartTwo()
ActiveWorkbook.Worksheets("Day by Day Pivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Charts!R26C9", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion12
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Top = 10 ' reposition
.Left = 555 ' reposition
.Height = 310 ' resize
.Width = 350 ' resize
End With
ActiveChart.ApplyLayout (3)
ActiveChart.ChartTitle.Text = "Calories, Carbohydrates and Fiber"
ActiveChart.SetSourceData Source:=Range("'Charts'!$I$26:$K$43")
ActiveWorkbook.ShowPivotChartActiveFields = False
ActiveChart.ChartType = xlCylinderColStacked
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Meal")
.Orientation = xlColumnField
.Position = 1
.PivotItems("(blank)").Visible = False
.PivotItems("Dinner").Position = 3
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Calories"), "Count of Calories", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Calories")
.Caption = "Sum of Calories"
.Function = xlSum
.NumberFormat = "0.0"
End With

ActiveSheet.PivotTables("PivotTable3").PivotSelect "Date[All]", xlLabelOnly + _
xlFirstRow, True

'Range("K27:L27").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "Dinner", xlDataAndLabel, _
True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Up:=12
End Sub

[mikerickson]

What version of Excel are you using?

[craigwg]

Excel 2007. This was written over a few days and many trials using my work computer and home computer. Both are running 2007 though. The crash occurs the same way on both. Both versions are up to date as far as I know.

[mdmackillop]

Can you post a workbook with sample data?

As a workaround, you could try using OnTime to call successive macros, giving a short time to allow processing to complete.

[mikerickson]

Sometimes inserting Save's will stabilize code. As will user interfaces.

[Paul_Hossler]

Also Ron Bovey's code cleaner might help

http://www.appspro.com/Utilities/CodeCleaner.htm



About the VBA Code Cleaner

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files



or you could manually export all the Modules and them re-import them, but Ron's utility is easier and has some other options that are handy.

There is some sort of module size limit. I've heard it's 64K, but if any of mine get to around 45KB or 50, I split them

Paul

[shrivallabha]

One more way would be to use (little woozy if it is the best foot forward)

@ macro1 end:
Call Macro2

And then @ the end of Macro2
Call Macro3

This way you'll always be sure that the each of the macro has run its course!

Hth,

[craigwg]

I found out my issue just today. When the macro ran, a range of cells was selected. When I ran the macro again, the pivot table tried to reference the selection, and also the range referenced in the modules. Apparently this crashed Excel. I just added a command to select the range A1 at the end of the delete macro and it has hummed right along ever since.

Cool deal!