Silent failure for callback in Excel add-in when using COM

Question

Using Visual Studio 2022 and Excel 365...

I've been able to use ExcelDna for plenty of tasks. I run into trouble when I try to use the Excel COM interface directly.

To start, I define a button in the Custom UI XML:

<button id='button_sample' label='Sample' onAction='OnSample' />

This simple function works:

public void OnSample(IRibbonControl control)
{
    Debug.WriteLine("Starting sample...");
}

---

Now I want to expand the function to know about a user's selection:

public void OnSample(IRibbonControl control)
{
    Debug.WriteLine("Starting sample...");
    try
    {
        Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
        Excel.Range range = (Excel.Range)app.Selection;
        object[,] values = (object[,])range.Value2;
        Debug.WriteLine($"Length: {values.Length}");
    }
    catch (Exception ex)
    {
        MessageBox.Show($"Exception: {ex.Message}");
    }
}

I need to add the interop assembly. However, the following line:

using Excel = Microsoft.Office.Interop.Excel;

gets this error:

The type or namespace name 'Office' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)    

From the "Solution Explorer" pane, I can right-click "Dependencies" and choose "Add COM Reference...". I search for "excel" and see "Microsoft Excel 16.0 Object Library". I enable this and hit "OK".

Now the build gets a new error:

Could not determine the dependencies of the COM reference "Microsoft.Office.Interop.Excel". Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY))   

If I right-click the assembly and choose "Open Containing Folder", the result is my project's directory. The assembly can't possibly be there!

So I remove that assembly and go back to "Add COM Reference...". This time I click "Browse..." and find an older version:

C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll

No more build errors, but my OnSample() function doesn't run. There's no debug output or exception; it's just a silent failure.

And that's where I'm at now.

---

Ok, so a couple other things I've tried:

• Reference NuGet for latest version in my .csproj file:

<PackageReference Include="MSOffice.Interop" Version="16.*" />

That gets an error:

Package 'MSOffice.Interop 16.0.55555' was restored using '.NETFramework,Version=v4.6.1, .NETFramework,Version=v4.6.2, .NETFramework,Version=v4.7, .NETFramework,Version=v4.7.1, .NETFramework,Version=v4.7.2, .NETFramework,Version=v4.8, .NETFramework,Version=v4.8.1' instead of the project target framework 'net6.0-windows7.0'. This package may not be fully compatible with your project.

The silent failure continues.

• Install VSTO for Visual Studio:

Go Control Panel -> Programs and Features. Right-click Visual Studio Community 2022 and choose Modify. Select "Individual components" in the top bar and search for "VSTO".

That didn't help with the "Microsoft Excel 16.0 Object Library" error above though.

Answer 1

Turns out I just needed to use ExcelDna.Interop by adding to my .csproj file:

<PackageReference Include="ExcelDna.Interop" Version="*" />

Answer 2

The library ExcelDna.Interop is currently only available with version 15 for Office 13 which ran out of support, so I don't think this is a good solution. However I don't find another one either, when I want to add the Object Library as an absolute reference.

EDIT: I found all necessary files with version 16 locally at 'C:\Program Files (x86)\Microsoft Office\root\Office16\DCF'.