Set path for dll in vba code

Posted on 2011-10-14
Last Modified: 2012-05-12
I am successfully calling an MFC dll from my vba code in my EXCEL spreadsheet, using this declaration:

Private Declare Function DllImport_MyDll _
                        Lib "MyDll.dll" _
                        Alias "MyDllFunction" _
                        (ByVal str1 As String, _
                        ByVal str2 As String) As Integer

and this call

nReturn = DllImport_MyDll(strSomeString, _

Piece of cake.  This code works correctly.   I believe the above code implies that "MyDll" will reside in the System32 folder.

However, what I'd like to do alter the 2nd parameter slightly.  Instead of passing in "MyDll.dll", I'd like to pass in a string variable that includes both the dll name "MyDll.dll" as well as the path to the dll. We'll call this variable "strPathAndNameOfDll".  So here's what the modified dll import signature would like like:

Private Declare Function DllImport_MyDll _
                        Lib strPathAndNameOfDll _
                        Alias "MyDllFunction" _
                        (ByVal str1 As String, _
                        ByVal str2 As String) As Integer

Note that my new parameter strPathAndNameOfDll is now listed as the Lib.

So, the problem I'm having is that I don't know where to declare my variable strPathAndNameOfDll.  If I declare it immediately above the DllImport statement, I cannot assign a String data type to it.  I can only declare it as a "type-less" constant, like this:

Private Const strPathAndNameOfDll = "C:\PathToMyDll\\EnvelopePlot.dll"

But when I do this, and then put strPathAndNameOfDll as the Lib in the DllImport statement, I get an error message that says: Compile Error: expected string constant.

Other notes in case you're wondering why I'm trying to do this:

a) I do NOT want to simply put my dll in System32.  The path to my dll needs to be determined at run-time, and is user-specific, DEPENDING ON HOW/WHERE A USER CHOOSES TO SET UP THE FOLDER CONTAINING MY APPLICATION AND DLL.  

So, at runtime, I can successfully determine the path to the dll.  However, I'm having trouble figuring out whether to declare the string variable that holds this path, so that I can pass the string variable into the DllImport statement as the Lib.

Any help would be greatly appreciated.

Question by:cjm20
    LVL 142

    Accepted Solution

    you cannot dynamically import from a "path variable".

    note this article that describes where DllImport will look when looking for a dll:

    Author Closing Comment

    Thanks.  I decided to simply put my dll in System32 (or the operating system equivalent) and be done with it.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now