MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

XLA addin file size growing

  Asked By: Karina    Date: Nov 07    Category: MS Office    Views: 1574

I have an XLA addin created in-house that I am
responsible for updating. I've noticed that the XLA file has grown
much more than the VBA code that I have written, and I don't have a
clue why.

I have been exporting all the code so that I can manage it with
SourceSafe, and noticed that all the raw files, including the BAS,
CLS, FRM and FRX files, all total 263k. The XLA file had grown to
over 800k. I decided to start with a blank XLS file, import all the
code, then save it as an XLA file. The result was a file that was
420k. Everything works as before.

This project uses a third party OCX file, with multiple control
instances on one of the forms, if that makes any difference.

Oh, I did run an antivirus checker. Windows XP Pro, Office 2003 SP1.

Does anyone have an idea what is going on?



3 Answers Found

Answer #1    Answered By: Dixie Jacobs     Answered 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.

Answer #2    Answered By: Saiqa Mian     Answered On: Nov 07

Take a look at the website of Rob Bovey.
There you will find among some other addins an addin  to clean
your VBA code.

Answer #3    Answered By: Fareess Khan     Answered On: Nov 07

Yes I looked at that site. I wasn't interested in any of the code  cleaning
being offered (e.g. removal of comments), so didn't use it. Just spent 10
minutes writing a couple of subroutines to do the job.

However, it's certainly worth looking at.

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