VBA CODE DECOMPILER AND COMPACTOR

This
utility Decompiles and Compacts Microsoft®
Visual Basic® for Application (VBA) code saved by Microsoft Access®,
Excel®, PowerPoint®, or Word, reducing the file size and the compilation
conflict probability between different Microsoft® Office versions
while also cleaning compilation garbage accumulated during the project
phase that frequently generates errors and slows loading.

Useful
for developers before file delivery and also for end users who experience
problems when loading 3rd party VBA projects, because this utility
can clean these projects without unprotecting the VBA project, from
the closed file on disk and opened in an application environment
free of the influence of application add-ins, COM add-ins, and others
pre-installed projects.

- The
decompiled and compacted file is saved in the format of the application
version default installed on the computer. But, if there is more
than one installed version a specific version can be selected

- Can
optionally preserve the original file date and time

- Creates
a backup of the original file. You can optionally create a single
backup file each time the utility is run or a series of sequentially
numbered backups

- Can
optionally register the activity in a log file

- Does
not require installation

- This
utility consists of a stand-alone executable file that uses installed
Office library. No other libraries or configurations are required

- The
utility doesn’t modify the configuration of any other application.
It doesn’t occupy Access, Excel, PowerPoint or Word memories like
add-ins and COM add-ins do

- Automatically
saves the last setting to be used as default in the next execution.
All options, including folder path, are saved in an INI file next
to VBADecompiler.exe or, when this is not possible, in the Microsoft®
Windows® temp folder.

- The
decompilation and compacting process can be run from the graphical
user interface or silently from the command line. The command line
supports all of the same options of the graphical user interface

- You
can generate complete command lines from the user interface and
send them to the clipboard to be pasted anywhere, such as into an
argument of the VB or VBA Shell function, in a Windows shortcut,
or into the Windows Run dialog box. See examples in
Executing via Command Line item below.

What It Means to Decompile and Compact in
VBA

In other
programming languages, to decompile normally means to revert compiled
code to its source code. In VBA it has a different meaning. To try
to understand, let us see, without many details, how compilation
functions in VBA.

Between
the human-readable text typed into the VBE and the native binary
code (specific to the CPU on which the code is executed) we can
identify at least four code states (S1, S2, S3 and S4) and three
compilations (C1, C2 and C3) as shown below. Except in the first
state, all the code states are binary.

The code
in state S1, text, only exists while it is displayed in the VBE.
Each time the Enter key is pressed after typing a line of code,
the text in it is compiled immediately at the VBE level into state
S2, Op-code. Op-code remains in the memory and is stored permanently
in the file when saving.

The Op-code
in state S2 is compiled to state S3, P-code/Ex-code, at the VBA
level, when the Run command (F5) or the Debug/Compile menu is executed.
P-code/Ex-code also remains in the memory and can be stored in the
file. The compiled P-code is serialized in memory in addition to
the Op-code and this is called Ex-code.

The code
in state S3, P-code/Ex-code, is compiled to state S4, Native code,
at the operating system level when executing the Run command (F5),
but it doesn’t remain in the memory nor saved in the file.

The first
compilation, C1, is bi-directional. The text is immediately converted
to Op-code when being typed as mentioned above and the Op-code is
temporarily converted to text on the fly when it is displayed in
the VBE window.

The second
compilation, C2, from Op-code to P-code/Ex-code is the VBA compilation
of interest here. Unlike other programming languages, where the
source code and the compiled code are saved in distinct files, here
all persisted code is stored in the same file. Moreover, P-code/Ex-code
isn’t static, when new code is typed into the VBE or when objects
are inserted or changed, P-code/Ex-code will be decompiled (ignored)
and it will not be substituted until the project is run (F5) or
compiled (Debug > Compile menu).

When
running a VBA project, if the VBA interpreter encounters compiled
code in a file that does not match the environment in which VBA
is currently running, a different application version for example,
VBA decompiles too to recompile on the fly adapting it to the new
conditions.

When
decompiling, the compiled code is not removed immediately from memory,
nor from the file. Instead, newly compiled code will be substituted
for it as the program is being executed. For example, if there are
50 subroutines in various modules in a project, and only Sub1 and
Sub15 were called, then only these Subs have the respective compiled
codes substituted for new ones in the default Compile on Demand
option.

When
decompiling, all the decompiled code can be removed from the memory
and from the disk file to compact the project. This reduces the
file size and therefore makes the file load faster. Unfortunately,
VBA won’t do this automatically, although it allows this to be done.
This is what this utility does; it decompiles and compacts VBA projects
saved to disk by Access, Excel, PowerPoint or Word.

Among
the Office applications, only Access was provided by Microsoft with
the command line argument ‘/decompile’. This argument is still not
formally documented, but there are many articles on the Web about
its usefulness. See, for example:

Advantage of Saving Decompiled and Compacted
VBA Code

Saving
compiled or saving decompiled and compacted: which is better?

A priori,
the advantage to saving compiled code is that it provides the best
performance when running. Without the need to compile, execution
begins immediately. This was true when computers had clock speeds
of 150 MHz, but today, with 2500 MHz clock speeds common and little
significant increase in the disk reading speed, compiling is much
faster than reading a project with double the file size from the
disk. And because VBA only compiles code as it is called by the
application, performance is increased.

When
a project is intended to run in different application versions,
there aren’t any advantages in compiling code, because it will be
decompiled if the version under which it is run is different from
the version that compiled it. Under these circumstances, compiled
code can only cause errors and slow loading.

A decompiled
and compacted project also results in significantly smaller files,
facilitating delivery. For example, to test this utility, decompile
and compact the file ATPVBAEN.XLA shipped with Office. You will
see that protected and installed or not, its size will be reduced
by about 67%.

Conclusion.
If a project will always be loaded by the same Office version that
saved it, it does not make much difference between the two options.
But if the project will be loaded by different versions of Office
it is highly recommended that it be saved in a decompiled and compacted
state.

Good Practices to Increase Stability
in VBA Codes:

In my
experience, the following four practices will help create more stable
code when running in different application versions (builds, languages,
OSs etc.):

1) Save
projects decompiled and compacted.

2) Avoid
references to VBA projects. Better to load them with the Open method,
use the Run method and maybe unload them immediately with the Close
method.

3) Avoid
the early binding method of referencing an object in Automation
whenever possible. Use it during the development phase but change
to late binding before distributing the application.

I have
been programming in VBA since it was first included in Office. As
time passed and clients began to use different versions of Office
I began to experience problems during the VBA load process. Once
I adopted the three practices above these problems were almost eliminated.

4) Avoid
lots of code directly into the open event, it is best to transfer
all the necessary code to a Sub in standard module and then call
it from there. In Excel, for example, would look like this:

Private Sub Workbook_Open()
Call YourMacro ‘Where YourMacro is a Sub in a standard module.
End Sub

I have
experienced instability in code into open event especially in Excel
2003 and Word 2003 and newer versions.

New Office 2007 and later (32 and 64-bit)

A priori,
all above considerations continue valid in VBA projects saved in
new zipped file formats of Office 2007 and later (32 and 64-bit).
Only the reduction tax in file final size on the disk can appear
smaller due to zip compression, but internally the tax reduction
continues similar to previous Office versions file formats, meaning
now, beyond less data to load, less data to uncompress.

Executing via Command Line

The decompilation
and compacting process can be run from the graphical user interface
or silently from command line. The command line supports all of
the same options of the graphical user interface. You can generate
complete command line from the user interface and send them to the
clipboard to be pasted anywhere.

The decompilation
via command line is useful for you automate your decompilations,
for example, you can:

1 - Create
a shortcut on Windows to decompile a project with just two clicks

Assuming
you want to create a shortcut on Windows desktop (can be in any
folder) to decompile your VBA project in YourApp.xls, then:
- Run
the VBADecompiler.exe;
- In the interface, select the options you want and click on the
WinCmdLine link in upper right corner
- On Open File dialog box go to the folder where YourApp.xls file
is and select it
- On the alert, press OK to send the mounted and ready command line
to Windows Clipboard
- Click with right mouse button on the Windows desktop > New > Shortcut
- In “Type the location of the item:” field, paste the Clipboard
contents and press “Next” button.
- In the following form, enter a name for your shortcut and click
on the "Finish" button. Ready, now just double-click on this shortcut
to decompile your VBA project in YourApp.xlsNote: If you have received an alert of long command line, more than
255 characters, before pasting in the first form, paste it in Notepad
and remove the path of YourApp.xls. Once the link is ready, click
on the Properties command from the right-click menu and enter removed
path in the 'Start in:" field.

Before
closing, your project can call the VBADecompiler.exe that waits
the project file is released so that starts the decompilation. You
can set conditions for the call. In the code below, the VBA project
is decompiled when closing long as it has something for saving: