uk
English      
br
Português    
Other:

b VBA CODE DECOMPILER AND COMPACTOR

What's new   Download   Home  Ads

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.

Picture

Following items in this page:

Features

Download

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)

Executing via Command LineNew

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.

Download

Download the English version here   
You also can download from this mirror site   
Last update: 12/23/2023 - 103.3kb    
Freeware based in this License Agreement   

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.

  S1                   S2                             S3                                S4
Text <===> Op-code === > P-code/Ex-code === > Native code
            C1                     C2                                    C3

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:

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

And it gets even more stable, if you use OnTime method, like this:

Private Sub Workbook_Open()
    Application.OnTime Now, "YourMacro"
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.xls
Note: 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.

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:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not ThisWorkbook.Saved Then
        Shell ThisWorkbook.Path & "\..\VBADecompiler.exe cmd/decompile" & _
              " /sXLfile:=" & ThisWorkbook.FullName & _
              " /sApplication:=Excel" & _
              " /sAppVersion:=Default" & _
              " /bOverBackup:=1" & _
              " /bPreservDateTime:=0" & _
              " /bLogActivity:=1" & _
              " /sFilePassword:="""
    End If
End Sub

3 - Create your decompilation manager in bat, VBScript, VBA etc

The VBA code below decompiles all XL files in a specified folder:

Option Explicit				
Sub DecompileAllXLonFld()
    Dim SetCurDir As FileDialog, arrFiles(), i As Long, sRet As String
    Dim sVBADecpPath As String, sVBADecpLog As String, lVBADecpLogSizeIni As Long, lVBADecpLogSizeNew As Long

    sVBADecpPath = ThisWorkbook.Path & "\..\"
    If Dir(sVBADecpPath & "VBADecompiler.exe") = "" Then MsgBox "VBADecompiler.exe not found!", vbCritical: Exit Sub
    sVBADecpLog = sVBADecpPath & "VBADecompiler.log"

    Set SetCurDir = Application.FileDialog(msoFileDialogFolderPicker)
    With SetCurDir
        .Title = "Select the folder where all XL files will be decompiled"
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.Path
        If .Show <> -1 Then Exit Sub Else sRet = .SelectedItems(1)
    End With
    Set SetCurDir = Nothing

    If MsgBox("All XL files (*.xl*) in " & CurDir & " will be decompiled. OK?", vbOKCancel) = vbCancel Then Exit Sub Else sRet = ""

    Do
        If sRet = "" Then sRet = Dir("*.xl*") Else sRet = Dir()
        If sRet <> "" And CurDir & "\" & sRet <> ThisWorkbook.FullName And InStr(1, sRet, "Backup ") = 0 Then
            ReDim Preserve arrFiles(i)
            arrFiles(i) = CurDir & "\" & sRet
            i = i + 1
        End If
    Loop Until sRet = ""

    If i = 0 Then MsgBox "No XL file is found in " & CurDir & "!": Exit Sub
    For i = 0 To UBound(arrFiles())
        If Dir(sVBADecpLog) <> "" Then lVBADecpLogSizeIni = FileLen(sVBADecpLog) Else lVBADecpLogSizeIni = 0

        Shell sVBADecpPath & "VBADecompiler.exe cmd/decompile" & _
              " /sXLfile:=" & arrFiles(i) & _
              " /sApplication:=Excel" & _
              " /sAppVersion:=Default" & _
              " /bOverBackup:=1" & _
              " /bPreservDateTime:=0" & _
              " /bLogActivity:=1" & _
              " /sFilePassword:="""

        Do
            DoEvents: Application.Wait Now + TimeValue("0:00:01")
            If Dir(sVBADecpLog) <> "" Then lVBADecpLogSizeNew = FileLen(sVBADecpLog) Else lVBADecpLogSizeNew = 0
        Loop While lVBADecpLogSizeNew = lVBADecpLogSizeIni
    Next
    If MsgBox(i & " XL file" & IIf(i < 0, " was", "s were") & " decompiled. Open VBADecompiler.log file?", vbOKCancel) = vbOK Then
        Workbooks.OpenText sVBADecpLog, , , xlDelimited, , , True
        Columns("A:O").EntireColumn.AutoFit
        Range("A1").Select
        ActiveWindow.SplitRow = 1
        ActiveWindow.FreezePanes = True
        Range("A1").End(xlDown).Select
    End If
End Sub

More information

Home

 

Facebook



gplus

 

 

Visitas acumuladas em todas as páginas:
Visitas hoje só nesta página: