VBA Excel Macro worked for 2-years now dosen't??

  • Thread starter Thread starter Canlink
  • Start date Start date
C

Canlink

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 
 
There is a limit of 64K in a module, so try splitting the procedures across
multiple modules.

--

HTH

Bob

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
 
Bob Phillips said:
There is a limit of 64K in a module, so try splitting
the procedures across multiple modules.
Click to expand...

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 -----
 
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 -----
Click to expand...

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


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


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
Click to expand...

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.
Click to expand...
This is extremely confusing to me and is not good for my client
relations either.
 
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


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


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
Click to expand...

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.
Click to expand...
This is extremely confusing to me and is not good for my client
relations either.
 
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













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

This is extremely confusing to me and is not good for my client
relations either.
Click to expand...

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".
 
How do you find the current size of a module?

You really can't. The 64K limit is on the size of "compiled" code,
not the size to the text source code. (VBA code is never stored as
text within the workbook. It is stored in an intermediate byte-code
language called OpCodes -- similar in theory to Java -- and at runtime
or when you choose Compile from the Debug menu, VBA converts the
OpCodes, which are version/platform neutral, to ExCodes, which are
version specific and feeds those ExCodes to the VBA interpreter
runtime which executes machine code on behalf of VBA based on the
ExCodes. What you see on the editor screen as text code is the OpCodes
translated to text for display.)

Exporting the module to a text file and looking at the size of that
file might give you a crude approximation of the compiled size, but I
wouldn't give it much credibility. As far as I know, the 64K limit
isn't publicly documented. It was revealed to an MVP by a Softie and
has propagated via usenet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
You really can't.  The 64K limit is on the size of "compiled" code,notthe size to the text source code. (VBA code is never stored as
text within the workbook. It is stored in an intermediate byte-code
language called OpCodes -- similar in theory to Java -- and at runtime
or when you choose Compile from the Debug menu, VBA converts the
OpCodes, which are version/platform neutral, to ExCodes, which are
version specific and feeds those ExCodes to the VBA interpreter
runtime which executes machine code on behalf of VBA based on the
ExCodes. What you see on the editor screen as text code is the OpCodes
translated to text for display.)

Exporting the module to a text file and looking at the size of that
file might give you a crude approximation of the compiled size, but I
wouldn't give it much credibility. As far as I know, the 64K limit
isn't publicly documented. It was revealed to an MVP by a Softie and
has propagated via usenet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
        Excel, 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
Click to expand...

I would like to thank all that posted their comments. Rob Bovey's
CodeCleaner add-in did the work!! THANKS TO ALL amd to Rob Bovey's
code.
 
Back
Top