Calling a dll in VBA

Hi All,
I am making a makro for an excel file and having some problem calling the function in a dll file. My dll file is located in the same directory as my excel file. I am using the below statement.

Private Declare Function Des Lib "Des.dll" (ByVal sMsg As String, ByVal sKey As String, ByVal sRet As String, ByVal iAction As Integer) As Integer


I can use the function if I specify the complete path to the dll. However I understand that if I keep the dll file with the excel file then I can use "Des.dll" instead of using a complete path to the file like "C:\New Folder\Des.dll"

Any help would be a lot appreciated.
Thanks,
AQ

Welcome to the Forums! :wave:

At a guess, I'd say that you have to register the Des.dll file on the system first. You could do this by using RegSvr32.exe. Also remember to make sure you copy the Des.dll file in the Windows/System directory.

I hope it helps!

In most cases dlls are not to register.
Putting them in the Windows\System or System32 folder should help, though.
Having the dll in the same folder as the excel sheet will possibly not help, if excel starts with its own folder as default directory.
It could however work, if you add your folder where the dll resides to the PATH environment variable.

Thanks Hannes and WoF for the reply,

I actually got an excel file and the "Des.dll" from my friend. His macro in his Excel sheet runs perfectly fine calling the "Des.dll".
No matter where I place his excel sheet, if the Des.dll is with the file, everything runs perfectly well. In his case, I never registered the "Des.dll". Or to put it in a Windows folder.

So I am unable to understand why the same is not doing any good for me.

Two questions:
What are the differences between the excel sheets?
Are there different versions of excel on the two computers?

Hi WoF
There is only one computer.
The only difference I can think of is that the first file came from a friend of mine and it may be written with a old version of MS Excel.
I am using Office 2007.
But would it create any difference to how the macro should behave ?

I think it is not the behaviour of the macro, but maybe the behaviour of the newer version of excel...
I can't say for sure. How do you load the excel sheet? By double clicking on it or by first starting excel and then loading the sheet?

Just by double clicking on the sheet. Is there something called default path where vb would search for the dll. Can I somehow see that path in my program ?

VB knows the app object.
The property app.path would hold the application path where the exe was started in.
I'm not sure if VBA has an app object, too. Never tried that.
You could try something like Text1.Text=app.path, having put a textbox somewhere.

Pretty sure it's Application.Path in VBA

Thanks for the replies guys,
So I checked on two things ....
ThisWorkbook.path -> Would return the path where my workbook is
Application.path -> Would return me the path where excel.exe is

So I placed the "des.dll" where excel.exe was ...... and bravo ..... it worked. But still I am not sure how do I use this information for my purpose. I need the file "des.dll" beside my workbook so that I can publish my workbook.

Somehow I cannot use any string variables in place of "Des.dll" for below mentioned statement

Private Declare Function Des Lib "Des.dll" () As Integer

Turns out that there are a few options:

http://msdn.microsoft.com/en-us/library/bb687915.aspx

Hi dglienna,
Thanks for the post. Yups I have been through this msdn article. But this post reiterates the same thing. I can register my dll using the declare statement. But I cannot somehow make the relative addressing work, so that everytime my function can read the dll from the directory where the excel sheet co-resides.

Create a macro that self-registers the .dll