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.

Try to copy your dll and tlb files to C:\Windows\SysWOW64 directory and in VBA editor add reference to tlb from this directory. Don't forget to check the checkbox in VBA references dialog. — gembird
– gembird, Commented Jul 18, 2018 at 11:41
Thank you, when I try to do that nothing happens, maybe because my library is already registered so nothing is added to the available references list in VBA editor, My library is there but it is not referring to C:\Windows\SysWOW64 maybe I should de-register my library first? — Schwan Abdulkareem
– Schwan Abdulkareem, Commented Jul 19, 2018 at 8:23
Have you tried to scroll down and look for the name ShwDolphin in References dialog of VBA editor? Or do you see checkbox checked beside this name already? It is two step process inside of the referenecens dialog. Firs add, then select. — gembird
– gembird, Commented Jul 19, 2018 at 9:19
It is there without me adding it, Visual studio 2017 did that job, When I try to add it manually like you said, nothing new is added. — Schwan Abdulkareem
– Schwan Abdulkareem, Commented Jul 19, 2018 at 9:34
I have no idea what is wrong on your computer. If the checkbox in references dialog is checked the component should be loaded and should write the result into output. I have tested it on my PC and it works. — gembird
– gembird, Commented Jul 19, 2018 at 9:42

Joseph Willcoxson · Accepted Answer · 2018-07-19 14:13:10Z

2

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

answered Jul 19, 2018 at 14:13

Joseph Willcoxson

6,0881 gold badge18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joseph Willcoxson Over a year ago

If an answer works, it is customary to mark it as Answered.

Schwan Abdulkareem Over a year ago

Thank you, I was not aware of that.

Schwan Abdulkareem · Accepted Answer · 2018-07-20 09:55:41Z

1

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.

answered Jul 20, 2018 at 9:55

Schwan Abdulkareem

1071 silver badge9 bronze badges

Comments

Maciej Los · Accepted Answer · 2018-07-18 11:33:26Z

0

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!

edited Jul 18, 2018 at 11:33

answered Jul 18, 2018 at 11:13

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

12 Comments

Schwan Abdulkareem Over a year ago

Thank you for your answer, I think I was missing that point but unfortunately this did not fix my problem, still the same error message :(

Maciej Los Over a year ago

It's impossible! Can you describe how you've created your dll step by step? Seems, you're doing something wrong. Note: that you need to refer to *tlb file in VBA.

Maciej Los Over a year ago

Take a look here: How to: Create a DLL by CSharp or VB.Net for VBA

Schwan Abdulkareem Over a year ago

I followed every single step in that article and still the same error message. Maybe there is something wrong with my machine?

Maciej Los Over a year ago

What version of VS do you have?

|

Collectives™ on Stack Overflow

Not able to use my simple VB class library in VBA

3 Answers 3

2 Comments

Comments

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related