.NET DLL in VBA (Excel)

Question

I have spent the last 2 days trying to get a basic VB.NET dll that created to work in VBA. I have read every single post on this sight but i keep getting the same error.

1. I create a VB.NET class (I run Visual Studio's as administrator).

My class

Imports System
Imports System.Collections.Generic
Imports System.Text  
Imports System.Runtime.Interposervices
Imports System.Linq

Public Class TestClass
Function TestMethod(ByVal input as String)
    Return "Hello" & input
End Function
End Class

2. I set "Make assembly comes visible"
3. I set "Register for COM interop"

4. I build and release the dll

5. I open Excel and add a reference to the dll.

Everything works great so far

6. In a sheet I add the below code

   Public Sub test()
       Dim a As TestClass 'Note: Auto fills in once i start typing it in so i know that the DLL there 
       Set a = New TestClass  
       MsgBox (a.TestMethod("World")
   End Sub
   

When i try running the code I get the following error

"ActiveX component can't create object"

I have tried this using 2 computers: Win7 64 bit, Excel 2010 64 bit ,VS 2010 and Win7 64 bit, Excel 2013 , VS 2010 64 bit with know luck. I have read people receiving the same error but I nothing seems to work.

Does anyone see any mistakes in my method?

Answer 1

I have fought this exact same battle many times and still am not completely sure what exactly needs to be done to make this work but I will pass along my most recent experience.

Having built my dll exactly as you described building yours, it worked fine in vba on the same machine I did the initial development on....seems to be because visual studio gets the registration of the dll exactly correct. It also worked when I copied it to the c:/windows/system32 directory and using the Regam.exe from the framework64 directory in Microsoft.net However, when I tried to do the same on another machine, it looked like it registered ok but when the vba code executed it said it could not create the activex object. I moved the dll to the syswow64 dir and I used the reasm.exe from the framework directory instead of the framework64 directory and then it worked.

Answer 2

-add a namespace to your code.
-make the function as public.
-make it com visible and set 'Register for COM interop' as you mentioned.
-your code should be like:

Namespace X
    Public Class TestClass
        Public Function TestMethod(ByVal input As String)
            Return "Hello" & input
        End Function
    End Class
End Namespace

-build your project, you will find a yourProjName.tlb file in \bin\debug or \bin\release.
-open Excel and add a reference to the yourProjName.tlb not to dll.
-modify your code to be like:

Sub test()
    Dim testObj As New TestClass
    Dim myStr As String
    myStr = testObj.TestMethod("ssssss")
    MsgBox myStr
End Sub

This worked for me.
EDIT #1
-I am working on Windows 7 32 bit, Office 2010 professional 32 bit with SP2, Visual Studio 2010 with framework 4.
-configure your project for x64 : from Visual Studio ->Go to the Build Menu ->click Configuration Manager -> Under Active solution platform, click New, then add one with x64 as the platform. Use this platform to compile for x64 , link .
-It is preferred to sign your assembly (very easy) : Project Properties > Signing > Sign the assembly > New -> Enter the name of the file as myKey.snk , no need for password.
-build your project.
- if you are working on the same machine that visual studio in running, then no need to register your assembly, because VS will do since we set 'Register for COM interop'.
-for other client machines where VS in not running, you must register your assembly, take the DLL file only, run cmd as administrator, run the following command:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm /codebase /tlb "D:\out\VB_DLL_001.dll"

Note that this command will generate the *.tlb file for you in the same folder.
-add your dll to Global Assembly cache by running the following command in cmd (that is running as administrator) :

"c:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\gacutil.exe" /i "d:\out\VB_DLL_001.dll"

-from Excel add reference to the generated tlb file : VBA Editor -> Tools -> References -> Browse -> Select the tlb file -> OK . Then run the previous code, I hope it will run successfully .

EDIT #2
Based on the comment, It works by building the DLL for x64 architecture, So, no need for other steps in EDIT #1.