Module Limits

[Guest]

I do a lot of VB programming in the modules, which take data from a
spreadsheet, perform a calcualtion and write the answer back to the
spreadsheet. I have a lot of code in a number of modules. At a certain
point, when I add code to a module, it stops working. Moving the code to
another module ususally corrects the problem.

My question: Is there a limit on the number or size of each module?

 

[Chip Pearson]

There is no hard limit on the number of lines of code in a
module, but the compiled size of the code in the module must be
less than 64K. I tend to keep modules less than about 3K lines
of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Richard Weatherly" <Richard (e-mail address removed)>
wrote in message
news:[email protected]...

 

[Guest]

Thanks for the info, Chip.

How do you know how much the compiled size is?

 

[Chip Pearson]

Unfortunately, there is no way to determine the size of a module,
other than number of lines of code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Richard Weatherly" <[email protected]>
wrote in message

 

[Alex J]

Chip,
Two ideas on module size that I have used - please advise if these are not
as effective as I had thought;
1. Export the module and view the file size using Windows Explorer
2. VBE Tools 2.0 Add-In from Office Automation (http://www.oaltd.co.uk ) has
a module size indicator in its toolbar.

Alex J

 

[Robin Hammond]

You should also get Rob Bovey's VBA Code Cleaner at www.appspro.com