How to Call .Net Methods from Excel Vba

How to call .NET methods from Excel VBA?

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

Calling a .net library method from vba

You will need to make a COM-callable wrapper (CCW) for your assembly (DLL). .NET interoperability is a fairly in-depth topic, but it's relatively easy to get something off the ground.

First of all, you need to make sure your entire assembly is registered for COM interop. You can do this on the "Build" tab in Visual Studio by checking "Register for COM Interop". Secondly, you should include the System.Runtime.InteropServices in all your classes:

using System.Runtime.InteropServices;

Next, you should decorate all the classes you want to be exposed with the [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)] attributes. This will make it so you can access the class members properly and using intellisense from within the VBA editor.

You need to have an entry point -- i.e. a main class, and that class should have a public constructor with no arguments. From that class, you can call methods which return instances of your other classes. Here is a simple example:

using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyCCWTest
{
    [Serializable(),  ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class Main
    {
        public Widget GetWidget()
        {
            return new Widget();
        }
    }

    [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class Widget
    {
        public void SayMyName()
        {
            MessageBox.Show("Widget 123");
        }
    }
}

Once you compile your assembly, you should be able to include a reference to it within VBA by going to "Tools > References":

Then you should be able to access your main class and any other classes like this:

Sub Test()
    Dim main As MyCCWTest.main
    Set main = New MyCCWTest.main
    Dim myWidget As MyCCWTest.Widget
    Set myWidget = main.GetWidget
    myWidget.SayMyName
End Sub

To answer your question about List<>: COM doesn't know anything about generics, so they're not supported. In fact, using arrays in CCW's is even a tricky subject. In my experience, I've found the easiest thing to do is to create my own collection classes. Using the example above, I could create a WidgetCollection class. Here is a slightly-modified project with the WidgetCollection class included:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyCCWTest
{
    [Serializable(),  ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class Main
    {
        private WidgetCollection myWidgets = new WidgetCollection();

        public Main()
        {
            myWidgets.Add(new Widget("Bob"));
            myWidgets.Add(new Widget("John"));
            myWidgets.Add(new Widget("Mary"));
        }

        public WidgetCollection MyWidgets
        {
            get
            {
                return myWidgets;
            }
        }
    }

    [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class Widget
    {
        private string myName;

        public Widget(string myName)
        {
            this.myName = myName;
        }

        public void SayMyName()
        {
            MessageBox.Show(myName);
        }
    }

    [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class WidgetCollection : IEnumerable
    {
        private List<Widget> widgets = new List<Widget>();

        public IEnumerator GetEnumerator()
        {
            return widgets.GetEnumerator();
        }

        public Widget this[int index]
        {
            get
            {
                return widgets[index];
            }
        }

        public int Count
        {
            get
            {
                return widgets.Count;
            }
        }

        public void Add(Widget item)
        {
            widgets.Add(item);
        }

        public void Remove(Widget item)
        {
            widgets.Remove(item);
        }
    }
}

And you can use it like this in VBA:

Sub Test()
    Dim main As MyCCWTest.main
    Set main = New MyCCWTest.main
    Dim singleWidget As MyCCWTest.Widget

    For Each singleWidget In main.myWidgets
       singleWidget.SayMyName
    Next
End Sub

NOTE: I have included System.Collections; in the new project so my WidgetCollection class can implement IEnumerable.

Call c# .net library method from Excel vba and pass the Workbook object

So I decided to go with an application level Add-In and check in a Workbook_Open Sub in each Workbook that requires it if the Add-In is installed before continuing. To achieve this, I've followed this tutorial and it works perfectly. However, this is application level, but I need class instances that are bound to specific workbooks.

The solution:

public partial class ThisAddIn
{
    private AddInUtilities utilities;

    protected override object RequestComAddInAutomationService()
    {
        if (utilities == null)
            utilities = new AddInUtilities();

        return utilities;
    }
}

[ComVisible(true), ClassInterface(ClassInterfaceType.None)]
public class AddInUtilities
{
    public WbVSTO GetWbVSTO()
    {
        return new WbVSTO(Globals.ThisAddIn.Application.ThisWorkbook);
    }
}

[ComVisible(true), ClassInterface(ClassInterfaceType.None)]
public class WbVSTO
{
    private Excel.Workbook _ThisWorkbook;

    public WbVSTO(Excel.Workbook ThisWorkbook)
    {
        _ThisWorkbook = ThisWorkbook;
    }
}

'ThisWorkbook object

Private AppVSTO As Object
Private WbVSTO As Object

Private Sub Workbook_Open()

    Set AppVSTO = Application.COMAddIns("AppVSTO").Object
    Set WbVSTO = AppVSTO.GetWbVSTO()
End Sub

What is very important is to make all classes that need to be exposed to Excel ComVisible.

A Simple C# DLL - how do I call it from Excel, Access, VBA, VB6?

You can't access a static member via COM interop. In fact your code doesn't even compile, the method should be in a class. Here is how you can do it:

[InterfaceType(ComInterfaceType.InterfaceIsDual)]
[Guid("01A31113-9353-44cc-A1F4-C6F1210E4B30")]  //Allocate your own GUID
public interface _Test
{
    string HelloWorld { get; }
}

[ClassInterface(ClassInterfaceType.None)]
[Guid("E2F07CD4-CE73-4102-B35D-119362624C47")]  //Allocate your own GUID
[ProgId("TestDll.Test")]
public class Test : _Test
{
    public string HelloWorld { get { return "Hello, World! "; } }
}

The project properties Build tab, select Register for COM interop. So you can see the results quickly. To install the dll on another machine you need to use regasm.

To then consume this:

Dim o : Set o = CreateObject("TestDll.Test")
MsgBox o.HelloWorld

You can also reference the dll and use early binding:

Dim o As TestDll.Test
Set o = New TestDll.Text
MsgBox o.HelloWorld

Call non-registered .NET dll from Excel VBA

You don't explain what the role of .NET would be in your scenario. You can indeed call many C libraries directly from VBA using 'Declare Function'.

If you find .NET useful, and want to call your .NET library functions as user-defined functions (UDFs) from Excel, you can use Excel-DNA. It gives you an .xll add-in library that integrates your .NET library into Excel. You'd still have to open the .xll add-in somehow - either by File -> Open, by adding it as an Excel add-in, or automatically from some VBA in your workbook. But it needs no other registration.

From the .NET library you can call the C .dll functions using P/Invoke, add categories, function and argument descriptions to integrate into the function wizard etc.

(Disclaimer: I'm the developer of Excel-DNA.)

Trying to call a .NET function from VBA - using RUN in VBA

Difference between : adding a tlb as reference to invoke function VS using 'Run'? has the answer to this. Basically, I was missing the reference to the tlb in the VBA project. Apart from registering the dll, the tlb also needs to be added as a reference.

---

Related Topics