|
Code stops for no apparent reason
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 |
Code stops for no apparent reason
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 |
Code stops for no apparent reason
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 |
Code stops for no apparent reason
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 |
Code stops for no apparent reason
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 |
| All times are GMT +1. The time now is 02:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com