Private Declare Function Problem

[q1w2e3r4t7]

I have created a test com dll in vs2005 that i'm calling from excel vba.

 

When the dll is registered using vs2005, i can call correctly from vba.

however i want to distribute this dll is the same directory as the excel file and be able to use it without having to register the dll on the host computer.

 

i understand this can be done by using the following code:

 

Private Declare Function myFunction Lib "ClassLibrary1.dll" () As Integer

 

however i in my test sub:

Private Declare Function myFunction Lib "ClassLibrary1.dll" () As Integer

Sub test()
   MsgBox myFunction '<-- error here
End Sub

... i get the error 'Specified DLL function not found'. I have searched the net however not been able to find anything to help me.

 

Any assistance would be greatly appreciated.

 

Here is the code i used to create the .net dll

<ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
Public Class ComClass1

#Region "COM GUIDs"
   ' These  GUIDs provide the COM identity for this class 
   ' and its COM interfaces. If you change them, existing 
   ' clients will no longer be able to access the class.
   Public Const ClassId As String = "xxxxxxxx-xxxx-xxxxx-xxxx-xxxxxxxxxxxx"
   Public Const InterfaceId As String = "xxxxxxxx-xxxx-xxxxx-xxxx-xxxxxxxxxxxx"
   Public Const EventsId As String = "xxxxxxxx-xxxx-xxxxx-xxxx-xxxxxxxxxxxx"
#End Region

   ' A creatable COM class must have a Public Sub New() 
   ' with no parameters, otherwise, the class will not be 
   ' registered in the COM registry and cannot be created 
   ' via CreateObject.
   Public Sub New()
       MyBase.New()
   End Sub

   Public Function myfunction() As Integer
       Return 100
   End Function
End Class

 

then in excel vba i use:

Private Declare Function myFunction Lib "ClassLibrary1.dll" () As Integer

Sub test()
   MsgBox myFunction
   myFunction
End Sub

 

I can get this to work by registering the dll and using the following:

Sub Test
Dim com as classlibrary1.comclass1
set com = new classlibrary1.comclass1
msgbox com.myfunction

 

HOWEVER as stated i would like to use this dll without having to register it

[PlausiblyDamp]

The Declare Function syntax is used when calling a traditional windows style function, COM components however require the DLL to be registered / referenced to be used.

[q1w2e3r4t7]

Hmm ... okies.

 

I have code that i have created in vba, however i want to be able to protect it. and we all know that vba password isn't really that safe. So the next best thing i can think of is to put my code into a dll and reference it from excel vba.

Sometimes on the host computer i won't have access (and even if i did, i don't want to) register the dll file. Is there any suggestions that i should look into ?

 

Thanks

Edited November 24, 2008 by q1w2e3r4t7