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*

There is a limit of 64K in a module, so try splitting the procedures across
multiple modules.

--

HTH

Bob

"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

How do you find the current size of a module?

Robert

"Bob Phillips" wrote in message
...

There is a limit of 64K in a module, so try splitting the procedures
across multiple modules.

--

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

--

HTH

Bob

"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

On Mar 13, 5:15*pm, "Joe User" <joeu2004 wrote:
"Bob Phillips" 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" wrote in message

...



There is a limit of 64K in a module, so try splitting the procedures
across multiple modules.

--

HTH

Bob

"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

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

On Sat, 13 Mar 2010 16:01:05 -0800 (PST), Canlink 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.

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

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.

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" wrote in message
...
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.

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

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

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





On Sat, 13 Mar 2010 14:14:29 -0700, "Robert Crandal"
wrote:

How do you find the current size of a module?

Robert

"Bob Phillips" wrote in message
...

There is a limit of 64K in a module, so try splitting the procedures
across multiple modules.

--

On Mar 14, 3:22*pm, Chip Pearson wrote:
How do you find the current size of a module?

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

On Sat, 13 Mar 2010 14:14:29 -0700, "Robert Crandal"



wrote:
How do you find the current size of a module?

Robert

"Bob Phillips" wrote in message
...

There is a limit of 64K in a module, so try splitting the procedures
across multiple modules.

--

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.