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.

Extraction

how did you expose your classes and methods to VBA? something like this? – Berber

vba4all, yes - exactly as described in Your link. (in VB.NET though and not in C#). – Extraction

have you set reference to your addin in vba project? – Oversold

wouldn't just addIn.OpenWorkbook work then? – Berber

See this. – Oversold

@ZAT, for downward compatibility to excel 2003 I use late binding to the Addin. However, I tested with early binding and reference to my addin in the vba project. Same effect. Also Your link brought no improvements -- unfortunately. – Extraction

@vba4all, yes You are right. It works but strangely only as long as there is not #If-statement in the workbook to be opened. – Extraction

just a thought: use boolean data type and use #if ebind then .... – Oversold

Conditional compilation is not conditional compilation in Office 2K+, all branches are compiled. Also in Office 2K+, workbook-level compilation constants are not visible, only VBA declared compilation constants are visible. Are eithor of these two things causing your problem? – Sclerosed

Have a look here: vbaexpress.com/forum/… – Shepperd

And here: msdn.microsoft.com/en-us/library/office/… – Shepperd

And one question: did you register your ComAddin in GAC? – Shepperd

Yes Maciej, I did using gacutil – Extraction

mayby you are using Not statement somewhere with #If, it was the source of errors in some versions – Funest

Try to write in VBA: Call automationObject.openWorkbook (filename) or automationObject.openWorkbook filename . – Tacet

I'll second @ElScripto. I don't know what the problem is, but you're surely using brackets incorrectly there. Your line should be either Call automationObject.openWorkbook(filename) (Call and ()) or automationObject.openWorkbook filename (no Call, no ()), not a mixture of both. It's a long shot, but who knows? – Francisco

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");
}

Ratel