Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Karina K patni   on Nov 07 In MS Office Category.

  
Question Answered By: Dixie Jacobs   on Nov 07

I'm finding that the VBA in 2003 spreadsheets just continues to grow.
Occasionally it will shrink a bit, particularly if you use Save As to create a
new version of the workbook.

If you search with Google you'll find sites that talk about it. I don't know
whether Microsoft regard it as a problem, so don't know if they're working on a
fix. However, it's relatively easy to get the space back.

Apparently the problem is the VBA symbol table, and the more changes you make in
the code, the bigger the table gets. You can make it smaller by deleting the
modules. Then when you re-create them, the symbol table will be compact again.

The workbooks I'm currently working on grow from their basic size  of about 4 MB
up to 8 MB or more with no real increase in code, just changes. Exporting,
deleting and re-importing the modules will drop the workbooks back to the 4 MB.
I use a small routine to export all the modules to a directory, then another one
to delete all non-sheet modules from the workbook. Then I simply drag them back
from the directory into the workbook. Only takes a couple of minutes about once
a week.

Share: 

 

This Question has 2 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on XLA addin file size growing Or get search suggestion and latest updates.


Tagged: