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)

Answer 3

This was a massive struggle, turned out the implementation of this is challenging in multiple ways. I really appreciate all the assistance that's been provided though, using the suggestion from @DmitrijHolkin, I used the "Microsoft.Office.Interop.Excel" library as the starting point for this.

What I didn't understand before was that you could invoke the "Compile" function from C# and have that run in a separate excel window. Now where that seems simple to accomplish in theory, the implementation of a script/application turned out to be a bit of a challenge. There are all sorts of things you need to worry about.

I've cobbled together a C# console application, along with a few example excel files that I believe is a good starting pointing for testing this. I will eventually adapt it to be run within the MSTest framework and integrate that into my CD pipeline. Of course there some important prerequisites:

1. You need the version of Excel installed, that you wish to test.
2. The ability to tolerate windows popping up/being closed (i.e. needs to be run on a unused user account/machine).

Looking at the code will demonstrate that I still haven't ironed out all the smaller problems yet. I'll eventually get around to doing that, but in the interim, this does work for now:

XXX.XLSM (VBA)

Public Function Compiler()
    On Error GoTo ErrorHandler

    Compiler = "Successfully Compiled"

    Dim compileMe As Object
    Set compileMe = Application.VBE.CommandBars.FindControl(Type:=msoControlButton, ID:=578)

    If compileMe.Enabled Then
        compileMe.Execute
    End If

    Exit Function

ErrorHandler:

    Compiler = "Unable to Compile - " & Err.Description

End Function

YYY.XLSM (VBA)

(Same as XXX but contains a separate method with a bunch of gibberish text designed to cause the compilation of the VBA file to fail)

TestVBACompilation - C#

(Note: You will need to install the "Microsoft.Office.Interop.Excel" library from NuGet)

using Microsoft.Office.Interop.Excel;
using Microsoft.Win32;
using System;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Threading;
using System.Threading.Tasks;

namespace TestVBACompilation
{
    internal class TestVBACompilationMain
    {
        private static void Main(string[] args)
        {
            Console.WriteLine(TestMainFile("Excel 2010 32-bit", @"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE", @"C:\Users\LocalAdmin\Downloads\XXX.xlsm"));
            Console.WriteLine(TestMainFile("Excel 2016 32-bit", @"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE", @"C:\Users\LocalAdmin\Downloads\XXX.xlsm"));

            Console.WriteLine(TestMainFile("Excel 2010 32-bit", @"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE", @"C:\Users\LocalAdmin\Downloads\YYY.xlsm"));
            Console.WriteLine(TestMainFile("Excel 2016 32-bit", @"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE", @"C:\Users\LocalAdmin\Downloads\YYY.xlsm"));

            Console.ReadLine();
        }

        /// <summary>
        /// Call this method with each version of the file and the version of excel you wish to test with
        /// </summary>
        /// <param name="pathToFileToTest"></param>
        /// <param name="pathToTheVersionOfExcel"></param>
        /// <param name="excelVersionFriendlyText"></param>
        /// <returns></returns>
        private static string TestMainFile(string excelVersionFriendlyText,
            string pathToTheVersionOfExcel,
            string pathToFileToTest
            )
        {
            TestVBACompilationMain program = new TestVBACompilationMain();
            string returnText = "";

            program.UpdateRegistryKey();
            program.KillAllExcelFileProcesses();

            //A compromise: https://stackguides.com/questions/25319484/how-do-i-get-a-return-value-from-task-waitall-in-a-console-app
            string compileFileResults = "";
            using (Task results = new Task(() => compileFileResults = program.CompileExcelFile(excelVersionFriendlyText, pathToTheVersionOfExcel, pathToFileToTest)))
            {
                results.Start();
                results.Wait(30000); //May need to be adjusted depending on conditions

                returnText = "Test: " + (results.IsCompleted ? compileFileResults : "FAILED: File not compiled due to timeout error");

                program.KillAllExcelFileProcesses();
                results.Wait();
            }

            return returnText;
        }

        /// <summary>
        /// This should be run in a task with a timeout, can be dangerous as if excel prompts for something this will run forever...
        /// </summary>
        /// <param name="pathToTheVersionOfExcel"></param>
        /// <param name="pathToFileToTest"></param>
        /// <param name="amountOfTimeToWaitForFailure">I've played around with it, depends on what plugins you have installed, for me 10 seconds seems to work good</param>
        /// <returns></returns>
        private string CompileExcelFile(string excelVersionFriendlyText,
            string pathToTheVersionOfExcel,
            string pathToFileToTest,
            int amountOfTimeToWaitForFailure = 10000)
        {
            string returnValue = "";
            _Application oExcelApp = null;
            Workbook mainWorkbook = null;

            try
            {
                //TODO: I still need to figure out how to run specific versions of excel using the "pathToTheVersionOfExcel" variable, right now it just runs the default one installed
                //In the future I will add support to run multiple versions on one machine
                //These are ways that don't seem to work
                //oExcelApp = new Microsoft.Office.Interop.Excel.Application();
                //oExcelApp = (Microsoft.Office.Interop.Excel.Application)Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application.14"));

                Process process = new Process();
                process.StartInfo.FileName = pathToTheVersionOfExcel;
                process.Start();

                Thread.Sleep(amountOfTimeToWaitForFailure);

                oExcelApp = (_Application)Marshal.GetActiveObject("Excel.Application");

                mainWorkbook = oExcelApp.Workbooks.Open(pathToFileToTest);

                Workbook activeWorkbook = oExcelApp.ActiveWorkbook;
                Worksheet activeSheet = (Worksheet)activeWorkbook.ActiveSheet;

                //Remember the following code needs to be present in your VBA file
                //https://stackoverflow.com/a/55613985/2912011
                dynamic results = oExcelApp.Run("Compiler");
                Thread.Sleep(amountOfTimeToWaitForFailure);

                //This could be improved, love to have the VBA method tell me what failed, that's still outstanding: https://stackguides.com/questions/55621735/vba-method-to-detect-compilation-failure
                if (Process.GetProcessesByName("EXCEL")[0].MainWindowTitle.Contains("Microsoft Visual Basic for Applications"))
                {
                    returnValue = "FAILED: \"Microsoft Visual Basic for Applications\" has popped up, this file failed to compile.";
                }
                else
                {
                    returnValue = "PASSED: File Compiled Successfully: " + (string)results;
                }
            }
            catch (Exception e)
            {
                returnValue = "FAILED: Failed to start excel or run the compile method. " + e.Message;
            }
            finally
            {
                try
                {
                    if (mainWorkbook != null)
                    {
                        //This will typically fail if the compiler failed and is prompting the user for something
                        mainWorkbook.Close(false, null, null);
                    }

                    if (oExcelApp != null)
                    {
                        oExcelApp.Quit();
                    }

                    if (oExcelApp != null)
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
                    }
                }
                catch (Exception innerException)
                {
                    returnValue = "FAILED: Failed to close the excel file, typically indicative of a compilation error - " + innerException.Message;
                }
            }

            return excelVersionFriendlyText + " - " + returnValue;
        }

        /// <summary>
        /// This is reponsible for verifying the correct excel options are enabled, see https://stackoverflow.com/a/5301556/2912011
        /// </summary>
        private void UpdateRegistryKey()
        {
            //Office 2010
            //https://stackoverflow.com/a/3267832/2912011  
            RegistryKey myKey2010 = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\14.0\Excel\Security", true);
            if (myKey2010 != null)
            {
                myKey2010.SetValue("AccessVBOM", 1, RegistryValueKind.DWord);
                myKey2010.Close();
            }

            //Office 2013
            RegistryKey myKey2013 = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\15.0\Excel\Security", true);
            if (myKey2013 != null)
            {
                myKey2013.SetValue("AccessVBOM", 1, RegistryValueKind.DWord);
                myKey2013.Close();
            }

            //Office 2016
            RegistryKey myKey2016 = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\16.0\Excel\Security", true);
            if (myKey2016 != null)
            {
                myKey2016.SetValue("AccessVBOM", 1, RegistryValueKind.DWord);
                myKey2016.Close();
            }
        }

        /// <summary>
        /// Big hammer, just kill everything and start the specified version of excel
        /// </summary>
        private void KillAllExcelFileProcesses()
        {
            //TODO: We could tune this to just the application that we opened/want to use
            foreach (Process process in Process.GetProcessesByName("EXCEL"))
            {
                process.Kill();
            }
        }
    }
}