6

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.

Improve this question

2 Answers 2

Reset to default
9

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

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 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)
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 I've added my source code
Thanks. That's great! I was searching around and this post here aakinshin.net/posts/wrap-cs-in-com seems right on point too!
|
0

If you want to do this in .NET Core, do not check the box "Register for COM interop" but put this in your project file instead:

    <PropertyGroup>
      <EnableComHosting>true</EnableComHosting>
    </PropertyGroup>

There's no reason to stick to the old .NET framework.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.