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:
- You need the version of Excel installed, that you wish to test.
- 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://mcmap.net/q/269567/-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://mcmap.net/q/932723/-a-way-to-automate-the-quot-compile-quot-function-of-ms-office-39-s-vba-code
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://mcmap.net/q/1009556/-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://mcmap.net/q/967845/-how-to-check-from-net-code-whether-quot-trust-access-to-the-vba-project-object-model-quot-is-enabled-or-not-for-an-excel-application
/// </summary>
private void UpdateRegistryKey()
{
//Office 2010
//https://mcmap.net/q/273499/-how-to-detect-installed-version-of-ms-office
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();
}
}
}
}
