Office Excel VBA How to call a C# function from VBA and Excel-iaspnetcore.com

VSTO doesn't provide anything for developing user-defined functions for Excel. You need to create such projects on your own - it can be an automation add-in or XLL one.

The C# code on VS 2019.

Both approaches are well described in the Using C# .NET User Defined Functions (UDF) in Excel article.

If your choice is XLL add-ins you may consider using Excel-DNA which simplifies development a lot.

To be able to use custom .NET class in VBA, a *.dll must:

1) choice COM automation

2)expose methods and properties

To be able to use custom .NET class in VBA, a *.dll must expose methods (and properties) to COM automation. What this means to you? You have to create an interface.

3) build/register DLL for 64-bits.

Reading the comments, my guess is that you have a difference in what I call "bitness". Default projects in Visual Studio are generally 32-bits. If you build a 32-bit COM DLL, it can only be loaded by 32-bit processes. If your Office installation is 64-bit, it will never work. What you will need to do is build/register it for 64-bits.

Now, if you build for MSIL and not any specific processor (x86 or x64), you don't have to really rebuild for 64-bits. All that is necessary is to register for 64-bits. (The default build configuration is MSIL.)

So you need to use the 64-bit regasm in C:\windows\Microsoft.NET\Framework64\v4.0.30319\regasm.exe

use that regasm with options of /tlb /codebase "thenameofyour.dll"

If you built x64 and Office is 32-bit, then do the opposite: use the 32-bit regasm, but odds are that you are using defaults which is 32-bit.

2.My COM DLLs are not longer working from excel. (as far as i can tell its after installing .Net5 or .Net6 preview). The DLL is still created using .Net 4.8. The same code is working on a diferent VM.

The DLL is recognised by excel and so are the functions defined int he DLL. Howvere the code will not execute, the VBA just stops when it reached the call to the DLL fucntion.

Heres an Exacmple Code：

using System;
using System.Runtime.InteropServices;

namespace TestCOM
{
    [Guid("A3E6B57A-E794-439B-AE96-ADED4D89F991")]
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class Class1
    {
        public double test()
        {
            return 999;
        }
    }
}

vba

Dim test1 As New TestCOM.Class1

Function test2() As Double

test2 = test1.test()

End Function

It could be one of multiple things:

Bit widths do not match. 32-bit Excel trying to load 64-bit COM DLL, or 64-bit Excel trying to load 32-bit COM DLL.
Excel has already loaded a versions of the .NET Framework that is not compatible with the framework being used by the COM DLL. Excel may have already loaded a COM DLL that uses .NET Framework 4.8, and so it can't load another COM DLL that uses .NET 5.
COM DLLs in .NET 5 may not be fully supported -- may need to check the readme's and blogs. I've personally not kept up on that aspect of using .NET 5.