How to compact the VBA-project in an Excel workbook

Question

I'm am searching for a way to compact the VBA project in an Excel workbook. I recently discovered that the VBA project in one of my workbooks had grown over 60 Mb when I opened the workbook with WinZip. The compressed workbook (.xlsm) exceeded the 10Mb limit that my email provider has set for email attachments. I had to take action or find another way to distribute.

I was able to reduce the size from more than 10Mb to less than 2Mb but that was a lot of work and I'm not certain if we can automate this.

This is the procedure I followed:

Export the code behind all sheets, ThisWorkbook, Forms, CodeModules and ClassModules.
Save the workbook as .xlsx (All code is removed)
Reopen the workbook and save as .xlsm
Import all modules again and past the code behind the sheets.

Now that I know that the VBA project grows without an option in Excel to compact it with a single press of a button, at least not an option that I know of. I would like to create some tool to do this every time I am to distribute the file.

Any ideas how the do this?

Look at saving it as a binary file (xlsb) instead of an xlsx — Tom
– Tom, Commented Nov 27, 2015 at 14:26

Andre · Accepted Answer · 2015-11-27 15:19:22Z

1

I have had good experiences with this tool:

VBA Decompiler

answered Nov 27, 2015 at 15:19

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to compact the VBA-project in an Excel workbook

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related