VBA Excel Macro worked for 2-years now dosen't??
in [Programming]
|
Prev: Auto complete a row
Next: Userforms & worksheets
From: Gord Dibben on 13 Mar 2010 19:30 Export a module and note any change in file size before and after? I have no idea if that will tell you anything<g> Gord Dibben MS Excel MVP On Sat, 13 Mar 2010 16:01:05 -0800 (PST), Canlink <canlink(a)gmail.com> wrote: >I always use more than one module, I learned also that their is a >limit on the size of a module, but I did not know it was 64K and I do >not know how to measure the size of each module. >The "shProtect" procedure is part of the standard module I use for >numerous applications. The "VacUsed" procedure is again separate and >only consists of a total of three procedures unique to this >spreadsheet.
From: Peter T on 14 Mar 2010 07:37 64k as a limit has been reported by some but I'm not sure there's any evidence that such a defined limit exists. I have (unwisely) had much more than that in one module without problems. If there is a limit it might be due to other factors, eg lines of actual code, number of procedures, callers, variables, very difficult to pin point. Obviously from a design point of view it's bad practice to include that much in one module, but that's a different matter. However modern systems can include several mg, or +100k lines of code (exclusive of white space & comments). As to why your code is suddenly not working it must surely be because something somewhere has changed. Try and explain what you mean by "doesn't work" Regards, Peter T "Canlink" <canlink(a)gmail.com> wrote in message news:f7a88471-4367-412d-ba11-9fd760cecfa6(a)q16g2000yqq.googlegroups.com... Is there a limit on how much code you can place in a VBA file? All works well except the macro I call "VacUsed" It is called from a couple of procedures I post the last procedure "ThisWorkBook" use to close and save the workbook. Private Sub Workbook_BeforeClose(Cancel As Boolean) Call FilterTestOff Call VacUsed Call DeleteMenu Call AllProtect Sheets("VacationAccrued").Activate End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call AllProtect Sheets("VacationAccrued").Activate End Sub And this is the VacUsed Procedure: Sub VacUsed() ' ' VacUsed Macro ' Macro recorded 5/16/2008 by Geoffrey Feldman ' ' Stores "Vacation Days Taken" from Vacation Accured Sheet ' Set Wkb = ActiveWorkbook Set ShtA = Wkb.Worksheets("VacationAccrued") inLRw = ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row Set ShtS = Wkb.Worksheets("VacUsedStorage") ShtS.Activate Call shUnprotect ShtS.Range("B2:C1000").ClearContents ' Update VacUsed Names from VacAccrue ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B" & inLRw).Value ' Update VacUsed Days Taken from VacAccrue ShtS.Range("C2:C" & inLRw - 1).Value = ShtA.Range("I3:I" & inLRw).Value ShtS.Columns("B:C").EntireColumn.AutoFit Range("B2").Select Application.CutCopyMode = False Call shProtect ShtA.Activate Range("B3").Select End Sub The macro skips the call "shUnProtect" which is needed to continue the update process Your expert help would be appreciated
From: Canlink on 14 Mar 2010 13:03 On Mar 14, 7:37 am, "Peter T" <peter_t(a)discussions> wrote: > 64k as a limit has been reported by some but I'm not sure there's any > evidence that such a defined limit exists. I have (unwisely) had much more > than that in one module without problems. If there is a limit it might be > due to other factors, eg lines of actual code, number of procedures, > callers, variables, very difficult to pin point. > > Obviously from a design point of view it's bad practice to include that much > in one module, but that's a different matter. However modern systems can > include several mg, or +100k lines of code (exclusive of white space & > comments). > > As to why your code is suddenly not working it must surely be because > something somewhere has changed. Try and explain what you mean by "doesn't > work" > > Regards, > Peter T > > "Canlink" <canl...(a)gmail.com> wrote in message > > news:f7a88471-4367-412d-ba11-9fd760cecfa6(a)q16g2000yqq.googlegroups.com... > Is there a limit on how much code you can place in a VBA file? > All works well except the macro I call "VacUsed" > It is called from a couple of procedures I post the last > procedure "ThisWorkBook" use to close and save the workbook. > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > Call FilterTestOff > Call VacUsed > Call DeleteMenu > Call AllProtect > Sheets("VacationAccrued").Activate > End Sub > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel > As Boolean) > Call AllProtect > Sheets("VacationAccrued").Activate > End Sub > > And this is the VacUsed Procedure: > Sub VacUsed() > ' > ' VacUsed Macro > ' Macro recorded 5/16/2008 by Geoffrey Feldman > ' > ' Stores "Vacation Days Taken" from Vacation Accured Sheet > ' > Set Wkb = ActiveWorkbook > Set ShtA = Wkb.Worksheets("VacationAccrued") > inLRw = ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row > Set ShtS = Wkb.Worksheets("VacUsedStorage") > ShtS.Activate > Call shUnprotect > ShtS.Range("B2:C1000").ClearContents > ' Update VacUsed Names from VacAccrue > ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B" > & inLRw).Value > ' Update VacUsed Days Taken from VacAccrue > ShtS.Range("C2:C" & inLRw - 1).Value = ShtA.Range("I3:I" > & inLRw).Value > ShtS.Columns("B:C").EntireColumn.AutoFit > Range("B2").Select > Application.CutCopyMode = False > Call shProtect > ShtA.Activate > Range("B3").Select > End Sub > The macro skips the call "shUnProtect" which is needed to continue > the > update process > > Your expert help would be appreciated OK I will explain what happens: You run the procedure that I call "VacUsed" directly and it works fine, However when I call the same procedure from another routine like my closing routine I can no longer rely upon it! It seems to skip 3- lines of code "ShtS.Activate: "Call shUnprotect" and "ShtS.Range("B2:C1000").ClearContents" it is as if they are considered info lines and not action lines. It then stops at the line " ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B" > & inLRw).Value" with an error that states you have not unprotected the worksheet. This is extremely confusing to me and is not good for my client relations either.
From: Peter T on 14 Mar 2010 13:43 What do you mean by "it seems to skip 3-lines of code". It should be very easy to determine if they are working or not with some simple debug lines, eg debug.print activesheet.name ShtS.Activate debug.print activesheet.name, ShtS.name, ShtS.ProtectContents Call shUnprotect debug.print activesheet.name, ShtS.name, ShtS.ProtectContents ' in the first line of shUnprotect debug.print "shUnprotect" Looks like your sheet is not getting unprotected for some reason Lines of code don't suddenly get skipped unless, just conceivably, there is some severe corruption in the project. To eliminate that possibility clean the project with Rob Bovey's CodeCleaner addin http://www.appspro.com/Utilities/CodeCleaner.htm Regards, Peter T "Canlink" <canlink(a)gmail.com> wrote in message news:f63477d3-8d5b-48d8-b7bb-488634ce1406(a)t20g2000yqe.googlegroups.com... On Mar 14, 7:37 am, "Peter T" <peter_t(a)discussions> wrote: > 64k as a limit has been reported by some but I'm not sure there's any > evidence that such a defined limit exists. I have (unwisely) had much more > than that in one module without problems. If there is a limit it might be > due to other factors, eg lines of actual code, number of procedures, > callers, variables, very difficult to pin point. > > Obviously from a design point of view it's bad practice to include that > much > in one module, but that's a different matter. However modern systems can > include several mg, or +100k lines of code (exclusive of white space & > comments). > > As to why your code is suddenly not working it must surely be because > something somewhere has changed. Try and explain what you mean by "doesn't > work" > > Regards, > Peter T > > "Canlink" <canl...(a)gmail.com> wrote in message > > news:f7a88471-4367-412d-ba11-9fd760cecfa6(a)q16g2000yqq.googlegroups.com... > Is there a limit on how much code you can place in a VBA file? > All works well except the macro I call "VacUsed" > It is called from a couple of procedures I post the last > procedure "ThisWorkBook" use to close and save the workbook. > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > Call FilterTestOff > Call VacUsed > Call DeleteMenu > Call AllProtect > Sheets("VacationAccrued").Activate > End Sub > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel > As Boolean) > Call AllProtect > Sheets("VacationAccrued").Activate > End Sub > > And this is the VacUsed Procedure: > Sub VacUsed() > ' > ' VacUsed Macro > ' Macro recorded 5/16/2008 by Geoffrey Feldman > ' > ' Stores "Vacation Days Taken" from Vacation Accured Sheet > ' > Set Wkb = ActiveWorkbook > Set ShtA = Wkb.Worksheets("VacationAccrued") > inLRw = ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row > Set ShtS = Wkb.Worksheets("VacUsedStorage") > ShtS.Activate > Call shUnprotect > ShtS.Range("B2:C1000").ClearContents > ' Update VacUsed Names from VacAccrue > ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B" > & inLRw).Value > ' Update VacUsed Days Taken from VacAccrue > ShtS.Range("C2:C" & inLRw - 1).Value = ShtA.Range("I3:I" > & inLRw).Value > ShtS.Columns("B:C").EntireColumn.AutoFit > Range("B2").Select > Application.CutCopyMode = False > Call shProtect > ShtA.Activate > Range("B3").Select > End Sub > The macro skips the call "shUnProtect" which is needed to continue > the > update process > > Your expert help would be appreciated OK I will explain what happens: You run the procedure that I call "VacUsed" directly and it works fine, However when I call the same procedure from another routine like my closing routine I can no longer rely upon it! It seems to skip 3- lines of code "ShtS.Activate: "Call shUnprotect" and "ShtS.Range("B2:C1000").ClearContents" it is as if they are considered info lines and not action lines. It then stops at the line " ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B" > & inLRw).Value" with an error that states you have not unprotected the > worksheet. This is extremely confusing to me and is not good for my client relations either.
From: Canlink on 14 Mar 2010 14:51
On Mar 14, 1:43 pm, "Peter T" <peter_t(a)discussions> wrote: > What do you mean by "it seems to skip 3-lines of code". It should be very > easy to determine if they are working or not with some simple debug lines, > eg > > debug.print activesheet.name > ShtS.Activate > debug.print activesheet.name, ShtS.name, ShtS.ProtectContents > Call shUnprotect > debug.print activesheet.name, ShtS.name, ShtS.ProtectContents > > ' in the first line of shUnprotect > debug.print "shUnprotect" > > Looks like your sheet is not getting unprotected for some reason > > Lines of code don't suddenly get skipped unless, just conceivably, there is > some severe corruption in the project. To eliminate that possibility clean > the project with Rob Bovey's CodeCleaner addinhttp://www.appspro.com/Utilities/CodeCleaner.htm > > Regards, > Peter T > > "Canlink" <canl...(a)gmail.com> wrote in message > > news:f63477d3-8d5b-48d8-b7bb-488634ce1406(a)t20g2000yqe.googlegroups.com... > On Mar 14, 7:37 am, "Peter T" <peter_t(a)discussions> wrote: > > > > > > > 64k as a limit has been reported by some but I'm not sure there's any > > evidence that such a defined limit exists. I have (unwisely) had much more > > than that in one module without problems. If there is a limit it might be > > due to other factors, eg lines of actual code, number of procedures, > > callers, variables, very difficult to pin point. > > > Obviously from a design point of view it's bad practice to include that > > much > > in one module, but that's a different matter. However modern systems can > > include several mg, or +100k lines of code (exclusive of white space & > > comments). > > > As to why your code is suddenly not working it must surely be because > > something somewhere has changed. Try and explain what you mean by "doesn't > > work" > > > Regards, > > Peter T > > > "Canlink" <canl...(a)gmail.com> wrote in message > > >news:f7a88471-4367-412d-ba11-9fd760cecfa6(a)q16g2000yqq.googlegroups.com.... > > Is there a limit on how much code you can place in a VBA file? > > All works well except the macro I call "VacUsed" > > It is called from a couple of procedures I post the last > > procedure "ThisWorkBook" use to close and save the workbook. > > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > Call FilterTestOff > > Call VacUsed > > Call DeleteMenu > > Call AllProtect > > Sheets("VacationAccrued").Activate > > End Sub > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel > > As Boolean) > > Call AllProtect > > Sheets("VacationAccrued").Activate > > End Sub > > > And this is the VacUsed Procedure: > > Sub VacUsed() > > ' > > ' VacUsed Macro > > ' Macro recorded 5/16/2008 by Geoffrey Feldman > > ' > > ' Stores "Vacation Days Taken" from Vacation Accured Sheet > > ' > > Set Wkb = ActiveWorkbook > > Set ShtA = Wkb.Worksheets("VacationAccrued") > > inLRw = ShtA.Cells(Rows.Count, "B").End(xlUp).End(xlUp).Row > > Set ShtS = Wkb.Worksheets("VacUsedStorage") > > ShtS.Activate > > Call shUnprotect > > ShtS.Range("B2:C1000").ClearContents > > ' Update VacUsed Names from VacAccrue > > ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B" > > & inLRw).Value > > ' Update VacUsed Days Taken from VacAccrue > > ShtS.Range("C2:C" & inLRw - 1).Value = ShtA.Range("I3:I" > > & inLRw).Value > > ShtS.Columns("B:C").EntireColumn.AutoFit > > Range("B2").Select > > Application.CutCopyMode = False > > Call shProtect > > ShtA.Activate > > Range("B3").Select > > End Sub > > The macro skips the call "shUnProtect" which is needed to continue > > the > > update process > > > Your expert help would be appreciated > > OK I will explain what happens: > You run the procedure that I call "VacUsed" directly and it works > fine, However when I call the same procedure from another routine like > my closing routine I can no longer rely upon it! It seems to skip 3- > lines of code "ShtS.Activate: "Call shUnprotect" and > "ShtS.Range("B2:C1000").ClearContents" it is as if they are > considered info lines and not action lines. It then stops at the line > " ShtS.Range("B2:B" & inLRw - 1).Value = ShtA.Range("B3:B"> & inLRw).Value" with an error that states you have not unprotected the > > worksheet. > > This is extremely confusing to me and is not good for my client > relations either. Thanks for the cleanup tip, I will send the project back to the client and we will see if it works tomorrow. I was making changes to other modules and to another procedure in the "contaminated" module but I did not touch the procedure "VacUsed". I also added the Debug.Print suggestion so that I can go on-line with my client and watch again what happens. All seemed to work once I ran "Clean Up". |