Behaviour at opening or referencing in Excel for an XLL having a DLL as dependency

Question

Take an XLL. You can

• open it from excel by doing "File" then "Open" etc or by dragging and dropping it in a blank (or not) Excel spreadsheet (it is the same)
• reference it from Excel using Excel's Add-ins manager by (in recent Excel versions) doing "File", "Options", "Add-ins" and in "Manage" choose "Excel Add-ins", then "Go", then browse to point to the XLL then "OK" so that it appears and is ticked in the Add-ins list, then "OK" to reference it. (That's the "OK" I will I refer to in what follows.)

Now I have an XLL1 that I compile with only one visual studio project. Opening it or referencing it doesn't make any difference and I have no problem. Great.

The code of XLL1 is basically lots of XLL functions that process inputs in the Excel C Api format to convert them in some internal (and Excel C Api independant) format and then feed these processed inputs to underlying C++ functions that performe intricated numerical calculations etc. I decided to separe the Excel C API stuff (the interface) from this underlying Excel C API independant numerical stuff by plugging the latter in a C++ DLL (that I will simply call "DLL" here) that the XLL's project now links to. I call the resulting XLL "XLL2".

Now if I open (in the sense defined above) XLL2 I get the following error :

the file format and extension of 'XLL2.xll' don't match. The file could be corrupted of unsafe. Blah Blah

that you usually get when you have an architecture mismatch between your Excel and your XLL, which is not my case here as everything (my Excel and my projects platforms) is 64 bits -- I confirmed it with Dependency Walker. (Plus, I don't see why architecture would matter suddenly for XLL2's "execution" when it doesn't for XLL1's compilation.)

If I try to reference (in the sense defined above) XLL2 in Excel, at the final "OK" I get the following error :

Microsoft Excel cannot access the XLL2.xll". There are several possible reasons : the file name or path does not exist ; the file is being used by another program ; the workbook you are trying to save has the same name as a currently open workbook.

Of course, none of this three reasons is verified in my case.

I naturally suspected that at the opening or referencing of XLL2 Excel cannot get the information about XLL2 depending on DLL, hence yells the only error message it is allowed to yell at that point.

Indeed, when I put XLL2 and DLL next to EXCEL.exe in the same folder (vomiting smiley) I had no issue with open or referencing it. But sadly, I don't intend to ship my code inside EXCEL.exe's folder so that option isn't a real one for me. Naturally, I tried to add "$(UniversalCRT_LibraryPath_x86)" or "$(UniversalCRT_LibraryPath_x64)" to the additional libraries directories in XLL2's properties in "linker --> general" (where I naturally already add the path to the DLL XLL2 links to). It just allowed me to open1 XLL2 at debug in visual studio. In the normal utilisation, outside of visual studio, the problem remained, while both in debug or normal execution, referencing XLL2 wasn't working.

Then I told myself that the only solution is in fact a proper use of LoadLibrary not just before Excel "loads" the XLL, but after that and just before it loads the XLL functions (that depend on DLL). I modified the Excel C API template code as follows :

// Excel calls xlAutoOpen when it loads the XLL.
__declspec(dllexport) int WINAPI xlAutoOpen(void)
{
    static XLOPER12 xDLL;      // The filename of this XLL.
    int i;
#if DEBUG
    debugPrintf("xlAutoOpen\n");
#else
#endif
    // Fetch the name of this XLL. This is used as the first arg
    // to the REGISTER function to specify the name of the XLL.
    Excel12f(xlGetName, &xDLL, 0);

    if (xDLL.xltype == xltypeStr)
    {
        std::wstring wpath(xDLL.val.str);
        std::string path(wpath.begin(), wpath.end());
        path.erase(0, 1);
        path = std::regex_replace(path, std::regex("XLL2.xll"), "DLL.dll");
        std::wstring wpath2 = std::wstring(path.begin(), path.end());
        LPCWSTR lpath = wpath2.c_str();
        HINSTANCE hinstLib = LoadLibrary(lpath);
        if (hinstLib == NULL)
            return -1;
    }

#if DEBUG
    debugPrintf("XLL Name : %S\n", xDLL.val.str);
#else
#endif
    // Loop through the g_rgUDFs[] table, registering each
    // function in the table using xlfRegister.
    for (i = 0; i < g_rgNumUDFs; i++)

by adding the if (xDLL.xltype == xltypeStr) part and choose to delay the DLL loading in XLL2's linker properties in VIsual Studio. And I also added $(UniversalCRT_LibraryPath_x64) (or $(UniversalCRT_LibraryPath_x86)) in the additional library directory for the linker in XLL2 project's properties.

And indeed, at debug or at execution, I can open XLL2 and use XLL2 as I was opening and using XLL1 : without any problem. (Important : would I not have add $(UniversalCRT_LibraryPath_x64) as described above, open the XLL2 would have triggered no error, but no functions of XLL2 would have been seen from Excel.)

But would I try to reference XLL2, I still have the same issue. (And referencing it matters as it allows for XLL2 to be loaded next times the user opens Excel, while just opening the XLL2 opens it only for the current Excel instance, not for the next ones.)

What puzzles me is that when you reference an XLL for the first time, Excel calls the XLL's xlAddInManagerInfo12 function (see https://learn.microsoft.com/en-us/office/client-developer/excel/xladdinmanagerinfo-xladdinmanagerinfo12 for instance) while when I put a breakpoint at the beginning of that functions in my XLL2's code, I never break at it at debug when I reference XLL2, I just directly have the Microsoft Excel cannot access the XLL2.xll". There are several possible reasons error message after doing "OK" and that's it. (Also, checking DLLs loaded by that Excel instance in Process Explorer doesn't show XLL2 nor DLL.)

I would like to know the proper way to achieve using XLL2 as I was using XLL1 (opening or referencing).