 English |
 Português |
English |
Português |
|
|||
|
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. It is a VBA code cleaner in any Access, Excel, PowerPoint, or Word file type. 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 and without invalidating code signing 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.
Following items in this page: What it means to decompile and compact in VBA Advantage of saving decompiled and compacted VBA code Good practices to increase stability in VBA codes New Office 2007 and later (32 and 64-bit)
Features:
- Decompiles and compacts VBA projects saved in any Access, Excel, PowerPoint, and Word file type, including add-ins - Decompiles and compacts password protected VBA projects without unprotecting them and without invalidating code signing - Decompiles and compacts (cleans) VBA code in Standard modules and in any Class module, including modules behind Form, Worksheet, Workbook, Slide, and Document objects - 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. Download:Tested in installed library of Office for Windows, versions 2002(XP), 2003, 2007, 2010 (32 and 64-bit), 2013 (32 and 64-bit), 2016 (32 and 64-bit), and 2019 (32 and 64-bit) in MSI-based and Click-To-Run installations of Office 365™ cloud-based services.
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. It is actually a VBA code cleaner. 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: http://support.Microsoft.com/kb/819780/en-US http://support.Microsoft.com/kb/814858/en-US http://support.Microsoft.com/kb/279334/en-US http://www.Microsoft.com/technet/archive/office/office97,,, http://www.granite.ab.ca/access/decompile.htm
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: And it gets even more stable, if you use OnTime method, like this: 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 LineThe 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: 2 - Make your VBA project auto decompilable through a BeforeClose event 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: 3 - Create your decompilation manager in bat, VBScript, VBA etc The VBA code below decompiles all XL files in a specified folder:
|
|||
|
|
|||
VBA CODE DECOMPILER AND COMPACTOR
