Compiling Excel VBA code increases workbook file size!

[Guest]

hello,

I develop a big Excel workbook having more than 30000 code lines.
When I compile all the code from the VBE and save the worbook, the worbook
file size increases dramaticaly (from 3500Ko to 7000Ko). It seems that Excel
saves compiled objects.

Is it possible to avoid Excel keeping compiled objects in the saved file?

Thanks for your help!

 

[deko]

I develop a big Excel workbook having more than 30000 code lines.

When I compile all the code from the VBE and save the worbook, the worbook
file size increases dramaticaly (from 3500Ko to 7000Ko). It seems that Excel
saves compiled objects.

Is it possible to avoid Excel keeping compiled objects in the saved file?

Can you do a full export/import with Excel? In Access this is a fairly
well-known issue. To fix it in Access, I create a new database, then go to
File => Get External Data and import everything from the original database.
The size of the mdb reduces by more than half, depending on how much coding
I've done.

 

[Tom Ogilvy]

To deko:
Why don't you use the compress database option in Access? For Excel, there
isn't one, so the process you describe is used for excel.

To OP:
For a routine that does it for you look at

http://www.appspro.com
Rob Bovey's site. Look in free downloads for code cleaner.

 

[deko]

Why don't you use the compress database option in Access?

I've seen mdbs reduce in size by 60 or 70 percent doing an full
export/import - the compact and repair (or compact on close) is good
practice and I use it, but it does not do what a full export/import does. I
think it has something to do with the way Access compiles the code or
maintains a shadow copy or something.

 

[Tom Ogilvy]

You would know more than I - thanks.

 

[Guest]

I have developped a full export/import from Excel and it works. But this
action must be runned under excel XP. Unfortunately my workbook must be
compatbile with Excel 97. If I open the rebuild workbook directly with Excel
97, I cannot unprotect worksheet since Excel 97 doesn"t know Excel XP
encryption algorithm. So I have found a way to redefine the encryption
algorithm used in my workbook. This method applies well to sheets protection
but not to VBA code protection. Althoug I have define an encryption algorithm
use by Excel 97 (XOR), I cannot deprotect VB code. Moreoever, when I build
the new worbook and define the references, I meet problem with the DLL's
version used.

So I have to use the following process tu rebuild my workbook :
- rebuild workbook under Excel XP
- open workbook under Excel 97. Then correct references and define VBA
protect password.
- open my workbook under Excel XP to protect sheets.

It works, but i's tiresome and semi -manual

So If I fnid a way to clean compiled objects, it will be heaven!

thanks for your answers!

 

[Fredrik Wahlgren]

I have developped a full export/import from Excel and it works. But this
action must be runned under excel XP. Unfortunately my workbook must be
compatbile with Excel 97. If I open the rebuild workbook directly with Excel
97, I cannot unprotect worksheet since Excel 97 doesn"t know Excel XP
encryption algorithm. So I have found a way to redefine the encryption
algorithm used in my workbook. This method applies well to sheets protection
but not to VBA code protection. Althoug I have define an encryption algorithm
use by Excel 97 (XOR), I cannot deprotect VB code. Moreoever, when I build
the new worbook and define the references, I meet problem with the DLL's
version used.

So I have to use the following process tu rebuild my workbook :
- rebuild workbook under Excel XP
- open workbook under Excel 97. Then correct references and define VBA
protect password.
- open my workbook under Excel XP to protect sheets.

It works, but i's tiresome and semi -manual

So If I fnid a way to clean compiled objects, it will be heaven!

thanks for your answers!

XOR encryption is really weak. It is so weak that I wonder why you bother at
all.

/Fredrik

 

[Guest]

XOR encryption is really weak. It is so weak that I wonder why you bother at
all.

/Fredrik

I know but it seems that it's the only algorithm that Excel 97 supports.