CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Calling a dll in VBA

    Welcome to the Forums!

    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!

  3. #3
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Calling a dll in VBA

    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.

  4. #4
    Join Date
    Feb 2010
    Posts
    6

    Re: Calling a dll in VBA

    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.

  5. #5
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Calling a dll in VBA

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

  6. #6
    Join Date
    Feb 2010
    Posts
    6

    Re: Calling a dll in VBA

    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 ?

  7. #7
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Calling a dll in VBA

    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?

  8. #8
    Join Date
    Feb 2010
    Posts
    6

    Re: Calling a dll in VBA

    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 ?

  9. #9
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Calling a dll in VBA

    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.

  10. #10
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Calling a dll in VBA

    Pretty sure it's Application.Path in VBA
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  11. #11
    Join Date
    Feb 2010
    Posts
    6

    Re: Calling a dll 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

  12. #12
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Calling a dll in VBA

    Turns out that there are a few options:

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #13
    Join Date
    Feb 2010
    Posts
    6

    Re: Calling a dll in VBA

    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.

  14. #14
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Calling a dll in VBA

    Create a macro that self-registers the .dll
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured