none
Ignore error messages in excel

    Question

  • Hi,

    I'm sorry if this is the wrong forum, but i couldn't find the Interop forums. This actually has nothing to do with VSTO.

    I use the Interop's to open an excel workbook, and run a VBA macro. Is there any way to ignore error msgs (or maybe even catch them) that the VBA might throw?

    Here's my code for running the VBA:

     

      private void RunExcelMacro(string FileName, string MacroName)
      {
       // Object for missing (or optional) arguments.
       object oMissing = System.Reflection.Missing.Value;
    
       MSEXCEL.ApplicationClass oExcel = new MSEXCEL.ApplicationClass();
       oExcel.Visible = false;
       MSEXCEL.Workbooks oBooks = oExcel.Workbooks;
       MSEXCEL._Workbook oBook = null;
    
       try
       {
        oBook = oBooks.Open(FileName, oMissing, oMissing,
         oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
         oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
    
        // Run the macros.
        RunMacro(oExcel, new Object[] { MacroName });
        oBook.Save();
       }
       catch (Exception ex)
       {
        throw ex;
       }
       finally
       {
        // Quit Excel and clean up.
        oBook.Close(false, 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;
       }
      }
    

     

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


    Nicolai Søndergaard LM Wind Power A/S
    • Edited by Nicoolai Tuesday, May 11, 2010 6:43 AM .
    • Changed type Nicoolai Wednesday, May 12, 2010 7:47 AM must have set it wrong..
    Tuesday, May 11, 2010 6:41 AM

Answers

  • Hi Nicoolai,

    This is a complex topic, but there is a work around. The problem is that calling 'Excel.Application.Run', which you are doing via late binding, results in an error message dialog box being displayed to the user, instead of the exception being propagated back to the .NET caller.

    The way to avoid this problem is, instead of having the VBA macro reside in a standard module and then calling it via 'Excel.Application.Run', put the VBA macro in in a class module, such as the ThisWorkbook class for the entire workbook, or in any of the Worksheet class modules, and then call the macro via late binding.

    When executed in this manner, your calls will trap any exception thrown. If you do not have access to the code, and therefore cannot change the location of the VBA macros, then you can create a new workbook that acts as an intermediary that can do this for you.


    Note that if you are using C# 4.0 you might be able to utilize the 'dynamic' type to enable late-bound VBA calls to be handled for you automatically. I have not tested using 'dynamic' to call VBA methods, but I would hope that it would work. If 'dynamic' does not work for you, then I know that making use of the 'Microsoft.VisualBasic.Interaction.CallByName' method, as explained in the article, definitely does work.

    -- Mike

    Mike Rosenblum, Excel MVP
    • Marked as answer by Nicoolai Wednesday, May 12, 2010 7:47 AM
    Tuesday, May 11, 2010 9:26 PM

All replies

  • Hi Nicoolai

    You'll find some links in the forum's Please Read First message where you can ask non-VSTO, Office-related questions. But in this case...

    I think oExcel.DisplayAlerts([arguments come here]) is what you're looking for. Excel's alert suppression is pretty good, much better than Word's anyway. You do have to remember to reset it at the end of your code (in a Finally block, to be SURE it gets reset).

    Something else you might want to look at is the oExcel.AutomationSecurity property. This will let you completely suppress execution of any "Auto macros" the workbook contains.

    I'm pretty sure there's no way for you to trap the errors that originate in VBA.


    Cindy Meister, VSTO/Word MVP
    Tuesday, May 11, 2010 7:00 AM
  • Thanks Cindy.

    I will try to add those to my project. I am glad you say it works better in excel than in word. I do not have good experience with word :)


    Nicolai Søndergaard LM Wind Power A/S
    Tuesday, May 11, 2010 7:05 AM
  • It didn't really suppress the VBA error msg. I suppose it isn't possible to do. The debug/end box pops up, and the automation stops.
    Nicolai Søndergaard LM Wind Power A/S
    Tuesday, May 11, 2010 8:24 AM
  • Hi Nicolai

    Well, if it's DEBUG message, then there's a problem in the macro execution, itself. I don't really understand why your application would want to execute macros that may fail?


    Cindy Meister, VSTO/Word MVP
    Tuesday, May 11, 2010 9:10 AM
  • Well, i don't care what it does to the macros that fail, i just don't want it to hang with the end/debug messagebox. If is fine it just ends it.

    My application listens for jobs. When there is a job, it opens the excel file in the job, and executes the macro in the job. The macro's are beyond my control, so they may fail, depending on how well they are made.

    I just don't want my program to stop running, whenever it encounters a badly made macro. It is bound to happen every now and then, so it would be nice if it could resume, instead of hangs.


    Nicolai Søndergaard LM Wind Power A/S
    Tuesday, May 11, 2010 9:18 AM
  • Hi Nicolai

    I can't think of any way to do that, short of building a "timer" into it that would use SendKeys to close the dialog box (you'd have to work out what key to send, probably Esc though).

    If the people who write the macros would put some basic VB error handling into them, they could fail "gracefully". Might be worth a little clinic or something to teach them how to put an On Error GoTo ErrorHandler at the beginning of their code, an Exit Sub line at the end, just before the ErrorHandler: tag. And maybe a Debug.Print err.Number, err.Description after the tag (or better, write it to a log file, which your app could pick up). Because that's the ONLY way you're going to be able to suppress it.


    Cindy Meister, VSTO/Word MVP
    Tuesday, May 11, 2010 3:09 PM
  • Hi Nicoolai,

    This is a complex topic, but there is a work around. The problem is that calling 'Excel.Application.Run', which you are doing via late binding, results in an error message dialog box being displayed to the user, instead of the exception being propagated back to the .NET caller.

    The way to avoid this problem is, instead of having the VBA macro reside in a standard module and then calling it via 'Excel.Application.Run', put the VBA macro in in a class module, such as the ThisWorkbook class for the entire workbook, or in any of the Worksheet class modules, and then call the macro via late binding.

    When executed in this manner, your calls will trap any exception thrown. If you do not have access to the code, and therefore cannot change the location of the VBA macros, then you can create a new workbook that acts as an intermediary that can do this for you.


    Note that if you are using C# 4.0 you might be able to utilize the 'dynamic' type to enable late-bound VBA calls to be handled for you automatically. I have not tested using 'dynamic' to call VBA methods, but I would hope that it would work. If 'dynamic' does not work for you, then I know that making use of the 'Microsoft.VisualBasic.Interaction.CallByName' method, as explained in the article, definitely does work.

    -- Mike

    Mike Rosenblum, Excel MVP
    • Marked as answer by Nicoolai Wednesday, May 12, 2010 7:47 AM
    Tuesday, May 11, 2010 9:26 PM
  • Thanks, the Application.Run worked out great. Amazing i didn't find that solution, before finding a solution with reflection. :)


    Nicolai Søndergaard LM Wind Power A/S
    Wednesday, May 12, 2010 7:46 AM
  • Cool, glad it worked for you. :-)

    But just to be clear, you mean that avoiding the use  of 'Application.Run' worked for you, right?


    Mike Rosenblum, Excel MVP
    Wednesday, May 12, 2010 11:13 AM
  • Cool, glad it worked for you. :-)

    But just to be clear, you mean that avoiding the use  of 'Application.Run' worked for you, right?


    Mike Rosenblum, Excel MVP

    Nope, i actually changed my code to use oExcel.Run() instead of using my RunMacro() method.

    It seems throws an exception in my application, if the macro fails, which is perfectly fine, but i have not tested it too much yet.


    Nicolai Søndergaard LM Wind Power A/S
    Wednesday, May 12, 2010 11:33 AM