A Way to Automate the "Compile" Function of MS Office's VBA Code

Question

Typically when I make a change to a VBA file I like to compile it to ensure my changes didn't break anything:

But compiling on different machines with different versions of the office will result in different results, sometimes it will compile, sometimes not... Things like this can happen, or maybe this. Turns out in each version of excel all sorts of things can be different (not just references though that is the most common issue).

How would I automate the compiling of my VBA code? I would like to be able to do this in multiple products such as Excel, PowerPoint, and Word, I would like to be able compile as 32 and 64 bit, with 2010, 2013, 2016, etc...

Update 1

Yes this is still a major pain point, right now I have a series of manual testers (people) review all relevant files on various different configurations based on our release schedule, there has got to be a better way to do this.

What I would prefer is some sort of PowerShell script/.Net project(C#, VB.NET) that would accomplish this, even if I had to setup a server with a bunch of versions of office, I think it would be well worth the investment.

I'd imagine, worst case you could install all of these different versions onto various VM's, then use AutoHotKey plus some sort of PowerShell script to compile them. Macro's on top of Macro's fun...

This odyssey just underlines to me how difficult VBA development is. Am I really the first person to have issues between different versions of excel? Is it unreasonable to ask to be able to compile under different versions?

MS may love it, but to me it's almost like this language doesn't really have a long term plan past just supporting legacy code. It just continues to exist without any major official future iterations or considerations as it relates to core development challenges such as this one.

Answer 1

You need to go to Excel -> File -> Options -> Trust Center-> Trust Center settings and check the option Trust access to the VBA project object model (if you don't check it the below code will raise the run-time error 1004 programmatic access to visual basic project is not trusted).

Sub Compiler()
Dim objVBECommandBar As Object
Set objVBECommandBar = Application.VBE.CommandBars
    Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578)
    compileMe.Execute
End Sub

on C something like that, Don't forget to add excel packages to namespace.

void Main()
{
    var oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
    try{
        var WB = oExcelApp.ActiveWorkbook;
        var WS = (Worksheet)WB.ActiveSheet;
        //((string)((Range)WS.Cells[1,1]).Value).Dump("Cell Value"); //cel A1 val
        oExcelApp.Run("Compiler").Dump("macro");
    }
    finally{
        if(oExcelApp != null)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
        oExcelApp = null;
    }
}

Also look here and 1 2 3

Answer 2

I think you can accomplish using some VBA IDE automation. You can do this process with several languages, however, I chose Autohotkey out of familiarity.

I don't think you can use VBA to accomplish this as I don't think you can compile other code while running other VBA code (could totally be wrong here!), so you need another process to get this working. You'll need to trust the VBA Project object model in Excel.

This code works by first creating a new Excel Application object and opening the workbook needed. Next it finds the DebugButton by navigating the CommandBars then calls the Execute method which is what calls the Compile action.

AHK Code

xl := ComObjCreate("Excel.Application")
xl.Visible := True
wb := xl.Workbooks.Open("C:\Users\Ryan\Desktop\OtherWB.xlsb")
DebugButton := wb.VBProject.Collection.VBE.CommandBars("Menu Bar").Controls("&Debug").Controls("Compi&le VBAProject")

if (isObject(DebugButton) && DebugButton.Enabled){
    DebugButton.execute()
}
wb.Close(SaveChanges:=True)