Not able to use my simple VB class library in VBA

Question

So this is my first post in this great community and I'm an absolute beginner, I'm sure I'll get good advice from here.

I mad this simple VB class library in Visual Studio 2017

Public Class Addition
    Public Function Add(No1 As Long, No2 As Long) As Long
        Return No1 + No2
    End Function
End Class

I made it COM visible and checked Register for COM interop. and build the project.

In my access VBA project, I've added the reference to my Dll without a problem and put the following code behind the click event of a button.

Private Sub Command0_Click()
Dim myCalc As ShwDolphin.Addition
Set myCalc = New ShwDolphin.Addition
Dim Num As Long
Num = myCalc.Add(2, 5)
Debug.Print Num
End Sub

"ShwDolphin" is VB assembly name.

But I always get this error message "Runtime error 429 Active X component can't create an object"

Can you please tell me what I'm doing wrong here? This is driving me crazy.

Thank you very much.

Answer 1

Reading the comments, my guess is that you have a difference in what I call "bitness". Default projects in Visual Studio are generally 32-bits. If you build a 32-bit COM DLL, it can only be loaded by 32-bit processes. If your Office installation is 64-bit, it will never work. What you will need to do is build/register it for 64-bits.

Now, if you build for MSIL and not any specific processor (x86 or x64), you don't have to really rebuild for 64-bits. All that is necessary is to register for 64-bits. (The default build configuration is MSIL.)

So you need to use the 64-bit regasm in C:\windows\Microsoft.NET\Framework64\v4.0.30319\regasm.exe

use that regasm with options of /tlb /codebase "thenameofyour.dll"

If you built x64 and Office is 32-bit, then do the opposite: use the 32-bit regasm, but odds are that you are using defaults which is 32-bit.

You can also look at this answer:activex can't create object by vbs but can by classic asp

Answer 2

To be able to use custom .NET class in VBA, a *.dll must expose methods (and properties) to COM automation. What this means to you? You have to create an interface.

Public Interface IAddition
    Function Add(No1 As Long, No2 As Long) As Long
End Interface

Public Class Addition
    Implements IAddition
    Public Function Add(No1 As Long, No2 As Long) As Long Implements IAddition.Add
        Return No1 + No2
    End Function
End Class

Usage:

Private Sub Command0_Click()
Dim myCalc As ShwDolphin.IAddition
Set myCalc = New ShwDolphin.Addition
Dim Num As Long
Num = myCalc.Add(2, 5)
Debug.Print Num
End Sub

For further details, follow the instruction provided in my past answer: Generics and Com Visible .NET libraries

You might be interested in this article too: Extend your VBA code with C#, VB.NET, or C++/CLI

Good luck!

Answer 3

So after much frustration, I was able to make it work by doing the following:

1- Make a new project with the same code (It was easier than removing previous dlls).

2- Disable "Register for COM interop" option in project properties of Visual Studio.

3- Build project.

4- Use use the 64-bit regasm in C:\windows\Microsoft.NET\Framework64\v4.0.30319\regasm.exe to register my assembly.

5- Add reference to the generated tlb file in the VBA editor.

Thanks to everyone for your generous help, thanks to this community. I don't know why I had to do this way, but I'm using 64-bit windows and 64-bit office. Also like @dee said adding interface was not necessary, it just worked.