From: Gord Dibben on
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
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
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
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
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".
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Auto complete a row
Next: Userforms & worksheets