.NET DLL to VBA (Excel)
I have spent the last 2 days trying to get a basic VB.NET dll built to work in VBA. I have read every post on this crosshair but I keep getting the same error.
- I am creating a VB.NET class (I am running Visual Studio 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
- I have set "Make assembly visible"
- I have installed "Register for COM Interop"
-
I am creating and freeing dll
-
I open Excel and add a DLL link.
Everything works fine so far
-
In the sheet, I add the code below
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 to run the code, I get the following error:
"ActiveX component cannot create object"
I tried this using 2 computers: Win7 64 bit, Excel 64 bit 64 bit, VS 2010 and Win7 64 bit, Excel 2013, VS 2010 64 bit with luck. I have read people getting the same error, but nothing seems to work for me.
Does anyone see errors in my method?
source to share
-add namespace for your code.
-make a function like public
.
- make it visible and set "Register COM Interop" as you mentioned.
- Your code should look like this:
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 yourProjName.tlb file in \ bin \ debug or \ bin \ release.
-Open Excel and add a link to your ProjName.tlb not to dll.
-modify your code as:
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'm working on Windows 7 32-bit, Office 2010 SP2 32-bit, Visual Studio 2010 with framework 4.
-configure your project for x64: from Visual Studio -> Go to build menu → click on Configuration Manager → Under Active Solution Platform, click on New and then add it with x64 as platform. Use this framework to compile for x64 link,.
-It's preferable to sign your assembly (very simple): Project Properties> Signing> Sign Assembly> New -> Enter the filename as myKey.snk, no password needed.
-build your project.
- if you are working on the same machine that visual studio is running then you don't need to register your build because VS will do since we have set "Register for COM Interop".
- for other client machines where VS is not running, you have to register your assembly, take only the DLL file, 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 create a * .tlb file for you in the same folder.
-add your dll to the Global Assembly cache by running the following command in cmd (which runs as administrator):
"c:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\gacutil.exe" /i "d:\out\VB_DLL_001.dll"
-from Excel add a link to the generated tlb file: VBA Editor -> Tools -> Links -> Browse -> Select the tlb file -> OK. Then run the previous code, I hope it works successfully.
EDIT # 2
Based on the comment, it works creating DLLs for x64 architecture , so there is no need for other steps in EDIT # 1.
source to share