The dreaded VBA Ignore error in Excel Automation from .NET, and how to deal with it easily….0x800AC472

I have been dealing with automating Excel for quite some time, and it is not always very friendly. It gets much much worse when automating from .NET due to the COM interactions.  The project I was working on kept getting “random” errors which after debugging where VBA Ignore errors from COM. They simply meant that Excel was busy right this second, come back later.

Well, in code, they just blew up since who would expect it to tell you that. Normal people would think the call could just wait until it wasn’t busy and continue.

I wrote a class that will help you out a bit in automating Excel. Just throw this utility into one of your base assemblies.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

namespace Seekford.CommonLibrary
{
    /// <summary>
    /// The delegate to use for the HandleExcelStatement inline delegates
    /// Just cast against an inline delegate.
    /// </summary>
    public delegate void ExcelAutomationStatement();

    /// <summary>
    /// Excel Helper Class for the VBA IGNORE HRESULT Error
    ///
    /// This class is designed to help minimize the coding required to handle the VBA IGNORE error that Excel can generate when it
    /// is in an unknown state. Excel does not use the proper COM notification mechanism to let the COM consumer know that Excel is in
    /// a state that the COM call will is not feasible. This state can be anything from a spell check popup to a cell being edited.
    /// It is very difficult to know when this error will occur and even occurs with no user interaction. (Possibly slow or asynchronous state changes?)
    /// Instead of writing a try loop with retry logic for every line of code that calls Excel automation, you can use this class
    /// to wrap the related lines call the HandleExcelStatement function.
    /// Brian Seekford
    /// </summary>
    /// <example>
    ///
    ///    ExcelAutomationHelperUtility.HandleExcelStatement((ExcelAutomationHelperUtility.ExcelAutomationStatement )delegate()
    ///                {
    ///                    if (askAboutMacros)
    ///                        excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityByUI;
    ///                    else
    ///                        excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityLow;
    ///                });
    ///
    /// </example>
    public sealed class ExcelAutomationHelperUtility
    {

        private static int defaultExcelAutomationVBAIgnoreMaximumRetries = 3;
        private static int defaultExcelAutomationSleepTime = 1000;
        private static bool defaultAllowUserDialog = true;
        private const string VBA_IGNORE_HRESULT = "0x800AC472"; //error code for the unknown state HRESULT. COM VBA_IGNORE flag

        /// <summary>
        /// By default, the number of times to retry before user prompt or rethrowing the error
        /// </summary>
        public static int DefaultExcelAutomationVbaIgnoreMaximumRetries
        {
            get { return defaultExcelAutomationVBAIgnoreMaximumRetries; }
            set { defaultExcelAutomationVBAIgnoreMaximumRetries = value; }
        }

        /// <summary>
        /// The number of milliseconds to wait between each retry
        /// </summary>
        public static int DefaultExcelAutomationSleepTime
        {
            get { return defaultExcelAutomationSleepTime; }
            set { defaultExcelAutomationSleepTime = value; }
        }

        /// <summary>
        /// The default value for showing the User the Retry Cancel dialog.
        /// </summary>
        public static bool DefaultAllowUserDialog
        {
            get { return defaultAllowUserDialog; }
            set { defaultAllowUserDialog = value; }
        }

        /// <summary>
        /// Processes a statement block with appropriate handling for the VBA ignore error. Uses default sleeptime, max retries, and default UI value
        /// </summary>
        /// <param name="excelStatement">Code block to execute (use an inline delegate)</param>
        public static void HandleExcelStatement(ExcelAutomationStatement excelStatement)
        {
            HandleExcelStatement(excelStatement, defaultExcelAutomationVBAIgnoreMaximumRetries, defaultExcelAutomationSleepTime, defaultAllowUserDialog);
        }
        /// <summary>
        /// Processes a statement block with appropriate handling for the VBA ignore error. Uses default sleeptime and max retries
        /// </summary>
        /// <param name="excelStatement">Code block to execute (use an inline delegate)</param>
        /// <param name="AllowUserDialog">Should the user be allowed to retry the operation?</param>
        public static void HandleExcelStatement(ExcelAutomationStatement excelStatement, bool allowUserDialog)
        {
            HandleExcelStatement(excelStatement, defaultExcelAutomationVBAIgnoreMaximumRetries, defaultExcelAutomationSleepTime, allowUserDialog);
        }

        /// <summary>
        /// Processes a statement block by executing the passed delegate.
        /// </summary>
        /// <param name="excelStatement">Code block to execute (Use a case inline delegate)</param>
        /// <param name="MaximumRetries">Number of time to rety the code statement</param>
        /// <param name="SleepTime">Total milliseconds to wait between retries</param>
        /// <param name="AllowUserDialog">Allow the user to retry the operation? Possibly letting them close and screens that may have popped up.</param>
        public static void HandleExcelStatement(ExcelAutomationStatement excelStatement, int maximumRetries, int sleepTime, bool allowUserDialog)
        {
            int retryCounter = 0; //number of time to try
            while (retryCounter < maximumRetries) //can we try again? (Could have just put a 1=1 here, but bad form).
            {
                try
                {
                    if (excelStatement != null) //if able, lets execute the code block
                        excelStatement();
                    return;
                }
                catch (Exception ex) //handle all exceptions
                {
                    retryCounter++;
                    if ((!ex.Message.Contains(VBA_IGNORE_HRESULT))) //if not our error, no retries. Just fail.
                        throw;

                    if ((retryCounter >= maximumRetries)) //if past retries
                    {
                        if (allowUserDialog) //can we ask the user to retry?
                        {
                            if (MessageBox.Show(ExcelAutomationHelperResources.ExcelUserWaitPrompt, ExcelAutomationHelperResources.ExcelUserWaitPromptCaption, MessageBoxButtons.RetryCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1, 0) == DialogResult.Retry)
                            {
                                retryCounter = 0;
                                continue;                                       // reloop here so that we don't need to wait another second
                            }
                            else
                                throw;
                        }
                        else
                            throw;
                    }
                    System.Threading.Thread.Sleep(sleepTime); //wait and try again.
                }
            }
        }

        ExcelAutomationHelperUtility()
        { }
    }
}

In order to use this class in your code, just follow the example. Basically, wrap your code in the delegate handler.

Example:

     ExcelAutomationHelperUtility.HandleExcelStatement((ExcelAutomationHelperUtility.ExcelAutomationStatement )delegate()
                    {
                        if (askAboutMacros)
                            excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityByUI;
                        else
                            excelApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityLow;
                    });

0 Replies to “The dreaded VBA Ignore error in Excel Automation from .NET, and how to deal with it easily….0x800AC472”

  1. A concrete example would help. I’ve been fumbling around with trying to make this work. Please provide a call that compiles.

    How do I call something like this below. I get the error message at app.Workbooks.Add(Type.Missing);

    Excel.Application app = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject(“Excel.Application”);

    Excel.Workbook wb = app.Workbooks.Add(Type.Missing);

    1. The example is at the bottom.
      Modified for your case:

      Excel.Application app = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject(“Excel.Application”);

      Excel.Workbook wb;

      ExcelAutomationHelperUtility.HandleExcelStatement((ExcelAutomationHelperUtility.ExcelAutomationStatement )delegate()
      {
      wb = app.Workbooks.Add(Type.Missing);
      });

Leave a Reply

Your email address will not be published. Required fields are marked *