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

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 ObjectSet objVBECommandBar = Application.VBE.CommandBars    Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578)    compileMe.ExecuteEnd 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

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 := Truewb := 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)

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 FunctionErrorHandler:    Compiler = "Unable to Compile - " & Err.DescriptionEnd 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://stackoverflow.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://stackoverflow.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();            }        }    }}