There are limits both to the size of a single procedure and to the
size of a module, but MS doesn't publish them (probably because they
are limits on the internal representation of the VBA code, not the
code as it exists as editable text).

However, if you get a message like "Procedure Too Large" or "Module
Too Large", you have long passed the practical limitations. Neither a
single procedure nor a single module should be anywhere near the
maximum size allowed by VBA. Good programming practice and
organization would have you break large procedures into multiple,
smaller, and reusable procedures and organize those procedures into
various modules based on functionality.

It is a known problem, however, that VBA doesn't always clean up after
itself if the code has been heavily edited. There is junk code left in
VBA's internal storage areas and this can cause some very weird
behavior, behavior that is often impossible to reliably reproduce and
debug. The solution is to export all VBA code out to text files, purge
it from VBA, and the re-import all the code from the text files, in
order that VBA starts from a clean slate. Rob Bovey has a must-have
utility called Code Cleaner that completely automates this
export/import process. See
http://www.appspro.com/Utilities/CodeCleaner.htm . Every developer
should have and use Rob's Code Cleaner.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Thu, 10 Sep 2009 02:49:01 -0700, Jock
wrote:

Is there a practicalimit on the amount of code you can use in various
modules, user forms and sheets within a workbook?
I ask because the code in a workbook I am creating stops working after a few
code executions and I need to close excel down and reopen the workbook.