.NET Core / .NET 6: Creating a TLB or DLL that can be added as reference in VBA

Question

I am trying to do basically what it says in the title: I have created a class library (dll) using .NET 6.0 and I would like to add that as a reference in an Excel/Access VBA document. I diligently followed the steps here:

https://learn.microsoft.com/en-us/dotnet/core/native-interop/expose-components-to-com

No matter what I try, I cannot add the resulting dll as a reference in Excel. I just keep getting the message: 'Can't add reference to the specified file'.

A bit more info: I am able to create the Assembly.comhost.dll file, and I think I have done everything correctly, but no joy.

This is rather frustrating, as doing this in .NET Framework is but a click of a couple of checkboxes. In .NET Core (.NET 6) It seems to be a bit of a nightmare. Also, being able to create a tlb from the dll was always a perfect confirmation that you would be able to add your reference in Excel.

ANY help is most welcome!

Thanks!

Answer 1

in the tutorial you hav ementioned, ther is a topic "Register the COM host for COM". here a file named "ProjectName.comhost.dll" is used to register the assembly to the registry as a COM type library. After doing this, I could use the assembly in VBS with the following code:

set projectObject = CreateObject("ProjectNamespace.ProjectClass")
newValue = projectObject.ComputeNewValue("abc")

Right now, I am not able to establish a reference to that type library in my Excel 2016, but the code sample works in VBA as well.

Answer 2

I could also in VBA

Set projectObject = CreateObject("COMServer.Server")
MsgBox projectObject.ComputePi()

with this repository : https://github.com/dotnet/samples/tree/main/core/extensions/COMServerDemo

But that works only with Registered COM not with RegFree COM (Go check README).

Not every component is a suitable candidate for use under Reg-Free COM. A component is not considered suitable if any of the following are true:

• The component is an out-of-process (ActiveX EXE) server. Only DLLs are supported.
• The component is a system component or part of the operating system, such as XML, Data Access, Internet Explorer, or DirectX® components. For example, you should not attempt to isolate components such as Microsoft XML (MSXml.dll) or Microsoft Internet Controls (SHDocVw.dll). These components are either part of the operating system, or can be installed with a separate redistributable package.
• The component is part of an application, such as Microsoft Office. For example, you should not attempt to isolate components such as the Microsoft Word Object Model or Microsoft Excel Object Model. These two components are part of Office and can only be used on a machine that has the full Office product installed on it.
• The component is intended for use as an add-in or a snap-in, such as an Office add-in or a control in a Web browser. Such components typically require some kind of registration scheme defined by the hosting environment that is beyond the scope of the manifest itself. The other problem is an arbitrary application may not be designed to recognize isolated components, as it probably doesn't have a way to reference your component through a manifest.
• The component manages a shared physical or virtual system resource. For example, it could manage some kind of data connection shared between multiple applications or a device driver for a print spooler.

Source : https://social.msdn.microsoft.com/Forums/en-US/cc08575d-3506-4a0f-a9e2-f23c2162ad38/using-net-excel-addin-with-registrationfree-com?forum=innovateonoffice

But I couldn't find a way to add it in Excel as a reference.