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. — Daniel Dušek, Jul 18 '18 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, Jul 19 '18 at 08: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. — Daniel Dušek, Jul 19 '18 at 09: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, Jul 19 '18 at 09: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. — Daniel Dušek, Jul 19 '18 at 09:42
Did you use the VB code exactly like above? without adding interface? — Schwan Abdulkareem, Jul 19 '18 at 09:44
Yes exactly your vb-code like above. Interface is not necessary, an default will be created, although it is good practice. It should work as it is, at least on my pc it works. When you get it working you should look at the tutorials and define your own interface. — Daniel Dušek, Jul 19 '18 at 09:48
Try to add the tlb and dll to `C:\Windows\System32` as well, I not sure where the vba is looking for it. Select the `tlb` then and check the checkbox finally. — Daniel Dušek, Jul 19 '18 at 09:54
I have edited your question and added the `com` tag. Maybe some `com` expert will see it and give you some advice. — Daniel Dušek, Jul 19 '18 at 10:04
@dee, you're wrong. To be able to use .net library within vba, you need to use interfaces! — Maciej Los, Jul 19 '18 at 10:16
Have a look [here](https://richnewman.wordpress.com/2007/04/15/a-beginners-guide-to-calling-a-net-library-from-excel/), it might help. And of course check the bitness problem which is mentioned in answer of Joseph. — Daniel Dušek, Jul 19 '18 at 19:14

score 2 · Accepted Answer · answered Jul 19 '18 at 14:13

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

If an answer works, it is customary to mark it as Answered. – Joseph Willcoxson Jul 20 '18 at 13:19 — Joseph Willcoxson, Jul 20 '18 at 13:19
Thank you, I was not aware of that. – Schwan Abdulkareem Jul 20 '18 at 14:32 — Schwan Abdulkareem, Jul 20 '18 at 14:32

score 1 · Answer 2 · answered Jul 20 '18 at 09:55

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.

Maciej Los · Answer 3 · 2018-07-18T11:33:26.140

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 '18 at 11:33

answered Jul 18 '18 at 11:13

Maciej Los

8,468
1
20
35

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 :( – Schwan Abdulkareem Jul 19 '18 at 08:20
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 Jul 19 '18 at 08:26
Take a look here: [How to: Create a DLL by CSharp or VB.Net for VBA](http://www.geeksengine.com/article/create-dll.html) – Maciej Los Jul 19 '18 at 08:50
I followed every single step in that article and still the same error message. Maybe there is something wrong with my machine? – Schwan Abdulkareem Jul 19 '18 at 09:28
What version of VS do you have? – Maciej Los Jul 19 '18 at 10:02
@SchwanAbdulkareem, close and re-open VS with admin privileges. Then try to compile you library again. Adn please, read this: [How to: Install an Assembly into the Global Assembly Cache](https://learn.microsoft.com/en-us/dotnet/framework/app-domains/how-to-install-an-assembly-into-the-gac). MSDN states: `Only strong-named assemblies can be installed into the GAC. For information about how to create a strong-named assembly`. – Maciej Los Jul 19 '18 at 10:20
Visual Studio 2017. – Schwan Abdulkareem Jul 19 '18 at 10:24
I did that already and my class library is registered correctly. I also tried to use my library in another vb project and it worked correctly. – Schwan Abdulkareem Jul 19 '18 at 10:27
@SchwanAbdulkareem, Did VS create `ShwDolphin.tlb` file? – Maciej Los Jul 19 '18 at 10:34
Did you add reference in VBA to this `tlb` file or to `dll`? – Maciej Los Jul 19 '18 at 10:37
The reference is added automatically to the list, I just checked the box. – Schwan Abdulkareem Jul 19 '18 at 10:39
Remove that reference from VBA project and add it again. Click `Tools->References..` then click `Browse` button and go to your `ShwDolphin` project folder. Find `Debug` folder and select `ShwDolphin.tlb` file and click `OK`. Save xlsm file, close it and re-open again. – Maciej Los Jul 19 '18 at 10:42

Not able to use my simple VB class library in VBA

3 Answers3