2

My ultimate objective is to build a class library (Calculator.dll) in C#, containing functions that will be made accessible to Excel via VBA.

I had hoped to avoid the need to register the dll, and rather, use the Declare Function statement in the VBA code, but apparently this is not possible and my research has pointed me to needing to make the class library COM-Visible, then register it, and add it as a reference to the VBA project. It seems like this was a matter of clicking a box in a dialog for the project properties in earlier Visual Studio versions, but I don't see the box in VS2022?!

These are the steps I've taken with a "toy" example, and the problems I've encountered.

(1) I built the following .dll with a class Calculate and a simple method to Add two integers. Since we use 32-bit Excel, I configured it to x86.

namespace ClassLibraryCalculator
{
    public class Calculate
    {
        public int Add(int a,int b){ return a + b; }
    }
}

(2) From the command prompt, running as administrator, I attempted to run regsvr32 "ClassLibraryCalculator.dll" but encountered an error "..was loaded but the entry-point DllRegisterServer was not found"

From searching around, the remedy for this is said to be to modify the project properties, the was (in earlier versions of VS?) a dialog box enter image description here

with a check box, but I see nothing related to COM in Visual Studio 2022 Project Properties.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Valarenti
  • 177
  • 1
  • 2
  • 13

1 Answers1

4

First make sure you create a project of type "Class library (.NET Framework)" that uses the classic Windows .net Framework like version 4.8. Do not choose the "Class library" project type that is based on .NET Standard and supports multiple different operating systems.

enter image description here

After creating the project, open the project properties by right-clicking on the project in the Solution Explorer and selecting "Properties".

In the project properties, go to "Application", then click on "Assembly Information".

enter image description here

In the "Assenmbly Information" dialog, enable the "Make assembly COM-Visible" checkbox.

enter image description here

For details see Turn a simple C# DLL into a COM interop component

Note that you can use the setting "Register for COM interop" on the "Build" area of the project properties to register the DLL (VS needs to run with admin privileges for that). When trying to register from command line, don't use "regsvr32", but "regasm". Be aware that there is a 32bit and a 64bit version of regasm available. Use the correct one.

Further important hints:

  • If you run Office as 64bit executables, choose "Platform target = x64" in the Build area in the project properties.
  • If you run Office as 32bit executables, choose "Platform target = x86" in the Build area in the project properties.

Note that "Any CPU" will not work. If you want to support both 32bit and 64bit instances of Office, you need to build two variants of your dll and register both.

This is the sample class I use for testing:

using System;
using System.Runtime.InteropServices;

namespace ClassLibrary4
{
    [Guid("ECB682F0-8AF1-40EA-B73A-1FACF3C7F742")]
    public interface IClass1
    {
        void Test();
    }

    [Guid("4EC93EC5-FA8C-4E82-8931-E47D979BDA93")]
    public class Class1 : IClass1
    {
        public void Test()
        {
            System.Console.Beep();
        }
    }
}

Note that you cannot use these GUIDs but must use your own ones. Use the "Tools > Create GUID" command in Visual Studio for that.

Here is how I use the class from VBA after activating the reference:

' Declare variable with interface type
Dim c As ClassLibrary4.IClass1
' Create from class type
Set c = New ClassLibrary4.Class1
' Use.
c.Test
NineBerry
  • 26,306
  • 3
  • 62
  • 93
  • Thanks! The key was picking the right template "Class library (.NET Framework)", and very useful to know Any CPU doesn't work. I was able to regasm the dll. I'm still moving forward - it doesn't automatically appear in the Tools>References? – Valarenti Dec 31 '22 at 00:43
  • @Valarenti It does appear there for me in the VBA Editor. Also make sure you have an exportable class (that is a public class with a public interface and both class and interface are marked with GUIDs) – NineBerry Dec 31 '22 at 00:46
  • No, I probably do not. I defined the class (which is public) simply as shown in the code block above. No interface, and no marking with GUIDS. Would you be able to sketch the simple class embellished with those enhancements? If so I would be grateful, if not you have helped a great deal already. – Valarenti Dec 31 '22 at 00:52
  • @Valarenti I've added my source code – NineBerry Dec 31 '22 at 01:00
  • Thanks. That's great! I was searching around and this post here https://aakinshin.net/posts/wrap-cs-in-com/ seems right on point too! – Valarenti Dec 31 '22 at 01:02
  • I encountered a few more pitfalls (rookie mistakes): (1) I was using the GUID from my assembly, instead of a unique GUID for the interface and for the class. (2) Then for some reason, my VS2022 did not have a Tools>Create GUID, so I found a GUID generator in the marketplace (https://marketplace.visualstudio.com/items?itemName=MadsKristensen.insertguid). I also ended up running VS as administrator with the Register for COM interop toggled on. To round this out completely, I next want to figure out how to get Intellisense for the class to appear when in VBA. – Valarenti Dec 31 '22 at 01:58
  • @Valarenti In order to have intellisense support in VBA Editor, use the interface type instead of the class type. I have changed my code above to show how. You could change the c# source code to use a so-called dual interface and would then also have type information on the object itself, but this is generally not recommended – NineBerry Dec 31 '22 at 02:22
  • You don't have to stick to .NET Framework 4.8 to build a COM object visible from VBA. You can make COM object with .NET Core 3,5->7 (https://learn.microsoft.com/en-us/dotnet/core/native-interop/expose-components-to-com). What you don't have easily though in this case is a type library while with 4.8 it's automatically build. Also you don't have to use the registry, there's reg free support (.NET 4.8 and Core) https://learn.microsoft.com/en-us/dotnet/core/native-interop/expose-components-to-com#enabling-regfree-com – Simon Mourier Dec 31 '22 at 08:15