I notice in Excel VBA under the Menu "Debug" you see Compile VBAProject.
VBA code is never stored as the plain text that you type in to the
editor. Input is immediately converted to platform- and
version-independent byte codes called OpCodes. These OpCodes are
converted by the editor to the text you see on the screen. When you
compile the project, the compiler translates these OpCodes to
platform- and version-specific codes called ExCodes. When you run the
code, the runtime reads the ExCodes and executes actual machine code
on behalf of the project based on the ExCodes. This whole process is
similar in principle to how Java and the Java Virtual Machine work.
If you were to export all your VBA code to text files and then remove
all the modules and then re-import the code from the text files back
into VBA (which is exactly what Rob Bovey's Code Cleaner does), you'll
see a decrease in file size. This is because the ExCodes were purged
and have not yet been recreated. Then, if you compile the project, the
file size will increase because now it stores the ExCodes in addition
to the OpCodes.
You really never need to compile the code. VBA will automatically do
it when necessary. However, the Compile command also does syntax
checking, which is its only real practical purpose.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com