Errors when calling certain Excel VBA macros from C#

Question

I have been struggling and searching on this problem to no avail... I have a few test macros that work perfectly when run from Excel, but either fail or don't work when called from C#, using the interop...

I am using MS Visual Studio 2012 (on 64 bit Windows 7) to create a console application that will call the VBA macro TestMacro() written in Excel 2007.

Here is the C# code to call the macro: (I basically copied what was done here Run Office Macros by Using Automation from Visual C# .NET)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

namespace C#_Macro_Wrapper
{
    class Program
    {
        static void Main(string[] args)
        {
            RunVBATest();
        }


        public static void RunVBATest()
        {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            object oMissing = System.Reflection.Missing.Value;
            Excel.Application oExcel = new Excel.Application();
            oExcel.Visible = true;
            Excel.Workbooks oBooks = oExcel.Workbooks;
            Excel._Workbook oBook = null;
            oBook = oBooks.Open("C:\\Users\\ssampath\\Documents\\RMS Projects\\Beta 3 Testing\\TestMacroForVSTO.xlsm", oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            // Run the macro.
            RunMacro(oExcel, new Object[] { "TestMacro()" });


            // Quit Excel and clean up.
            oBook.Close(true, oMissing, oMissing);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
            oBook = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
            oBooks = null;
            oExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
            oExcel = null;
            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
        }


        private static void RunMacro(object oApp, object[] oRunArgs)
        {
            oApp.GetType().InvokeMember("Run",
                System.Reflection.BindingFlags.Default |
                System.Reflection.BindingFlags.InvokeMethod,
                null, oApp, oRunArgs);
        }
    }
}

The Macros are...

Sub TestMacro()  
Worksheets("Sheet1").ClearContents
End Sub

This macro fails with ComException to Visual Studio -> "Exception from HRESULT: 0x800A03EC"

Sub TestMacro()
Range("TestRange").ClearContents
End Sub

Does not return exception, but does not clear contents of TestRange

Sub TestMacro()
x = Range("TestRange").Count()
Cells(5, 5).Value = x
End Sub

But this works, thus the range name can be recognized, and the count operation can be done...

Sub TestMacro()
Worksheets("Sheet1").Select
x = Range("TestRange").Count()
Worksheets("Sheet2").Select
Cells(5, 5).Value = x
End Sub

This works like last case, but fails to switch to sheet 2, and instead writes result to sheet 1...

As said earlier all these macros work perfectly when run from excel, but fail when called from C#. None of the works sheets are protected, and I have set "enable all macros", "trust access to VBA project model" in Macro Security. I feel there is some access/permissions issue, but I have no idea how to fix it....any help would appreciated..

Thanks in advance!!

Answer 1

Well apparently there was a very easy solution. Instead of using RunMacro function...using the application method "Run" works for all macros. I used the line

oExcel.Run("TestMacro"); 

instead of calling

private static void RunMacro(object oApp, object[] oRunArgs)
{
    oApp.GetType().InvokeMember("Run",
        System.Reflection.BindingFlags.Default |
        System.Reflection.BindingFlags.InvokeMethod,
        null, oApp, oRunArgs);
}

with

RunMacro(oExcel, new Object[] { "TestMsg" });

Arghh..this issue stole 2 days of my life!