On Mar 14, 7:37*am, "Peter T" <peter_t@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" wrote in message

...
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 Procedu
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.