VBA Excel Macro worked for 2-years now dosen't??
in [Programming]
|
Prev: Auto complete a row
Next: Userforms & worksheets
From: Canlink on 13 Mar 2010 12:17 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: Bob Phillips on 13 Mar 2010 12:57 There is a limit of 64K in a module, so try splitting the procedures across multiple modules. -- HTH Bob "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: Robert Crandal on 13 Mar 2010 16:14 How do you find the current size of a module? Robert "Bob Phillips" <bob.phillips(a)somewhere.com> wrote in message news:uTDsiatwKHA.3536(a)TK2MSFTNGP06.phx.gbl... > > There is a limit of 64K in a module, so try splitting the procedures > across multiple modules. > > -- >
From: Joe User on 13 Mar 2010 17:15 "Bob Phillips" <bob.phillips(a)somewhere.com> wrote: > There is a limit of 64K in a module, so try splitting > the procedures across multiple modules. 64K what? Also, what would I type into VBA Help to discover this and any other limitations? Nothing I tried seems to work. But it's easy to overlook the obvious with all the seemingly irrelevant links that a Help search often spits out. PS: It is unusual, even for MS, for a product to regress in limitations. So why would >64K whatever work 2 years ago, but not now, if that is indeed the problem? Or are you assuming "Greg House" rules? ;-) ----- original message ----- "Bob Phillips" <bob.phillips(a)somewhere.com> wrote in message news:uTDsiatwKHA.3536(a)TK2MSFTNGP06.phx.gbl... > There is a limit of 64K in a module, so try splitting the procedures > across multiple modules. > > -- > > HTH > > Bob > > "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 13 Mar 2010 19:01
On Mar 13, 5:15 pm, "Joe User" <joeu2004> wrote: > "Bob Phillips" <bob.phill...(a)somewhere.com> wrote: > > There is a limit of 64K in a module, so try splitting > > the procedures across multiple modules. > > 64K what? > > Also, what would I type into VBA Help to discover this and any other > limitations? > > Nothing I tried seems to work. But it's easy to overlook the obvious with > all the seemingly irrelevant links that a Help search often spits out. > > PS: It is unusual, even for MS, for a product to regress in limitations. > So why would >64K whatever work 2 years ago, but not now, if that is indeed > the problem? Or are you assuming "Greg House" rules? ;-) > > ----- original message ----- > > "Bob Phillips" <bob.phill...(a)somewhere.com> wrote in message > > news:uTDsiatwKHA.3536(a)TK2MSFTNGP06.phx.gbl... > > > > > There is a limit of 64K in a module, so try splitting the procedures > > across multiple modules. > > > -- > > > HTH > > > Bob > > > "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 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. |