CODEWITHSUNDEEP
c#vbaexcel
patientCoder
patientCoder

Reputation: 71

Run Excel Macro from C#: Catch Runtime error from VBA

I found solutions on StackOverflow to execute Excel macros from C# like here which works.

Is there a way to forward any exception from VBA to C#? It seems to me, that the calling C# code simply stops if the VBA code runs into a runtime error and comes to a halt.

Upvotes: 3

Views: 1736

Answers (2)

rene
rene

Reputation: 42483

If you have control over the Excel sheet and can place the public sub methods in the ThisWorkbook of the Excel as opposed to under Modules you can use this helper method to invoke the methods and have a .Net exception thrown:

static object CallSub(Workbook workbook, string name)
{
    var bindingFlags = BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.InvokeMethod | BindingFlags.OptionalParamBinding;

    try
    {
        return workbook.GetType().InvokeMember(
            name, 
            bindingFlags, 
            null, 
            workbook, 
            new object[] { }, // arguments
            null, 
            CultureInfo.InvariantCulture, 
            null);
    }
    catch (TargetInvocationException tie)
    {
        throw tie.InnerException;
    }
}

I found the InvokeMember idea via How can I capture a Microsoft Access VBA debug error from my C# code? because I checked on Interaction.CallByName. While you can add a reference to Microsoft.VisualBasic to get that helper class, I opted to strip it down to its bare minimum that just works for a simple method.

This is how you would use it:

var app = new Microsoft.Office.Interop.Excel.Application();
var workbook = app.Workbooks.Open(@"test.xlsm");

try
{
    CallSub(workbook, "DivByZero");
}
catch(DivideByZeroException dbz)
{
    Console.WriteLine(dbz.Message);
}

Or if you like dynamic you can do:

var app = new Microsoft.Office.Interop.Excel.Application();
var workbook = app.Workbooks.Open(@"test.xlsm");

dynamic wb = workbook; // cast workbook to dynamic
wb.DivByZero();  // the runtime figures it out for you

Remember that this only works for subs that are in ThisWorkbook. See this image to get a visual on the differences:

enter image description here

Upvotes: 3

Pᴇʜ
Pᴇʜ

Reputation: 57733

You can catch the exception in VBA (error handling) and return eg. the error number or something else back to C#.

Public Function MyVbaFunction (myParameterA)
    On Error Goto ERR_RETURN

    'your code that throws error here    

    Exit Function
ERR_RETURN:
    MyVbaFunction = Err.Number 'return error number
End Function

And then use that return in C#

var excel = new Application {Visible = true};
excel.Workbooks.Open(filename);
var returned = excel.Run("MyVbaFunction", myParameterA);

and returned now contains the error number or what ever VBA returned.

Upvotes: 3

Related Questions