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

Question

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.

score 3 · Answer 1 · answered Jun 05 '18 at 08:11

3

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.

answered Jun 05 '18 at 08:11

Pᴇʜ

56,719
10
49
73

Nice. 1. Instead of executing a macro you call a predefined funaction (which does the same as the macro execution would do) 2. But how to retrieve the distinct error message VBA would throw? – patientCoder Jun 05 '18 at 11:20
what do you mean by (1)? A function is a macro or do you mean a procedure (sub)? A sub cannot return anything therefore you will need a function. (2) If you want to return the text message instead of the error number use `Err.Description` as return in your VBA function. – Pᴇʜ Jun 05 '18 at 11:29

score 3 · Answer 2 · answered Jun 05 '18 at 20:21

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:

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

2 Answers2