How to catch error message from a vba macro with ExcelDNA

[NTK]

Hi there,

Right now I'm using C# and Microsoft.Office.Interop.Excel to open a workbook and then call some macros in this wb with:

            try
            {
                excelApplication.Run(macroName);
            }
            catch (Exception)
            {
                message = string.Format("Error executing macro {0}.", macroName);
            }

So what happens if there's a error in that macro!? As my macros are usually in a module the error message is not propagated and a pop up box is displayed.

According to:
http://social.msdn.microsoft.com/forums/vstudio/en-US/9025c565-8702-4e84-af00-8cdfa4f7df57/ignore-error-messages-in-excel
http://www.xtremevbtalk.com/showthread.php?p=1356557#post1356557
this is simply the way Excel works, so I either have to put my code inside a worksheet or follow the last post in the second link (which I don't fully comprehend at the moment).

So, here's my question: I want to leave my macros wherever they are and still catch the error message in my C# program instead of displaying this error message  box. Can ExcelDNA somehow help me on this quest?

Thanks,
Toby

[NTK]

the code in posts from the second link look better here: http://www.xtremevbtalk.com/archive/index.php/t-296563.html

[Patrick O'Beirne]

As you have seen you can't catch the return from the vba function . If you have control over the macro then put in an error handler and save any err value in some object accessible from excel dna like a defined name MyErr.
If it's other people's code that has no error handling then you're out of luck.

[NTK]

Thanks, luckily this is my vba code so I'm using an ErrorHandler form now on. Somehow I was under the impression that even with an error handler there will be a pop up box. Great, I also changed my Sub in Function and whenever I call from C# I expect a return string which is empty whenever there's no error and otherwise gives me some information about the error that I can use in C# to write a nicely error message.

C#

var macroResult =string.Empty;
macroResult = excelApplication.Run(macroName);

VBA

Function GenerateError()
    On Error GoTo ErrorHandler
    Dim i As Integer
    i = 8 / 0#
    GenerateError = ""
    Exit Function
ErrorHandler:
    GenerateError = Err.Number & " - " & Err.Description
End Function

[Patrick O'Beirne]

So I was wrong when I believed, without checking, that other link about not being able to get a return from a VBA function back into ExcelDNA.
Thanks for testing it, Toby!
Just one thing about your VBA error handling:
It's safer to Resume, so the code should look like

ExitFunction:

    Exit Function
ErrorHandler:
    GenerateError = Err.Number & " - " & Err.Description

    Resume ExitFunction
End Function

or for something as simple as a one-liner, you can do this:

Function CatchAnError(something) as String
On Error Resume Next
<One line of code that might cause an error>
if Err <> 0 Then CatchAnError = Err.Number & " - " & Err.Description
End Function

P

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.