Private Declare Function Problem

[q1w2e3r4t7]

I have created a test com dll in vs2005 that im 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 isnt 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 wont have access (and even if i did, i dont want to) register the dll file. Is there any suggestions that i should look into ?

Thanks