We help IT Professionals succeed at work.

VB.NET: how to add a dll with windows form to VBA excel

andy7789
andy7789 used Ask the Experts™
on
Hi X-perts,

I need to create a windows form to be shown and accessible from Excel VBA. Sorry for a silly question, but I have just started .NET. Here are my steps:

1) Created a new Class Library project in Visual studio
2) Added a simple form with one button (attached)
3) Saved and built dll

4) tried to add a reference to a new VBA excel project by browsing and pointing to that dll. I am getting a message: "Cannot add a reference to specified file"

Please, correct what i am doing wrong here

thanks
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MsgBox("test")
    End Sub
End Class

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

Are you trying to build a .dll with Visual Studio, and then use it within Excel VBA? Have you registered the .dll build? If not, you need to run this line

regsvr32 c:/path_to_dll/myproject.dll

and when you reference the file in VBA, use the same dll you have registered above.

Does that work?
NY

Author

Commented:
I was under impression that I don't need to register it on a development machine (?) I have found out my mistake: I need to check "Register for COM interop" option on the project property.

After I;ve done this, the reference from VBA works, but only if I link it to .tlb file (not dll). is it as it should be?

After I register with regsrv32, should the link to .dll work fine?
Thanks for the points.

Regarding your last question, I had to google hard for that one, because I wasn't so sure myself... I found thse 2 useful links:

http://msdn.microsoft.com/en-us/library/ms973807.aspx 
http://weblogs.asp.net/psteele/archive/2004/06/16/157767.aspx

The first one goes one about types of components in .NET. If I'm not mistaken, DLL and TLB are basically two ways of exposing methods, but TLB is specifically COM.

Apparently, the TLB file can be generated from the DLL file you build. This you can read about in the second link. When you checked that option box, you basically said to build the TLB after it built the DLL. In my opinion, it should be fine to use the TLB as it apparently doesn't need any more registering. But in any case, my opinion would be that if you registered the DLL with regsvr32, it should also have to work fine.

I would suggest copying the .dll to windows/system32 , and registering it there. Then, point to that file in the references. If you rebuild, you should first unregistered the previous build using
regsvr32 /u c:/windows/system32/myproject.DLL, and copying it back, and registering it again.

That does help you a bit more?
NY