Class Libirary reference

hansp

New Member
Joined
Aug 2, 2006
Messages
8
Hi Everyone
This may not be possible

I have made a small Class Library (.DLL) with some small calculation routines within my profession (designing ships) witch I am using for various tasks in my daily work.
Is there an way that I can reference and use this routines in Excell-VBA
If I try to add the file to the ref-list I get the following message “A reference can not be added to the specified file” (I have translated the message from Swedish so it may not be the correct English message). The Class Library are made with VB.Net using .NET Framework 2
Thank You all in advance for any replay on this matter, even if it just says that it’s not possible
With regards hanp
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
L

Legacy 98055

Guest
There are several ways to make your net dll visible to pre-net vb. Here is the easiest way.

I'll walk you through the creation of a simple classlibrary project and then you can apply this to your existing project...

MyFunctionLibrary.zip

Open VB.net and create a new classlibrary project named "MyFunctionLibrary".
Screen 0916060550.jpg


Replace your Class1 class code with the following:
Code:
<ComClass( _
    MyFunctions.ClassId, _
    MyFunctions.InterfaceId, _
    MyFunctions.EventsId)> _
 _
Public Class MyFunctions
    Public Const ClassId As String = "028D48E9-F45B-4871-BAEA-5D2A946D63C8"
    Public Const InterfaceId As String = "76E52199-30FE-4772-8149-8677A266442B"
    Public Const EventsId As String = "684C13F4-B14F-4316-88C5-C9E8D910969F"

    Public Function ReturnNumberPlusOne(ByVal Number As Double) As Double
        ReturnNumberPlusOne = Number + 1
    End Function

    Public Function ReturnHello() As String
        ReturnHello = "Hello"
    End Function

End Class
Note the ComClass attribute.

Select Project, MyFunctionLibrary Properties, Configuration Properties, Build; Select "Register for COM Interop".
Screen 0916060555.jpg


Build and save your project.

From VBA, add a reference to "MyFunctionLibrary".
Example code:
<table width="100%" border="1" bgcolor="White" style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> o <font color="#0000A0">As</font> MyFunctionLibrary.MyFunctions
       <font color="#0000A0">Set</font> o = <font color="#0000A0">New</font> MyFunctionLibrary.MyFunctions
       Debug.Print o.ReturnNumberPlusOne(1)
       Debug.Print o.ReturnHello
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("916200661037525").value=document.all("916200661037525").value.replace(/<br \/>\s\s/g,"");document.all("916200661037525").value=document.all("916200661037525").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("916200661037525").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="916200661037525" wrap="virtual">
Sub Example()
Dim o As MyFunctionLibrary.MyFunctions
Set o = New MyFunctionLibrary.MyFunctions
Debug.Print o.ReturnNumberPlusOne(1)
Debug.Print o.ReturnHello
End Sub</textarea>

MyFunctionLibrary.zip

Now simply apply the same principles to your dll.
 

hansp

New Member
Joined
Aug 2, 2006
Messages
8
Tom
Thank you for your time
I downloaded the ZIP-File containing your sample code
When adding the reference there was the same error-message as before when I used the dll-file, but there seems a reference added when using the tlb-file. Is this behavior correct?

When I am executing the Example Sub I get the following message
ActiveX component can't create object or return reference to this object (Error 429)
The folowin three lines in the code
Code:
Public Const ClassId As String = "028D48E9-F45B-4871-BAEA-5D2A946D63C8" 
    Public Const InterfaceId As String = "76E52199-30FE-4772-8149-8677A266442B" 
    Public Const EventsId As String = "684C13F4-B14F-4316-88C5-C9E8D910969F"
The values for the three constants, how do I decide the values or where are they generated?
I have searched the registry for the values, but only the middle one (InterfaceId As String = "76E52199-30FE-4772-8149-8677A266442B") were found
Since I do not have Visual-Studio for private use, I will ask if there is any other way to compile this code i.e vbc.exe

My best regards to all
hansp
 
L

Legacy 98055

Guest
If you do not have VS installed, I think you will need to run RegAsm.exe on your dll to properly register the file and create the tlb. I have always depended on the development environment to complete these various tasks for me and am therefore unfamiliar with the framework's command line tools. Check out RegAsm.exe on the net. The syntax in pretty straightforward. For me, it was:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe "C:\Documents and Settings\Tom\My Documents\Visual Studio Projects\MyFunctionLibrary\bin\MyFunctionLibrary.dll" /tlb

If you are unable to resolve this, just send me your project files and I'll pkg them for you.
 

Forum statistics

Threads
1,136,261
Messages
5,674,705
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top