Compact

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an Excel solution for a client, but with User Forms and
modules the file has gotten rather large. I realize there is no compact and
repair, as in Access, within Excel. Can anyone give me instructions on how
to export modules into a new spreadsheet file, or what is the best way to try
and reduce the file size.

Thanks,
 
Hi Steve,
I have created an Excel solution for a client, but with User Forms and
modules the file has gotten rather large. I realize there is no
compact and repair, as in Access, within Excel. Can anyone give me
instructions on how to export modules into a new spreadsheet file, or
what is the best way to try and reduce the file size.
Click to expand...

You could try running Rob Bovey's VBA Code Cleaner:

http://www.appspro.com/Utilities/Utilities.htm

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Ok, thanks. I have downloaded the CodeCleaner and installed it, but how do I
make it run? How does it clean the code, so to speak?
 
Hi Steve,

Run the downloaded exe file which invokes an automatic installation process.

The CodeCleaner may then be called from the VBA or Excel Tools menus.

If you are using xl2002 or later, note Rob's instructions:

'===============================

Note to Excel 2002 (XP) and Excel 2003 Users
There is a new level of code security in Excel 2002 and higher versions that
you must disable in order for the Code Cleaner to operate. Choose Tools >
Macro > Security from the Excel menu and click the Trusted Sources tab. At
the bottom of this tab you'll see a checkbox labeled Trust Access to Visual
Basic Project. Check this option and click OK to enable the Code Cleaner to
run.
'===============================
 
Steve,

Definitly use the Code Cleaner.
Compile your code.

There are many things that inflate a file size: (hopefully you already know
most of these)
1. How many cells does excel think you are using. Use Ctrl+End to see
where you end up on
each worksheet. You may find some going all the way to column #256, or
to the last row,
when you only intended to use (say) A1:M512 (this is a common killer)
2. Formulas are a load. Watch your file expand rapidly when you add
hundreds or thousands
of formulas to cells. Maybe your code could quickly calculate the
results and put values into
the cells.
3. How many different fonts, colors, borders are you using. The more
variations in formatting adds
bulk to a file.
4. The number of forms and controls on forms could load the program down.
Can you simplify
these or simply create them at runtime.
5. Check the number of fonts, and the number of custom number formats -
these help load the size
of the file.
6. Simplify your code modules. If you are duplicating code in different
modules - break out that
part of the code into a single module and call it from the other
modules.
7. Make sure you have removed as much select or selection from the code as
possible. (This
helps stream line the code so that it works faster and uses fewer lines)
8. Are you using With ... End With statements - these help shorten and
simplify code.
9. [and there are probably many, many more....]

In summary - simplify, simplify, simplify... and than do it again...
 
Back
Top