LoadLibrary does not recognize PATH in VBA code

Question

I have a DLL and I need to call it using LoadLibrary("func1.dll"). The full path of func1.dll is omitted from LoadLibrary because I have to set the PATH variable to the place where func1.dll resides, since func1.dll references another DLL called func2.dll.

So, in order to get this to work I use the following code:

Private Declare Function FreeLibrary Lib "kernel32" (ByVal hLibModule As Long) As Long
Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
Private Declare Function SetEnvironmentVariable Lib "kernel32" Alias "SetEnvironmentVariableA" (ByVal lpName As String, ByVal lpValue As String) As Long

Private Sub t1()
    Dim lb As Long
    Dim dllpath As String

    dllpath = "C:\temp\DllsOffice\DLLsOffice\Debug"
    SetEnvironmentVariable "PATH", dllpath 
    Debug.Print Environ("PATH")

    lb = LoadLibrary("func1.dll")
    MsgBox lb   

    FreeLibrary lb
End Sub

which works perfectly fine for VBA of Offices 2007, 2010, 2013, 2016 even the Office 2019. 32 and 64 bits.

The problem arises when writing the above code in VBA of the Office installed from Microsoft Store:

When running the above VBA code in Office from Microsoft Store the line:

LoadLibrary("func1.dll")

returns 0, meaning that the DLL was not loaded. So, I ran out of ideas in order to get this to work, but with no success til now.

Here are some additional information about the problem and what I've tried to do:

1. Copying the DLL to executable path. For instance, if I placefunc1.dll (and func2.dll) in C:\Program Files (x86)\Microsoft Office\Office14 I can use LoadLibrarywithout the need of using SetEnvironmentVariable "PATH", dllpath. But I cannot copy the DLL to the path of Office from Microsoft Store C:\Program Files\WindowsApps\Microsoft.Office.Desktop.Word_16040.10827.20181.0_x86__8wekyb3d8bbwe\Office16 because it gives access denied;

2. I completely sure that SetEnvironmentVariable "PATH", dllpath is working fine on Office from Microsoft Store. I tested this putting an executable inside the C:\temp\DllsOffice\DLLsOffice\Debug and then calling Shell "test.exe" (without passing the full path) my test.exe program is opened normally.

Do you what I'm missing or have any ideas for me to follow? Thanks you all.

Answer 1

I managed to find a solution using the function AddDllDirectory. According to the Microsoft website, the AddDllDirectory function:

Adds a directory to the process DLL search path.

That means, differently from SetDllDirectory which replaces the DLL search path, AddDllDirectory works like an incremental DLL search path. So, my working code can be something like this:

Private Declare Function FreeLibrary Lib "kernel32" (ByVal hLibModule As Long) As Long
Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
Private Declare Function SetEnvironmentVariable Lib "kernel32" Alias "SetEnvironmentVariableA" (ByVal lpName As String, ByVal lpValue As String) As Long
Private Declare Function AddDllDirectory Lib "kernel32" (ByVal lpLibFileName As String) As Integer

Private Sub t1()
    Dim lb As Long
    Dim dllpath1 As String
    Dim dllpath2 As String
    Dim dllpathN As String

    dllpath1 = "C:\temp\DllsOffice\DLLsOffice\Debug1"
    dllpath2 = "C:\temp\OtherPath"
    dllpathN = "C:\temp\EvenOtherPath"
    AddDllDirectory (StrConv(dllpath1, vbUnicode))
    AddDllDirectory (StrConv(dllpath2, vbUnicode))
    AddDllDirectory (StrConv(dllpathN, vbUnicode))

    lb = LoadLibrary("func1.dll")  ' Success :)
    MsgBox lb   

    FreeLibrary lb
End Sub

OBS: The trick thing with the AddDllDirectory in VBA is that there is not an Ansi nor Unicode version of that function to be used like:

AddDllDirectoryA for Ansi 
AddDllDirectoryW for Unicode

So I needed to use the function StrConv to explict convert the path to the Unicode format.