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(strSh eetName).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 WHE=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

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é wrote:
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(strSh eetName).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 WHE=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

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é

"Jim Jackson" wrote:

Do you mean "ctrl/Break" causes a complete stop? I would place several
"Stop"s in the code and run it, assessing the situation at each stop. It
could be that, if you add "Do....Loop until (criteria is met)" the problem
can be overcome.

If you can provide more detail, please do so and more ideas can be offered.
--
Best wishes,

Jim


"René" wrote:

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(strSh eetName).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 WHE=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

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é" wrote in message
...
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é

"Jim Jackson" wrote:

Do you mean "ctrl/Break" causes a complete stop? I would place several
"Stop"s in the code and run it, assessing the situation at each stop. It
could be that, if you add "Do....Loop until (criteria is met)" the
problem
can be overcome.

If you can provide more detail, please do so and more ideas can be
offered.
--
Best wishes,

Jim


"René" wrote:

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(strSh eetName).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 WHE=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

Thanks Chip,

I'll give this a try

greetings
René

"Chip Pearson" wrote:

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é" wrote in message
...
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é

"Jim Jackson" wrote:

Do you mean "ctrl/Break" causes a complete stop? I would place several
"Stop"s in the code and run it, assessing the situation at each stop. It
could be that, if you add "Do....Loop until (criteria is met)" the
problem
can be overcome.

If you can provide more detail, please do so and more ideas can be
offered.
--
Best wishes,

Jim


"René" wrote:

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(strSh eetName).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 WHE=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