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

Question

I have a simple class library written in c#.

using System;
namespace TestDll
{
    public class Test
    {
        public string HelloWorld
        {
            get
            {
                return "Hello World";
            }
        }
    }
}

My question is how can I call this HelloWorld function from Microsoft Office Visual Basic (which I think is VB6)?

My first step was to add the DLL as a reference - but on browsing and selecting the compiled DLL the message "Can't add a reference to the specified file." was thrown.

Can anyone point me in the right direction as to why/how to get this working?

Thanks in advance SO!

Answer 1

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

Answer 2

And to expand on registering the DLL on different computers.

Once you compile and build the above code on your development machine, if you have

The project properties Build tab, select Register for COM interop.

your Visual Studio output folder (usually bin\Debug) where the compiled *.dll is found will also have a *.tlb file.

This *.tlb file is a 'Type Library'. And is needed by the client machine to understand the different 'Types' in your *.dll and to basically tell the client machine how to use it.

By setting the above 'Register for COM interop' -- aswell as a *.tlb file being produced, the assembly(dll) is registered on your machine, and is therefore accessible.

In VBA you can now add this file as a reference by

VBA Editor -> Tools -> References -> Browse -> Select

this will allow you to then declare the classes found in your library.

Dim TestClass As Test
Set TestClass = New Test
MsgBox TestClass.HelloWorld

HOWEVER - if you want to then use your dll on a different client machine, you will have to use regasm.exe - to register the assembly(dll) on that machine.

This can be done by the command line,

regasm.exe

in this case

regasm.exe TestDll.dll

once you have registered the assembly on the new client machine, you will be able to access it by again adding a reference to its *.tlb

Hope this helps!

Answer 3

Just wanted to comment that in Visual Studio 2008, to get the .tlb file generated you must also go under the Application | Assembly Information and select "Make Assembly COM visible". Took me a while to find that, so hope it helps others out.

Answer 4

To add to AnthonyWJones's good answer, you'll also need to register your DLL using Regasm.exe which adds the necessary registry entries.