Excel Automation AddIn fails under .NET6+

Start by reproducing the failure with the shown .NET 6 project file, COM hosting settings, registry entries, and Excel automation add-in scenario. Inspect the IDispatch activation path and compare it with the .NET Framework behavior described in the issue. Done means AddIt() is exposed as an Excel worksheet function without requiring an unspecified wrapper.

Description

Excel Automation AddIn (used to define UDFs in managed code) that works in .NET Framework fails in .NET6.

Reproduction Steps

Under .NET Framework it is straightforward to expose user-defined functions for Excel with a so-called automation addin.
A minimal implementation is something like:

namespace ClassLibrary1
{
    [ComVisible(true)]
    [Guid("2FD6F136-4235-4727-B473-C5C04244E8E3")]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface IClass1
    {
        double AddIt(double a, double b);
    }

    [ComVisible(true)]
    [Guid("0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB")]
    [ComDefaultInterface(typeof(IClass1))]
    [ClassInterface(ClassInterfaceType.None)]
    public class Class1: IClass1
    {
        public Class1() { }
        public double AddIt(double a, double b) => a + b;
    }
}

With the above, and the usual registrations under HKCU\Software\Classes\CLSID{0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB} and HKCU\Software\Classes\Typelib{ECDFE6A5-2DEB-45CB-8BE3-46A7ED117114} etc a new category of functions will appear in the function helper next to the F2 bar called ClassLibrary1.Class1 with AddIt inside.

In trying to replicate the same with .NET6 I have used exactly the same code, the exact same type library from the .NET Framework project exported by tlbexp.exe and the following project file, together with the Registry Settings listed below:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <EnableComHosting>true</EnableComHosting>
    <PlatformTarget>x64</PlatformTarget>
    <Platforms>x64</Platforms>
  </PropertyGroup>
  <ItemGroup>
    <ComHostTypeLibrary Include="C:\Users\ilias\source\repos\ClassLibraryFw1\bin\x64\Debug\ClassLibrary1.tlb"></ComHostTypeLibrary>
  </ItemGroup>
</Project>

[HKEY_CURRENT_USER\Software\Classes\CLSID\{0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB}]
@="ClassLibrary1.Class1"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB}\Implemented Categories]
@="{62C8FE65-4EBB-45e7-B440-6E39B2CDBF29}"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB}\InprocServer32]
@="C:\\Users\\ilias\\source\\repos\\ClassLibrary1\\bin\\x64\\Debug\\net6.0\\ClassLibrary1.comhost.dll"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB}\ProgID]
@="ClassLibrary1.Class1"

[HKEY_CURRENT_USER\Software\Classes\CLSID\{0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB}\Programmable]

[HKEY_CURRENT_USER\Software\Classes\CLSID\{0DD3D8DC-BC65-4818-AA46-8A579BF8E0DB}\TypeLib]
@="{ECDFE6A5-2DEB-45CB-8BE3-46A7ED117114}"

[HKEY_CURRENT_USER\Software\Classes\Interface\{2FD6F136-4235-4727-B473-C5C04244E8E3}]
@="IClass1"

[HKEY_CURRENT_USER\Software\Classes\Interface\{2FD6F136-4235-4727-B473-C5C04244E8E3}\ProxyStubClsid32]
@="{00020420-0000-0000-C000-000000000046}"

[HKEY_CURRENT_USER\Software\Classes\Interface\{2FD6F136-4235-4727-B473-C5C04244E8E3}\TypeLib]
@="{ECDFE6A5-2DEB-45CB-8BE3-46A7ED117114}"
"Version"="1.0"

[HKEY_CURRENT_USER\Software\Classes\Typelib\{ECDFE6A5-2DEB-45CB-8BE3-46A7ED117114}]

[HKEY_CURRENT_USER\Software\Classes\Typelib\{ECDFE6A5-2DEB-45CB-8BE3-46A7ED117114}\1.0]
@="ClassLibrary1"

[HKEY_CURRENT_USER\Software\Classes\Typelib\{ECDFE6A5-2DEB-45CB-8BE3-46A7ED117114}\1.0\0]

[HKEY_CURRENT_USER\Software\Classes\Typelib\{ECDFE6A5-2DEB-45CB-8BE3-46A7ED117114}\1.0\0\win64]
@="C:\\Users\\ilias\\source\\repos\\ClassLibrary1\\bin\\x64\\Debug\\net6.0\\ClassLibrary1.comhost.dll"

With the above settings I am able to activate a COM server (i.e. "COM AddIn" in Excel speak) from VBA using both Early and Late Binding (i.e. by either adding a reference to the type library or by using CreateObject("ClassLibrary1.Class1") so this really basic configuration works as it should.

However the Excel Automation AddIn scenario fails at activation stage in the below place where Excel passes the riid of IDispatch while the classType.GetInterfaces() only contains IClass1:

Clearly some auto-generated machinery that gets created on the fly by the .NET Framework runtime is missing here.
I have seen #86751 in relation to the removal of dynamic generation of ITypeLibs so I am wondering what else was removed around the IDispatch support expected by Excel and whether this has any chance of working in the first place. Comparing the dynamically generated interfaces between .NET Framework and .NET6 leaves a lot to be desired:

.NET Framework:

.NET6

I am also struggling in particular with the Unknown Error 0x80131515 that only seems to appear in google searches in relation to security related file access blocks in Windows explorer. But since I am able to activate the COM addin with VBA (both early and late bound using .NET6) I think it is unrelated.

A native trace is not very revealing either (as I am debugging in a MacBook using a VM I cannot run both Native and Managed at the same time):

'EXCEL.EXE' (Win32): Loaded 'C:\Users\ilias\source\repos\ClassLibrary1\bin\x64\Debug\net6.0\ClassLibrary1.comhost.dll'. Symbol loading disabled by Include/Exclude setting.
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\host\fxr\9.0.2\hostfxr.dll'. Symbol loading disabled by Include/Exclude setting.
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\shared\Microsoft.NETCore.App\6.0.36\hostpolicy.dll'. Symbol loading disabled by Include/Exclude setting.
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\shared\Microsoft.NETCore.App\6.0.36\coreclr.dll'. Symbol loading disabled by Include/Exclude setting.
Exception thrown at 0x00007FF92D0BC798 (KernelBase.dll) in EXCEL.EXE: 0x04242420 (parameters: 0x0000000031415927, 0x00007FF87CE00000, 0x000000AA6B4C6B40).
Profiler was prevented from loading notification profiler due to app settings.
  Process ID (decimal): 18224.  Message ID: [0x2509].
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\shared\Microsoft.NETCore.App\6.0.36\System.Private.CoreLib.dll'. 
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\shared\Microsoft.NETCore.App\6.0.36\clrjit.dll'. Symbol loading disabled by Include/Exclude setting.
'EXCEL.EXE' (Win32): Loaded 'C:\Users\ilias\source\repos\ClassLibrary1\bin\x64\Debug\net6.0\ClassLibrary1.dll'. 
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\shared\Microsoft.NETCore.App\6.0.36\System.Runtime.dll'. 
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\shared\Microsoft.NETCore.App\6.0.36\System.ComponentModel.TypeConverter.dll'. 
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\dotnet\x64\shared\Microsoft.NETCore.App\6.0.36\System.Runtime.InteropServices.dll'. 
Exception thrown at 0x00007FF92D0BC798 (KernelBase.dll) in EXCEL.EXE: 0xE0434352 (parameters: 0xFFFFFFFF80004002, 0x0000000000000000, 0x0000000000000000, 0x0000000000000000, 0x00007FF87CE00000).
The thread '.NET Tiered Compilation Worker' (14836) has exited with code 0 (0x0).
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\Microsoft Office\root\Office16\OSF.DLL'. Symbol loading disabled by Include/Exclude setting.
'EXCEL.EXE' (Win32): Loaded 'C:\Program Files\Microsoft Office\root\Office16\OSFUI.DLL'. Symbol loading disabled by Include/Exclude setting.
The thread 5844 has exited with code 0 (0x0).

Expected Behavior

Ideally this works out of the box and AddIt() is accessible as a worksheet cell function.

Actual Behavior

Exception thrown as described above, UDF is unavailable in Excel worksheet cell.

Regression?

Works in .NET Framework

Known Workarounds

None - but potentially some wrapper that properly exposes/supports IDispatch is required. This seems to be related to the removal of ClassInterfaceType.AutoDual/AutoDispatch, in other words the functionality is no longer there for this to work out of the box.

Configuration

Windows 11 on Arm / .NET 6