Accessing .NET function exposed as COM in Excel

Question

In Convert vba to vb6 and create a .dll - how to - hints, tipps and risks, it was discussed how to convert VBA code into VB.NET code and access the function in VB.NET as COM in Excel.

In Excel, the function has to be accessed this way via VBA:

Public Function getParameterNumberOfMaterial() As Integer
    Dim myclass as New ExcelExample.ExcelVB
    getParameterNumberOfMaterial = myclass.getParameterNumberOfMaterial()
End Function

This means for every VBA function exposed to the user, I have to write a wrapper as above when converting to VB.NET.

Is there a way to use the function directly without writing the VBA wrapper? That is, in Excel, the user can directly use getParameterNumberOfMaterial(), just like the orignal VBA function, without me writing a VBA wrapper.

Answer 1

You can certainly call .NET functions in a COM-visible .DLL directly from Excel, though you will need to modify them slightly to make then compatible with COM and Excel. This example will be in C# since that's what I'm familiar with, but the concept should be the same in VB.Net.

Open a new Class project, and add a line to import the InteropServices library:

using System.Runtime.InteropServices;

Declare an interface that will contain the functions you want to call:

public interface ISample
{
      object func1(int p1);
      object func2(string p1);
}

Only functions listed in the interface will be available in Excel. Note the functions return type is 'object'. This is needed for Excel to properly display the result in the sheet.

Declare the class that will implement the interface, and be exposed via COM.:

[ClassInterface(ClassInterfaceType.None)]  // the proper way to declare COM-visible classes
public class Sample : ISample
{
    public object func1(int p1)
    { 
        // sample implementation
        object[,] retval = new object[1,1]
        retval[0,0] = "a";
        return retval;
    }

    public object func2(string p1)
    {
           // ....
    }

Excel expects all return types to be two-dimensional arrays of objects, so you'll need to convert the return values of your functions this way.

You will also need to add a couple functions that will assist with registering and unregistering the DLL:

    // helper function to get Registry key of given type
    private static string GetSubKeyName(Type type)
    {
        return "CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\Programmable";
    }

    // called when class is being registered
    [ComRegisterFunctionAttribute]
    public static void RegisterFunction(Type type)
    {
        // register the automation DLL function will be visible to Excel
        RegistryKey key = Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
        if (key != null)
        {
            key.SetValue(string.Empty, Environment.SystemDirectory + @"\mscoree.dll");
        }
    }
    

    // called when class is being unregistered
    [ComUnregisterFunctionAttribute]
    public static void UnregisterFunction(Type type)
    {
        try
        {
            // remove automation DLL from registry
            Registry.ClassesRoot.DeleteSubKeyTree(GetSubKeyName(type));
        }
        catch (Exception ex)
        {
            Debug.Print(ex.Message + ex.StackTrace);
        }
    }

• In the Properties screen of your project, in "Application" tab, click "Assembly Information", and check "Make assembly COM-Visible". Click OK.
• In "Build" tab, click "Register for COM Interop" near the bottom.
• Build the project.

In Excel, click the Developer ribbon, click "Add-ins", "Automation" and scroll down to your project. It will be listed in the form [namespace].[classname]

You should now be able to call the functions directly in an Excel worksheet:

=func1(12)

There is also a post on this from an MSDN blogger, but it is quite old

P.S. If there is any part of this you need assitance with converting to VB.Net, please let me know and I can certainly assist.

Answer 2

There used to be a way up to excel 97 but was removed as a security risk. The call function could call functions from COM dlls. http://www.cpearson.com/excel/Call.htm