How big can code be?

  • Thread starter Thread starter Jock
  • Start date Start date
J

Jock

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.
 
It is probably not the number of lines of code in the macro, but the amount
of memory the worksheet is using. When you perform a lot of operations on a
worksheet the memory associated with the worksheet fragments and you start to
use up more meory. Depending on the amount of RAM you have in your computer
will make a difference and the amount of swap space you have on your hard
drive. If you ran the program on a PC with more memory you would find that
you would be able to perform more code executions before the program stopped
running.

You may want to perform a save on the workbook in the middle of your macro
(or between running the macro) which may recover some memory and allow you to
continue running.
 
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)
 
Back
Top