Code stops for no apparent reason

[René]

Hello,

I wrote some code, that creates multiple charts (more than 10 or even 20).
The code works fine, but after a short while, it stops for no apparent
reason, while VBE indicates that the code is still running. If I want to stop
it, Excel quits.
I wonder what is causing this. Does someone have the answer? I has probably
to do with the loop, I guess.
See below for the code.

greetings
René

Sub CreateDiskChart()

Dim Bereik
Dim strSheetName As String, strSheetName2 As String, strChartTitle As
String, strWorkBook As String
Dim intRow As Integer, intCharts As Integer

strSheetName = ActiveSheet.Name
Sheets.Add
strSheetName2 = ActiveSheet.Name
strWorkBook = ActiveWorkbook.Name
intCharts =
Application.WorksheetFunction.CountIf(Sheets(strSheetName).Range("A:A"),
"customer")
Sheets(strSheetName).Select
Cells(1).Select

For n = 1 To intCharts
intRow = ActiveCell.Row
Set Bereik = Range(Cells(intRow, 4), Cells(Cells(intRow,
1).CurrentRegion.Rows.Count + intRow - 1, 7))
Range(Cells(intRow, 4), Cells(Cells(intRow,
1).CurrentRegion.Rows.Count + intRow - 1, 7)).Select
strChartTitle = ActiveCell.Item(2, 0).Value & " (" &
MonthName(ActiveCell.Item(2, -1).Value, False) & ")"

Charts.Add
ActiveChart.ChartType = xlColumnClustered

ActiveChart.SetSourceData Source:=Bereik, PlotBy:= _
xlColumns
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
ActiveChart.Location WHERE:=xlLocationAsObject, Name:=strSheetName2


With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = strChartTitle
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Disk"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Percentage used"
.SeriesCollection(3).Select
.ChartGroups(1).SeriesCollection(3).PlotOrder = 1
End With
z = ActiveSheet.ChartObjects.Count
ActiveSheet.Shapes(z).IncrementLeft 50 + 10 * n
ActiveSheet.Shapes(z).IncrementTop 50 + 10 * n


Workbooks(strWorkBook).Activate
Sheets(strSheetName).Select
ActiveCell.End(xlDown).Select
ActiveCell.End(xlDown).Select

Sheets(strSheetName).Select
Next n
Range("A1").Select
End Sub

 

[gimme_this_gimme_that]

Your code could be iterating through an infinite loop because of the
inCharts assignment might result in questionable values.

In anycase the clip below should be rewritten so there is a single
assignment to the Worksheet from a two dimensional array. This code is
so inefficent that it's not even funny.

For n = 1 To intCharts
intRow = ActiveCell.Row
Set Bereik = Range(Cells(intRow, 4), Cells(Cells(intRow,
1).CurrentRegion.Rows.Count + intRow - 1, 7))
Range(Cells(intRow, 4), Cells(Cells(intRow,
1).CurrentRegion.Rows.Count + intRow - 1, 7)).Select
strChartTitle = ActiveCell.Item(2, 0).Value & " (" &
MonthName(ActiveCell.Item(2, -1).Value, False) & ")"

 

[René]

Hi Jim

I already solved the problem by stepping through the code with F8. The
strange thing is that it stops at a certain lign, but that no error is given.
Thanks for the effort.

René

 

[Chip Pearson]

It is quite possible that VBA hasn't cleaned up its internal storage of the
code. This can cause very odd errors, including the one you describe. The
solution is to Export the VBA modules to text files, Remove the modules from
the project, and Import the text files back into the project.

Rob Bovey has an excellent add-in that automates all of this. See
http://www.appspro.com/Utilities/CodeCleaner.htm for details. Rob's Code
Cleaner is a "must have" for serious Excel developers.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[René]

Thanks Chip,

I'll give this a try

greetings
René