How to call .NET methods from Excel VBA?

call c# dll from excel
call vb net dll from vba
excel vba createobject dll
excel vba dll
how to create a dll in visual studio
loading net framework excel
vb net create com interop dll
make assembly com visible c#

I found a way to call .NET 2 code directly from VBA code:

Dim clr As mscoree.CorRuntimeHost
Set clr = New mscoree.CorRuntimeHost
clr.Start
Dim domain As mscorlib.AppDomain
clr.GetDefaultDomain domain
Dim myInstanceOfDotNetClass As Object
Set myInstanceOfDotNetClass = domain.CreateInstanceFrom("SomeDotNetAssembly.dll", "Namespace.Typename").Unwrap
Call myInstanceOfDotNetClass.ExecuteSomeDotNetMethod

I added references to mscoree.tlb and mscorlib.tlb to Excel VBA using Tools -> References.

This works for .NET CLR 2 assemblies, up to .NET framework version 3.5.

I need to make it work with .NET 4.

I understood that .NET CLR4 introduced another, version agnostic, way of creating an instance of the runtime and I have found a code example written in C++: http://dev.widemeadows.de/2014/02/04/hosting-the-net-4-runtime-in-a-native-process/

My Excel VBA skills are not enough to translate those few lines of code.

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 Long

Private 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).Unwrap
End Function

How to call a C# .NET class library from Excel VBA: Guided Example , This short guide should get you started in calling your C# .NET functions from Excel VBA. If you plan on using a GUI, see the other guide as this  3. Call Public Functions from Excel VBA; 4. Deployment Unblock the DLL (Windows Security) Registering the Assembly Related articles; References; 1. Create C# Library Class. Put this code in a new C# file in a new Visual Studio project. It is bare bones with one simple function that will later be called from Excel vba.

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.MyFunctions
Set dotNetClass = New AutomationAddIn.MyFunctions

Dim dbl As Double
dbl = dotNetClass.MultiplyNTimes(3, 2, 5)

End Sub

And hey presto!


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

A Beginner's Guide to calling a .NET Framework Library from Excel , NET Framework library directly from Excel on Windows, particularly if you are using To call a method in a class in our library from Excel we need the class to Paste the VBA code below into the code window for Sheet1:. To trust the location of the workbook. Start Excel. Click the File tab. Click the Excel Options button. In the categories pane, click Trust Center . In the details pane, click Trust Center Settings . In the categories pane, click Trusted Locations . In the details pane, click Add new location . In

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 Then
Private Declare PtrSafe Function GetShortPathName Lib "Kernel32.dll" Alias "GetShortPathNameW" (ByVal LongPath As LongPtr, ByVal ShortPath As LongPtr, ByVal Size As Long) As Long
Private Declare PtrSafe Function SetDllDirectory Lib "Kernel32.dll" Alias "SetDllDirectoryW" (ByVal Path As LongPtr) As Long
Private 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)
#Else
Private Declare Function GetShortPathName Lib "Kernel32.dll" Alias "GetShortPathNameW" (ByVal LongPath As Long, ByVal ShortPath As Long, ByVal Size As Long) As Long
Private Declare Function SetDllDirectory Lib "Kernel32.dll" Alias "SetDllDirectoryW" (ByVal Path As Long) As Long
Private 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 variables
Dim m_myobject As Object
Dim 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.Stop
End Function

Private Function Is64BitApp() As Boolean

    #If Win64 Then
        Is64BitApp = True
    #End If
End 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.

C# Excel Interop, It is possible for VBA code (which is a COM component) to call C# code. VBA can call .NET managed code by using a COM Callable Wrapper (CCW). to true if you want all the public interfaces, classes, methods, etc to be visible from VBA. VBA can call .NET managed code by using a COM Callable Wrapper (CCW). To allow your C# code to be accessible from VBA you need to make sure that all the necessary types are COM visible. All public, COM visible interfaces and data types will be exposed allowing values to be passed and returned to VBA. mscoree.dll. Once the C# class has been made COM visible the corresponding registry entries need to be added.

I'm not sure if this was just a coincidence or because I posted related question. SO showed me your question and I think I could also contribute something.

When working with VBA and DLL, most solutions that I've seen so far is telling me to register the DLL and make it com/gac visible. If you are doing this in your PC that's absolutely fine but if you are distributing your VBA application, you don't really want to install DLLs in their system. You might not have permission or you don't really want to go through install/uninstall process or messing with referencing issues.

However you can load dlls dynamically using some windows APIs.

DLL

Now the question is how to access .NET dll from vba? if your clients have mixed os architecture x86 x64 you need to handle this accordingly. Lets assume we are working on 32bit office/Excel.

If you create a .NET dll and would like to access it from VBA it will throw an error message similar to "Can't find the dll entry point". thankfully Robert Giesecke has created an abstract wrapper which will allow you to create simple DLL consumable via VBA.

A template can be found here.

All you have to do

  1. Create a new class project in visual studio
  2. Set the project platform either x86 for 32bit and otherwise
  3. Create your methods within a main class.
  4. create another class which will return your main class as object (is returning to vba)
  5. (follow the template from his website)

Lets assume you have followed his template and created a test method as following.

[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class YOUR_MAIN_CLASS
{
    [return: MarshalAs(UnmanagedType.BStr)]
    public string FN_RETURN_TEXT(string iMsg)
    {

        return "You have sent me: " + iMsg + "...";
    }
}

and your unmanagedexport class:

static class UnmanagedExports
{
    [DllExport]
    [return: MarshalAs(UnmanagedType.IDispatch)]
    static object YOUR_DLL_OBJECT()
    {
        return new YOUR_MAIN_CLASS();
    }
}

Preparing to access the dll from vba side

Add the DLL to your root folder:

#If VBA7 Then 
    Public Declare PtrSafe Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As LongPtr
    Public Declare PtrSafe Function YOUR_DLL_OBJECT Lib "YOUR_DLL.dll" () As Object
#Else
    Public Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal strFilePath As String) As Long
    Public Declare Function YOUR_DLL_OBJECT Lib "YOUR_DLL.dll" () As Object
#End If

Now It's all about loading the dll and creating & accessing objects it in vba. that would be:

LoadLibrary (FN_APP_GET_BASE_PATH & "YOUR_DLL.dll")
dim mObj as object
set mObj = YOUR_DLL_OBJECT()
debug.print mObj.FN_RETURN_TEXT("Testing ..")

the output should be

"You have sent me: Testing ....."

Advantages I personally don't like installing and referencing dlls. By following above template, you don't need to reference anything, you don't need to install anything just load and work with your the DLL with full freedom.

NOTE: I assume the dll/.net code is yours and you can compile it again with above templates to.

I had success with above template and created a .NET non-blocking notifications for vba you can have a look here: non-blocking "toast" like notifications for Microsoft Access (VBA)

How to Use .NET Class Library in Excel VBA, NET class libraries and reference them from within Excel's VBA code editor. Public Function Multiply(ByVal inVal As Integer) As Integer Dim computedValue  I am working on an add-in that will only be used in Excel. I want to invoke one of the instance methods I've written in .NET from within an Excel macro. Currently, I have an ADXAddinModule class that contains all my add-in interface code. This works great for most of my needs, but those methods can't be seen in Excel VBA.

Call DLL function from EXCEL VBA - MSDN, NET Framework). I have added my dll to VBA project references. Can anyone help how to call function? DLL CODE: Option Strict On Option  Invoke the DLL from within your VBA or VB. To use the variables and methods in the C# DLL, we need to call the DLL inside VBA. Add a button to the Access form and then add a click event to it. In the click event we create object from the DLL and call its method to do the addition. There are two ways to call a .Net DLL. See below.

Walkthrough: Call code from VBA in a Visual C# project, This walkthrough demonstrates how to call a method in a document-level customization for Microsoft Office Excel from Visual Basic for  The Call statement is not required, but if you use it, you must enclose any arguments in parentheses. You can use a Sub procedure to organize other procedures so they are easier to understand and debug. In the following example, the Sub procedure Main calls the Sub procedure MultiBeep, passing the value 56 for its argument.

How to create C# or VB.Net DLLs for VB or VBA in Access or Excel, Below is a sample VBA to call SimpleCalc.dll. test() function invokes the .Net DLL by creating a new object from Calc class and then call its methods. Public  I know in VBA you can just call a COM Add-in and use it's methods, so is there a C# equivalent of that. I've tried calling the DLL to call the methods but whichever way I call it, it returns an exception saying it can't find the xxx.cs class or that it can't find an entry point (using PInvoke).

Comments
  • Have you installed the Visual Studio Tools for Office Runtime?
  • The TLBs are still there with CLR4 (in C:\Windows\Microsoft.NET\Framework\v4.0.30319). What exactly is the problem?
  • I believe this has to do with the registration of the dll. Take a look. Hope it helps. geeksengine.com/article/register-dll.html
  • @SimonMourier the problem is that even though the .NET 4 reference gets set, there's still an automation error when running it with .NET 3.5 (and 2) disabled. It only works if .NET 2 is enabled on the PC. This question is intended to determine how to instantiate a COM object that resides in a .NET 4 assembly, without having to register the dll using regasm.exe.
  • That seems to be the only answer that actually answers the question, so have my thanks and an upvote! I do think, however, that your answer would benefit from a little more explanation: What are the T magic numbers? Why do we need VariantCopy instead of a simple Set? CorBindToRuntimeEx is deprecated in .NET 4, why do we use it anyway?
  • @Heinzi, T is a buffer holding 2 CLSID used by CorBindToRuntimeEx. VariantCopy is used instead of Set so it doesn't increment the reference count on the variable. I use CorBindToRuntimeEx here since I don't think there's another way around it and I doubt that this method it will be removed any time soon.
  • I really appreciate the effort for this answer, but it does not include a method that works the same way as the original question shows. Although the question doesn't explicitly state this, the main reason to use that method is so you can call the assembly without having to register it in VBA as a reference. All the methods you show require the DLL to be registered and available as a reference.
  • It's 12:54am here Jon let me get back to you tomorrow, Thxs
  • I missed that you wanted to do it without a reference, you can still do it without admin privileges, but unfortunately need regasm. I have had excellent experiences and great projects using Excel-DNA. Kicking myself I forgot to mention Robert Giesecke's work in this answer.
  • @user1983691 could you please accept this canonical answer. It'll give you a few rep points and let everyone know the problem is solved. Thanks
  • Great answer. Do you know how to do the same thing with more complex types like arrays ? I just need to pass arrays between VBA and DLL rather than just integers or strings
  • Do you by any chance work for "Soraco Technologies"? If you do you should disclose your affiliation. See Limits for self-promotion in answers
  • Could you check your example? I tried this with my own class and kept getting a "Bad DLL calling convention" error from MS Access, so I created a new DLL based entirely on your example and get the same error at this line "set mObj = YOUR_DLL_OBJECT()". This happens with Make assembly COM-visible checked or unchecked. x86 platform.
  • Or if you upload your source for VBA_TOOLS to github again, I could see for myself exactly how you built your library.
  • I found the problem! The conditional declare was following the VBA7 path and using the PtrSafe declare, which wasn't working. I removed it so it uses the traditional declare, which worked. I should be on VBA7 though, so looked at the declare and added " As Object" to the end. It's working now!