Here is a canonical answer on the 3 main methods to call .Net from Excel (or VBA).

All three ways work in .Net 4.0.

1. XLLs

The 3rd party vendor Add-In Express offer XLL functionality, however its free and easy to use Excel-DNA the author is here https://stackoverflow.com/users/44264

Here is an extract from the Excel-DNA page: https://excel-dna.net/

Introduction

Excel-DNA is an independent project to integrate .NET into Excel. With Excel-DNA you can make native (.xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more. Your entire add-in can be packed into a single .xll file requiring no installation or registration.

Getting Started

If you are using a version of Visual Studio that supports the NuGet Package Manager (including Visual Studio 2012 Express for Windows Desktop), the easiest way to make an Excel-DNA add-in is to:

Create a new Class Library project in Visual Basic, C# or F#.Use the Manage NuGet Packages dialog or the Package Manager Console to install the Excel-DNA package:

PM> Install-Package Excel-DNA

Add your code (C#, Visual Basic.NET or F#):

using ExcelDna.Integration;public static class MyFunctions{    [ExcelFunction(Description = "My first .NET function")]    public static string SayHello(string name)    {        return "Hello " + name;    }}

Compile, load and use your function in Excel:

=SayHello("World!")

2. Automation AddIns

This article by Eric Carter shows how to do it, the article is missing heaps of images so I am copy / pasting the entire article and have recreated the images for preservation.

REF: https://blogs.msdn.microsoft.com/eric_carter/2004/12/01/writing-user-defined-functions-for-excel-in-net/

Excel enables the creation of user defined functions that can be used in Excel formulas. A developer must create a special kind of DLL called an XLL. Excel also allows you to write custom functions in VBA that can be used in Excel formulas. Unfortunately, Excel does not support or recommend writing an XLL that uses managed code. If you are willing to take your chances that your XLL might not run in current or future versions of Excel, there are solutions available that enable this scenario—search the web for “managed XLL”.

Fortunately, there is an easier way to create a user defined function that doesn’t require you to create an XLL dll. Excel XP, Excel 2003, and Excel 2007 support something called an Automation Add-in. An Automation Add-in can be created quite simply in C# or VB.NET. I’m going to show you an example in C#.

First, launch Visual Studio and create a new C# class library project called AutomationAddin for this example.

Then, in your Class1.cs file, enter the code shown below. Replace the GUID with your own GUID that you create by using Generate GUID in the Tools menu of Visual Studio.

using System;using System.Runtime.InteropServices;using Microsoft.Win32;namespace AutomationAddin{  // Replace the Guid below with your own guid that  // you generate using Create GUID from the Tools menu  [Guid("A33BF1F2-483F-48F9-8A2D-4DA68C53C13B")]   [ClassInterface(ClassInterfaceType.AutoDual)]  [ComVisible(true)]  public class MyFunctions  {    public MyFunctions()    {    }    public double MultiplyNTimes(double number1, double number2, double timesToMultiply)    {      double result = number1;      for (double i = 0; i < timesToMultiply; i++)      {        result = result * number2;      }      return result;    }    [ComRegisterFunctionAttribute]    public static void RegisterFunction(Type type)    {      Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"));      RegistryKey key = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);      key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll",RegistryValueKind.String);    }    [ComUnregisterFunctionAttribute]    public static void UnregisterFunction(Type type)    {      Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), false);    }    private static string GetSubKeyName(Type type, string subKeyName)    {      System.Text.StringBuilder s = new System.Text.StringBuilder();      s.Append(@"CLSID\{");      s.Append(type.GUID.ToString().ToUpper());      s.Append(@"}\");      s.Append(subKeyName);      return s.ToString();    }    }}

With this code written, show the properties for the project by double clicking on the properties node under the project in Solution Explorer. Click on the Build tab and check the check box that says “Register for COM Interop”. At this point you have an extra step if you are running on Windows Vista or higher. Visual Studio has to be run with administrator privileges to register for COM interop. Save your project and exit Visual Studio. Then find Visual Studio in the Start menu and right click on it and choose “Run as Administrator”. Reopen your project in Visual Studio. Then choose “Build” to build the add-in.

Now launch Excel and get to the Automation servers dialog by following these steps:

1.  Launch Excel and click the Microsoft Office button in the top left corner of the window. 

2.  Choose Excel Options.

3.  Click the Add-Ins tab in the Excel Options dialog. 

4.  Choose Excel Add-Ins from the combo box labeled Manage.  Then click the Go button.

5.  Click the Automation button in the Add-Ins dialog.

You can find the class you created by looking for AutomationAddin.MyFunctions in the list of Automation add-ins:

Now, let’s try to use the function MultiplyNTimes inside Excel. First create a simple spreadsheet that has a number, a second number to multiple the first by, and a third number for how many times you want to multiply the first number by the second number. An example spreadsheet is shown here:

Click on an empty cell in the workbook below the numbers and then click on the Insert Function button in the formula bar. From the dialog of available formulas, drop down the “Or select a category” drop down box and choose “AutomationAddin.MyFunctions.

Then click on the MultiplyNTimes function as shown here:

When you press the OK button, Excel pops up a dialog to help you grab function arguments from the spreadsheet as shown here:

Finally, click OK and see your final spreadsheet as shown here with your custom formula in cell C3.

3. Calling .Net from Excel VBA

REF: Calling a .net library method from vba

Using the code from the Automation.AddIn project we can easily call the MultiplyNTimes function from Excel VBA.

First Add a reference to the DLL from Excel, to do this you will need to be in the VB Editor. Press Alt + F11, then click Tools menu and References:

Select the AutomationAddIn DLL:

Add VBA code to call the .Net DLL:

Sub Test()Dim dotNetClass As AutomationAddIn.MyFunctionsSet dotNetClass = New AutomationAddIn.MyFunctionsDim dbl As Doubledbl = dotNetClass.MultiplyNTimes(3, 2, 5)End Sub

And hey presto!

Please note if you're working with Classes in C# you will need to mark them with ClassInterface, with an Interface marked with ComVisible = true: Use CLR classes from COM addin in Excel VBA?

Finally there are some excellent MSDN articles about Excel and .Net by "Andrew Whitechapel" - google them

Here's your solution, tested for .NET 2.0 and .NET 4.0, 32 bit and 64 bit, courtesy of Soraco Technologies.

The solution proposed below uses late binding and does not require registration of the .NET assemblies.

Declarations

Add the following declarations to your project:

#If VBA7 ThenPrivate Declare PtrSafe Function GetShortPathName Lib “Kernel32.dll” Alias “GetShortPathNameW” (ByVal LongPath As LongPtr, ByVal ShortPath As LongPtr, ByVal Size As Long) As LongPrivate Declare PtrSafe Function SetDllDirectory Lib “Kernel32.dll” Alias “SetDllDirectoryW” (ByVal Path As LongPtr) As LongPrivate Declare PtrSafe Sub LoadClr_x64 Lib “QlmCLRHost_x64.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)Private Declare PtrSafe Sub LoadClr_x86 Lib “QlmCLRHost_x86.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)#ElsePrivate Declare Function GetShortPathName Lib “Kernel32.dll” Alias “GetShortPathNameW” (ByVal LongPath As Long, ByVal ShortPath As Long, ByVal Size As Long) As LongPrivate Declare Function SetDllDirectory Lib “Kernel32.dll” Alias “SetDllDirectoryW” (ByVal Path As Long) As LongPrivate Declare Sub LoadClr_x64 Lib “QlmCLRHost_x64.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)Private Declare Sub LoadClr_x86 Lib “QlmCLRHost_x86.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)#End If ‘ WinAPI Declarations' Declare variablesDim m_myobject As ObjectDim m_homeDir As String

Initialization

You must initialize the m_homeDir variable to the path where the .NET assemblies are located.

For example, if you install the .NET assemblies in the same folder as the Excel or MS-Access files, you should initialize m_homeDir to:

Excel: m_homeDir = ThisWorkbook.Path

Access: m_homeDir = CurrentProject.Path

.NET Object Creation

Add the following code to your project.

Private Function GetMyObject(dllPath As String, dllClass As String) As Object    Dim LongPath As String    Dim ShortPath As String    LongPath = “\\?\” & m_homeDir    ShortPath = String$(260, vbNull)    PathLength = GetShortPathName(StrPtr(LongPath), StrPtr(ShortPath), 260)    ShortPath = Mid$(ShortPath, 5, CLng(PathLength – 4))    Call SetDllDirectory(StrPtr(ShortPath))    Dim clr As mscoree.CorRuntimeHost    If Is64BitApp() Then        Call LoadClr_x64(“v4.0”, False, clr)    Else        Call LoadClr_x86(“v4.0”, False, clr)    End If    Call clr.Start    Dim domain As mscorlib.AppDomain    Call clr.GetDefaultDomain(domain)    Dim myInstanceOfDotNetClass As Object    Dim handle As mscorlib.ObjectHandle    Set handle = domain.CreateInstanceFrom(dllPath, dllClass)    Dim clrObject As Object    Set GetMyObject = handle.Unwrap    Call clr.StopEnd FunctionPrivate Function Is64BitApp() As Boolean    #If Win64 Then        Is64BitApp = True    #End IfEnd Function

Instantiate the .NET object

Now you are ready to instantiate your .NET object and start using it. Add the following code to your application:

m_homeDir = ThisWorkbook.Path m_myobject = GetMyObject(m_homeDir & “\yourdotnet.dll”, “namespace.class”)

The first argument is the full path to the .NET DLL.

The second argument is the fully qualified name of the requested type, including the namespace but not the assembly, as returned by the Type.FullName property.

Required DLLs

The solution requires deployment of 2 DLLs that are responsible for hosting the .NET CLR. The DLLs are expected to be deployed in the same folder as your Excel or MS-Access file.

The DLLs can be downloaded from Soraco’s web site: https://soraco.co/products/qlm/QLMCLRHost.zip

Licensing LGPL-2.1

We hereby grant you the right to use our DLLs as long as your application does not compete directly or indirectly with Quick License Manager. You can use these DLLs in your commercial or non-commercial applications.

The default policy is preventing the CLR 4 from excuting the legacy code from the CLR 2 :

Set clr = New mscoree.CorRuntimeHost

To enable the legacy execution, you can either create the file excel.exe.config in the folder where excel.exe is located:

<?xml version="1.0" encoding="utf-8" ?><configuration>  <startup useLegacyV2RuntimeActivationPolicy="true">    <supportedRuntime version="v4.0"/>  </startup></configuration>

Or you can call the native function CorBindToRuntimeEx instead of New mscoree.CorRuntimeHost :

Private Declare PtrSafe Function CorBindToRuntimeEx Lib "mscoree" ( _    ByVal pwszVersion As LongPtr, _    ByVal pwszBuildFlavor As LongPtr, _    ByVal startupFlags As Long, _    ByRef rclsid As Long, _    ByRef riid As Long, _    ByRef ppvObject As mscoree.CorRuntimeHost) As LongPrivate Declare PtrSafe Function VariantCopy Lib "oleaut32" (dest, src) As Long''' Creates a .Net object with the CLR 4 without registration.  '''Function CreateInstance(assembly As String, typeName As String) As Variant  Const CLR$ = "v4.0.30319"  Static domain As mscorlib.AppDomain  If domain Is Nothing Then    Dim host As mscoree.CorRuntimeHost, hr&, T&(0 To 7)    T(0) = &HCB2F6723: T(1) = &H11D2AB3A: T(2) = &HC000409C: T(3) = &H3E0AA34F    T(4) = &HCB2F6722: T(5) = &H11D2AB3A: T(6) = &HC000409C: T(7) = &H3E0AA34F    hr = CorBindToRuntimeEx(StrPtr(CLR), 0, 3, T(0), T(4), host)    If hr And -2 Then err.Raise hr    host.Start    host.GetDefaultDomain domain  End If  VariantCopy CreateInstance, domain.CreateInstanceFrom(assembly, typeName).UnwrapEnd Function