VBA Cleaner & Compressor for Excel, PowerPoint & Word

Does your VBA program behave erratically or stops with an incomprehensible error?

VBA is a terrible housekeeper. As you edit your source code over time, the size of the file grows in ways that make no sense and the bloat makes its way into the add-ins you compile. Eventually, it can make your add-ins unstable: weird errors crop up, stuff that makes no sense and that you can't fix in code because it's not the code that causes the problem.

Visual Basic for Applications (VBA) is a programming language built into most Microsoft Office applications. It first appeared in 1993. Although new VBA properties and methods are constantly being added to support innovations in Office, the Visual Basic Editor (VBE) and VBA's inner workings haven't changed much over the last 2 decades in order to ensure consistency and compatibility in the programming model across releases.

Hardware in the mid-90s was pretty slow compared to computers nowadays, so VBA had to rely on an internal performance cache in order to deliver an acceptable user experience.

How things looked when VBA first appeared in Microsoft Office

Unfortunately, VBA code doesn't properly clean-up after itself, so lots of junk gets left behind in performance caches during editing of VBA projects. Not only does this increase the file size, but it may lead to odd behaviour of your program at runtime or slow loading.

If add-ins or workbook macros are run across several versions of Office, stability issues are likely to be experienced with any uncleaned VBA project. The problems are triggered by compiled macro code stored in version dependent performance caches. Some errors that may be resolved by code cleaning:

System Error &H80004005

Could not load some objects because they are not available on this machine

Could not load some objects error in Excel

Security Concerns

If you plan to share a copy of a macro-enabled Office file or template (workbook, document, presentation etc) you should be aware that the username used by the macro author may be stored inside the redundant compiled code caches of the VBA project.

Depending on how private you want your coding work to be, you may want to remove this hidden information by cleaning the VBA project in macro enabled files, as it can reveal sensitive details about your company or yourself to the public.

The Ribbon Commander VBA cleaner removes the redundant caches from closed files only, so VBE doesn't get a chance to replace the information. You need to clean files destined for publication after being saved.

A second potential concern is that redundant macros stored in caches can provide a peak at earlier versions of your code. A forensic investigator can travel back in time and read bits and pieces from code you compiled during VBA code revisions. So, if you really want to delete any sensitive code from your macro-enabled workbooks (e.g. passwords) you should distribute only cleaned VBA files.

The Workaround: Clean & Compress

Clean & Compress
VBA in Excel Files

Clean & Compress VBA
in PowerPoint Files

Clean & Compress
VBA in Word Files

Cleaned VBA Files
Are Smaller In Size

Ribbon Commander offers the CleanVBAProject method, which can remove all code garbage from any macro-enabled or binary workbook and add-ins instantly.

The Ribbon Commander VBA cleaner removes all redundant caches from closed files, so VBE doesn't get a chance to replace the information.

Cleaned files are smaller in size. Nowadays files are moved between desktops and the cloud more frequently than ever before. On an enterprise level, compressed files translate into decreased costs for online storage and bandwidth. Backups and downloads can be executed faster. Email bounce-backs could be stopped and email storage requirements could be reduced.

For years the 'golden' standard for Excel VBA code cleaning has been module export - import.
Ribbon Commander aims to raise the bar by offering several productivity advantages for VBA developers.

Ribbon Commander's VBA Project code cleaner supports both 32 & 64 bit versions of Office/Excel.
The .CleanVBAProject method does NOT open files in Excel. This translates to significant time savings, as massive workbooks may require even minutes to open and save.
Multiple closed files can be cleaned very fast by Ribbon Commander. Check if your files are already clean using the .isVBAProjectClean property.
Traditional cleaning involves exporting all VBA components to text files on disc. This a loophole for the security minded, as these text files can be undeleted easily by file recovery software.
Ribbon Commander's Code Cleaner can read and clean both unlocked & locked VBA projects.
End users can clean 3rd party protected add-ins, when stability issues are experienced during loading or running code. Locked VBA projects are not unprotected under any circumstances.
Ribbon Commander's Code Cleaner can clean closed macro-enabled (.xlsm), binary (.xlsb) workbooks, templates (.xltm) & add-ins (.xlam) or any macro-enabled Office file (PowerPoint presentations & Word documents).
Ribbon Commander's Code Cleaner can remove the full path to MSForms.exd from the VBA performance cache, which contains the developer's username in the format shown below:

C:\Users\james07\AppData\Local\Temp\VBE\MSForms.exd

The Ribbon Commander framework is available for both 32 & 64 bit versions of Microsoft Office

How to Clean Macro-Enabled Files From The Ribbon

Please clean files destined for publication after being saved, signed with a digital certificate and closed.

Clean your VBA projects from Ribbon Commander's Tools menu

How To Clean VBA Projects Programmatically

Add a reference to the Ribbon Commander framework library

Sub CleanVBA()
 
   '----------------------------------------------------------
   ' Reference to IlydaUK Ribbon Commander v1.1 library needed
   '----------------------------------------------------------
 
    Dim oXML As openXmlFile: Set oXML = New openXmlFile
    Dim sFileName As String
     
    On Error GoTo ErrHandler
     
    With Application.FileDialog(msoFileDialogOpen)
        .Show
        .AllowMultiSelect = False
        sFileName = .SelectedItems(1)
    End With
    
    ' Is the VBA project clean?
    If Not oXML.isVBAProjectClean(sFileName) Then
        ' No, clean VBA project
        oXML.cleanVBAProject sFileName, True
        MsgBox "File cleaned!", vbOKOnly, "RC VBA cleaner"
    Else
        MsgBox "File is already clean!", vbOKOnly, "RC VBA cleaner"
    End If
    
   Set oXML = Nothing
Exit Sub
 
ErrHandler:
    Debug.Print Err.Number, Err.Description
End Sub

Download a demo add-in with code shown above here

How To Clean Office Files From Command Line With VBScript

Click button to download the VBScript sample files

File Size Optimization

Reclaim existing storage space on desktop PCs and servers by replacing large macro-enabled Microsoft Office files with smaller versions. Slow storage growth as new files are added.

Cleaned macro Excel files in File Explorer

Frequently Asked Questions

I noticed that my macro Excel files have shrunk in size. Are cleaned files being compressed?

No, file size has been reduced by purging the redundant VBA performance cache form your Microsoft Office files.

Can I clean macro-enabled Office files that have been code-signed with a digital certificate?

Yes! The digital signature will not be invalidated by VBA cleaning, because the discarded performance cache is not signed. Signing a macro signs only VBA code, not the entire Office file. Please clean Office files after code signing.

Why do I get the following error?

This error is shown because an attempt was made to clean VBA in a macro-enabled file that is open in Excel. Please close the workbook (presentation or document) that you want to clean.