Automation Error with conditional compilation

Question

I find strange behaviour when trying to load an Excel workbook.

I have an Excel-AddIn, written in .NET with COM Interop. It is mainly used to create my own Ribbon-Tab, load workbooks from a menu and do some project administration.

When I try to open a workbook using two ways, I get different results:

First, when I load the Workbook (Excel 2003-Version) from within the Addin everything works fine. From the Button-Event of the ribbon, a public function openWorkbook of the Add-In is called that uses application.workbooks.open(...) to load the Excel workbook.

This way, the workbook opens without an error.

Second, when I try to call the Addin-Function from within VBA using code like:

Set addIn = Application.COMAddIns("WMExcelAddin1")
Set automationObject = addIn.Object
automationObject.openWorkbook (filename)

I get an error message:

Compile Error

Automation Error

and the IDE stops at the first occurrence of a conditional compile in one of the workbook-modules, looking like follows:

#const ebind = 0
[...]
sub proc1()

     #if ebind = 1 then         ' IDE Stops here
          [...]
     #else
          [...]
     #end if

end sub

I tried to use boolean datatype instead of numbers with the same effect.

I'm at my wits' end.

Answer 1

In Automation mode Excel does not load add-ins by default. Excel loads add-ins using the conditions add-ins were compiled with. In order for Add-In works during Automation mode, one should force Excel to load it prior to load any workbooks depending on that add-in. Below I provide the code example from my real project (with some editions) which implements this loading sequence in JScript. Comments explain steps.

function run_excel() {
  dbg_log("run_excel");
  g_xla_addin = null;
  g_xla = null;
  try {
    g_add_ins = get_excel_app().AddIns;
    dbg_log("finding add_in.xlam");

    //Searching for the installed add-in like Application.COMAddIns("WMExcelAddin1")
    g_xla_addin = find_addin(g_add_ins, "add_in.xlam");
  } catch(v_err) {
      throw new error(
        "xla_loading"
      , CR_xla_loading
      , 'Unexpected error occurred while determining if add_in.xlam is installed.'
      , v_err
      );
  }
  if (g_xla_addin == null) throw new error(
      "xla_loading"
    , CR_xla_not_installed
    , "MS Excel addin is not installed."
    );
  try {
    dbg_log("opening add_in.xlam");

    //In the example, the add-in has the name of its workbook
    try { g_xla = g_excel.Workbooks(g_xla_addin.Name); } catch(e) {}
    if (g_xla == null) {
      g_excel.AutomationSecurity = 1; // 1 == msoAutomationSecurityLow

      //Loading the add-in. The add-in also handles `OpenWorkbook` at this time.
      g_xla = g_excel.Workbooks.Open(
        g_xla_addin.FullName  //FileName
      , 2     //UpdateLinks
      , true  //ReadOnly
      , null  //Format
      , null  //Password
      , null  //WriteResPassword
      , true  //IgnoreReadOnlyRecommended: not display the read-only recommended message
      , 2     //Origin: xlWindows
      , null  //Delimiter
      , null  //Editable
      , null  //Notify
      , null  //Converter
      , false //AddToMru: don't add this workbook to the list of recently used files
      , true  //Local: saves files against the language of Microsoft Excel. 
      , 0     //CorruptLoad: xlNormalLoad 
      );
      hide_excel(); //To speed up and not interfere with user actions
    }
  } catch(v_err) {
    throw new error(
      "xla_loading"
    , CR_xla_loading
    , 'Unexpected error occurred while loading add_in.xlam:\n'
    , v_err
    );
  }

  //Now the Add-In is loaded, so the VBA engine knows its API, and the workbook referencing to it are loaded fine.
  try {
    g_sig_cat_wbk = g_excel.Workbooks.Open(
      g_sig_cat_fn  //FileName
    , 2     //UpdateLinks
    , true  //ReadOnly
    , null  //Format
    , null  //Password
    , null  //WriteResPassword
    , true  //IgnoreReadOnlyRecommended: not display the read-only recommended message
    , 2     //Origin: xlWindows
    , null  //Delimiter
    , null  //Editable
    , null  //Notify
    , null  //Converter
    , false //AddToMru: don't add this workbook to the list of recently used files
    , false //Local: saves files against the language of Microsoft Excel. 
    , 0     //CorruptLoad: xlNormalLoad 
    );

//Calling on the loaded workbook the target macro from the loaded add_in.xlam 
    vba_ret(g_excel.Run(g_xla_addin.Name+"!my_addin.prepare_sig_import", g_sig_cat_wbk.Name));
  } catch(v_err) {
      throw new error(
        "sig_cat_loading"
      , CR_sig_cat_loading
      , 'Error occured while loading catalog of signals:\n'
      , v_err
      );
  }
  finally {
    g_sig_cat_wbk.Close(false);
    g_sig_cat_wbk = null;
  }
  dbg_log("run_excel done");
}