|
Macro suffers from premature exiting
One of my macros, which has been working for years, now quits prematurely. It
will consistently quit about the 5th statement in on a activesheet.name command. It executes the command, but then quits, with no error message. If I step past this command, it will quit about 5 statements later on a copy command (after executing it as well). The project is 3 modules long, and about 1000 statements in each module. I've exported each module to a .bas file, and reimported them, to hopefully get rid of any fragmentation. There is no protection on the workbook. I'm using XL2002 SP3 on Windows XP. Any suggestions from the gurus? -- Regards, Fred |
Macro suffers from premature exiting
That's a lot of macro. Perhaps your macro needs to be cleaned up. Post some
of it. Maybe you have a lot of unnecessary and undesirable selections such as: activesheet.name instead of sheets("mysheet").name As suggested, post some of it -- Don Guillett SalesAid Software "Fred Smith" wrote in message ... One of my macros, which has been working for years, now quits prematurely. It will consistently quit about the 5th statement in on a activesheet.name command. It executes the command, but then quits, with no error message. If I step past this command, it will quit about 5 statements later on a copy command (after executing it as well). The project is 3 modules long, and about 1000 statements in each module. I've exported each module to a .bas file, and reimported them, to hopefully get rid of any fragmentation. There is no protection on the workbook. I'm using XL2002 SP3 on Windows XP. Any suggestions from the gurus? -- Regards, Fred |
Macro suffers from premature exiting
Fred,
A snippet of code would be helpful here just to be curious about it. If it's been wokring for years, though, you might try running your code through Rob Bovey's wonderful "CleanPorject" utility. It clears up a lot of odd and inexplicable behvaior, particularly for long-used (and long) VBA code. You can find it at: http://www.appspro.com/Utilities/CodeCleaner.htm = Marchand = On Feb 11, 1:54 pm, "Fred Smith" wrote: One of my macros, which has been working for years, now quits prematurely. It will consistently quit about the 5th statement in on a activesheet.name command. It executes the command, but then quits, with no error message. If I step past this command, it will quit about 5 statements later on a copy command (after executing it as well). The project is 3 modules long, and about 1000 statements in each module. I've exported each module to a .bas file, and reimported them, to hopefully get rid of any fragmentation. There is no protection on the workbook. I'm using XL2002 SP3 on Windows XP. Any suggestions from the gurus? -- Regards, Fred |
Macro suffers from premature exiting
Thanks Marchand, I'll take a look at CleanProject.
The code starts out as follows: Option Explicit ' About 30 Dims Sub PrepareMeetingPackage() ' Format Holdings sheet On Error Resume Next ValueAsof = InputBox("Verify valuation as of date", , Date - 1) If Err < 0 Then Exit Sub 'Cancel clicked Set Book1 = ActiveWorkbook ActiveSheet.Name = "Export" 'After executing this statement, the macro quits Range("C2").Select 'If I step around the above, macro quits at next ActiveSheet ActiveWindow.FreezePanes = True On Error Resume Next Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True On Error GoTo 0 Sheets.Add ActiveSheet.Name = "Holdings" 'If I step around this statement, macro quits at the copy DLCol = Application.Match("Security", Sheets("Export").Range("1:1"), 0) If IsError(DLCol) Then GoTo MissingData Sheets("Export").Columns(DLCol).Copy Destination:=Columns("B") ' About 500 more lines of code which used to work ' Code in other modules has been changed recently, but not this one. -- Regards, Fred wrote in message oups.com... Fred, A snippet of code would be helpful here just to be curious about it. If it's been wokring for years, though, you might try running your code through Rob Bovey's wonderful "CleanPorject" utility. It clears up a lot of odd and inexplicable behvaior, particularly for long-used (and long) VBA code. You can find it at: http://www.appspro.com/Utilities/CodeCleaner.htm = Marchand = On Feb 11, 1:54 pm, "Fred Smith" wrote: One of my macros, which has been working for years, now quits prematurely. It will consistently quit about the 5th statement in on a activesheet.name command. It executes the command, but then quits, with no error message. If I step past this command, it will quit about 5 statements later on a copy command (after executing it as well). The project is 3 modules long, and about 1000 statements in each module. I've exported each module to a .bas file, and reimported them, to hopefully get rid of any fragmentation. There is no protection on the workbook. I'm using XL2002 SP3 on Windows XP. Any suggestions from the gurus? -- Regards, Fred |
Macro suffers from premature exiting
Fred,
On Feb 12, 8:28 pm, "Fred Smith" wrote: ThanksMarchand, I'll take a look at CleanProject. The code starts out as follows: Option Explicit ' About 30 Dims Sub PrepareMeetingPackage() ' Format Holdings sheet On Error Resume Next ValueAsof = InputBox("Verify valuation as of date", , Date - 1) If Err < 0 Then Exit Sub 'Cancel clicked Set Book1 = ActiveWorkbook ActiveSheet.Name = "Export" 'After executing this statement, the macro quits Range("C2").Select 'If I step around the above, macro quits at next ActiveSheet ActiveWindow.FreezePanes = True On Error Resume Next Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True On Error GoTo 0 Sheets.Add ActiveSheet.Name = "Holdings" 'If I step around this statement, macro quits at the copy DLCol = Application.Match("Security", Sheets("Export").Range("1:1"), 0) If IsError(DLCol) Then GoTo MissingData Sheets("Export").Columns(DLCol).Copy Destination:=Columns("B") ' About 500 more lines of code which used to work ' Code in other modules has been changed recently, but not this one. -- Regards, Fred wrote in message oups.com... Fred, A snippet of code would be helpful here just to be curious about it. If it's been wokring for years, though, you might try running your code through Rob Bovey's wonderful "CleanPorject" utility. It clears up a lot of odd and inexplicable behvaior, particularly for long-used (and long) VBA code. You can find it at: http://www.appspro.com/Utilities/CodeCleaner.htm =Marchand= On Feb 11, 1:54 pm, "Fred Smith" wrote: One of my macros, which has been working for years, now quits prematurely. It will consistently quit about the 5th statement in on a activesheet.name command. It executes the command, but then quits, with no error message. If I step past this command, it will quit about 5 statements later on a copy command (after executing it as well). The project is 3 modules long, and about 1000 statements in each module. I've exported each module to a .bas file, and reimported them, to hopefully get rid of any fragmentation. There is no protection on the workbook. I'm using XL2002 SP3 on Windows XP. Any suggestions from the gurus? -- Regards, Fred |
Macro suffers from premature exiting
Fred,
I you take out the "On Error Resume Next" statement, what VBA error report do you see? Is it something as simple as a Worksheet named "Export" already exists in the file? = M = On Feb 12, 8:28 pm, "Fred Smith" wrote: ThanksMarchand, I'll take a look at CleanProject. The code starts out as follows: Option Explicit ' About 30 Dims Sub PrepareMeetingPackage() ' Format Holdings sheet On Error Resume Next ValueAsof = InputBox("Verify valuation as of date", , Date - 1) If Err < 0 Then Exit Sub 'Cancel clicked Set Book1 = ActiveWorkbook ActiveSheet.Name = "Export" 'After executing this statement, the macro quits Range("C2").Select 'If I step around the above, macro quits at next ActiveSheet ActiveWindow.FreezePanes = True On Error Resume Next Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True On Error GoTo 0 Sheets.Add ActiveSheet.Name = "Holdings" 'If I step around this statement, macro quits at the copy DLCol = Application.Match("Security", Sheets("Export").Range("1:1"), 0) If IsError(DLCol) Then GoTo MissingData Sheets("Export").Columns(DLCol).Copy Destination:=Columns("B") ' About 500 more lines of code which used to work ' Code in other modules has been changed recently, but not this one. -- Regards, Fred wrote in message oups.com... Fred, A snippet of code would be helpful here just to be curious about it. If it's been wokring for years, though, you might try running your code through Rob Bovey's wonderful "CleanPorject" utility. It clears up a lot of odd and inexplicable behvaior, particularly for long-used (and long) VBA code. You can find it at: http://www.appspro.com/Utilities/CodeCleaner.htm =Marchand= On Feb 11, 1:54 pm, "Fred Smith" wrote: One of my macros, which has been working for years, now quits prematurely. It will consistently quit about the 5th statement in on a activesheet.name command. It executes the command, but then quits, with no error message. If I step past this command, it will quit about 5 statements later on a copy command (after executing it as well). The project is 3 modules long, and about 1000 statements in each module. I've exported each module to a .bas file, and reimported them, to hopefully get rid of any fragmentation. There is no protection on the workbook. I'm using XL2002 SP3 on Windows XP. Any suggestions from the gurus? -- Regards, Fred |
Macro suffers from premature exiting
I found it!
It's got nothing to do with the existing macro, because it's worked hundreds of times before, and I never changed it. So it had to be something else in the project. In another module, I added a function to determine whether a cell had a formula in it. The function added was: Function Overridden(cell As Range) As Boolean Overridden = Not cell.Hasformula End Function As soon as I took this out, my PrepareMeetingPackage started working again. While I'm curious why adding this function causes another macro in another Module to quit prematurely, I don't really need to know. That's for Microsoft to worry about. But I really would like to get this function working. Can you see anything I'm doing wrong with this function? Thanks for your help, Fred. -- Regards, Fred wrote in message oups.com... Fred, I you take out the "On Error Resume Next" statement, what VBA error report do you see? Is it something as simple as a Worksheet named "Export" already exists in the file? = M = On Feb 12, 8:28 pm, "Fred Smith" wrote: ThanksMarchand, I'll take a look at CleanProject. The code starts out as follows: Option Explicit ' About 30 Dims Sub PrepareMeetingPackage() ' Format Holdings sheet On Error Resume Next ValueAsof = InputBox("Verify valuation as of date", , Date - 1) If Err < 0 Then Exit Sub 'Cancel clicked Set Book1 = ActiveWorkbook ActiveSheet.Name = "Export" 'After executing this statement, the macro quits Range("C2").Select 'If I step around the above, macro quits at next ActiveSheet ActiveWindow.FreezePanes = True On Error Resume Next Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True On Error GoTo 0 Sheets.Add ActiveSheet.Name = "Holdings" 'If I step around this statement, macro quits at the copy DLCol = Application.Match("Security", Sheets("Export").Range("1:1"), 0) If IsError(DLCol) Then GoTo MissingData Sheets("Export").Columns(DLCol).Copy Destination:=Columns("B") ' About 500 more lines of code which used to work ' Code in other modules has been changed recently, but not this one. -- Regards, Fred wrote in message oups.com... Fred, A snippet of code would be helpful here just to be curious about it. If it's been wokring for years, though, you might try running your code through Rob Bovey's wonderful "CleanPorject" utility. It clears up a lot of odd and inexplicable behvaior, particularly for long-used (and long) VBA code. You can find it at: http://www.appspro.com/Utilities/CodeCleaner.htm =Marchand= On Feb 11, 1:54 pm, "Fred Smith" wrote: One of my macros, which has been working for years, now quits prematurely. It will consistently quit about the 5th statement in on a activesheet.name command. It executes the command, but then quits, with no error message. If I step past this command, it will quit about 5 statements later on a copy command (after executing it as well). The project is 3 modules long, and about 1000 statements in each module. I've exported each module to a .bas file, and reimported them, to hopefully get rid of any fragmentation. There is no protection on the workbook. I'm using XL2002 SP3 on Windows XP. Any suggestions from the gurus? -- Regards, Fred |
| All times are GMT +1. The time now is 07:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com