Is it possible to specify a location in a user's home directory for a shared library in VBA (in Office for Mac)?

Question

I'm currently using the VBA code similar to the following to specify a location of a shared library for use in communicating (passing a string) from an Office application to a desktop application. The VBA code/macros will need to exist in an add-in (.ppa).

Private Declare Sub sharedLibPassString CDecl Lib "/Users/myUserName/Library/Application Support/myCompanyName/MySharedLib.dylib" Alias "PassString" (ByVal aString As String)

In code from a VBA Macro, I then can do the following.

Call sharedLibPassString(myString)

I've got the communication working, but I'd like to replace the /Users/myUserName/ part with the current user's home directory. Normally on a Mac, you'd specify ~/Library/Application Support/..., but the ~/ syntax doesn't work, producing a "File not found" runtime error.

I discovered that using the following Environment Variable method gets me the ~/ location that I need:

Environ("HOME")

However, I don't see a way to make this part of the CDecl Lib statement, since, as far as I can tell, Environ is evaluated at runtime.

Is there any way to specify a location of a shared library in the user's home directory (~/) in VBA?

---

Here are a few notes about my environment/approach:

• I'm using a Mac, though I believe if there is a solution it would be similar on a PC.
• I don't believe it shouldn't matter, but the Office application I'm using is PowerPoint (2011).
• The reason I'm trying to access an area inside of the Application Support directory, instead of the default location for shared libraries is because I'd like the Desktop application to place the shared library in a location without an installer, and without requiring a user's or administrator's privileges. If there is a better solution or location to accomplish the same task, this would be very helpful as well.

Answer 1

Sorry for giving a long response, I just wanted to make sure I explained this fairly well.

From this page (Anatomy of a Declare Statement) we read that

The Lib keyword specifies which DLL contains the function. Note that the name of the DLL is contained in a string within the Declare statement. (emphasis added)

From experimentation, the VBE scolds me if I try to give anything but a string constant.

The only work around that I'm aware of requires rewriting the string constant at runtime.

Here is an example of how this could be done: Let's say your delaration statement is in Module1 in your current project, and that you deliberately wrote the declaration in this format at the top of your module:

Private Declare Sub sharedLibPassString CDecl Lib _
"/Users/myUserName/Library/Application Support/myCompanyName/MySharedLib.dylib" _
Alias "PassString" (ByVal aString As String)

You can access that module via code through this (requires permissions to VBA in trust Center listed under Developer Macro Settings):

Dim myModule
set myModule = ActivePresentation.VBProject.VBComponents("Module1").CodeModule

Once you've gained the CodeModule, you can replace the 2nd line directly:

myModule.ReplaceLine 2, Environ("HOME") & " _"

Mission accomplished!

If you do this, you will need to update the path prior to attempting to call your declared sub. There must be a break in execution that allows VBA to recognize the change. Also, you will not be able to modify the code while in break mode.

Now if I were doing this, I'd want to make sure I replace the right line, to not break code. You can check the contents of the 2nd line by calling this myModule.Lines(2,1) which will return the string value of the line.

However, here is a more robust solution that will find the correct line and then replace it (assumes myModule has already been defined as listed above):

Dim SL As Long, EL As Long, SC As Long, EC As Long
Dim Found As Boolean
SL = 1     ' Start on line 1
SC = 1     ' Start on Column 1
EL = 99999 ' Search until the 99999th line
EC = 999   ' Search until the 999th column
With myModule
    'If found, the correct line will be be placed in the variable SL
    'Broke search string into two pieces so that I won't accidentally find it.
    Found = .Find("/Users/myUserName/Library/Application Support/myCompanyName/" & _
             "MySharedLib.dylib", SL, SC, EL, EC, True, False, False)
    If Found = True Then
        'Replace the line with the line you want, second paramater should be a string of the value.
        .ReplaceLine SL, Environ("HOME") & " _"
    End If
End With

Answer 2

I don't have a Mac, so this is an incomplete answer and I don't know if it will help, but it's a couple of ideas.

I know on Windows, VB doesn't load an external library until you first try to call a function declared with it, and if you specify only the filename in the declare statement, it will use the system path to look for it. Once I did the same thing you are doing, loading a library from a dynamic path, by specifying only a filename, then making a system API call to set the current working directory to the directory of the library before loading it. Changing the PATH environment variable would probably also work.

Second idea: you could hard-code the path to a filename in the /tmp directory; then automatically copy the desired library to that location before loading it. Watch out for the file being in use by another process, but that's only an error if it is a different version of the file to the one that you want.