Return to Question

The source code is deposited into a GitHub repo

VBA class managing loading DLL libraries

I am playing with a custom-built SQLite library intending to access it from VBA via the C-language API. I keep the library within the project folder, so I load it via the LoadLibrary API. To make the load/unload process more robust, I created this class that wraps LoadLibrary/FreeLibrary/SetDllDirectory APIs:

In a regular module, it can be used like this:

Private Sub LoadLibs()
 Dim RelativePath As String
 RelativePath = "Library\SQLiteCforVBA\dll\x32"
 
 Dim DllMan As DllManager
 Set DllMan = DllManager(RelativePath)
 Dim DllNames As Variant
 
 DllNames = Array( _
 "icudt68.dll", _
 "icuuc68.dll", _
 "icuin68.dll", _
 "icuio68.dll", _
 "icutu68.dll", _
 "sqlite3.dll" _
 )
 
 Dim DllNameIndex As Long
 For DllNameIndex = LBound(DllNames) To UBound(DllNames)
 Dim DllName As String
 DllName = DllNames(DllNameIndex)
 DllMan.Load DllName, RelativePath
 Next DllNameIndex
End Sub

Private Sub LoadLibs()
 Dim RelativePath As String
 RelativePath = "Library\SQLiteCforVBA\dll\x32"
 
 Dim DllMan As DllManager
 Set DllMan = DllManager(RelativePath)
 Dim DllNames As Variant
 
 DllNames = Array( _
 "icudt68.dll", _
 "icuuc68.dll", _
 "icuin68.dll", _
 "icuio68.dll", _
 "icutu68.dll", _
 "sqlite3.dll" _
 )
 DllMan.Load DllName, RelativePath
End Sub
or like this:
```vb
Private Sub LoadLibs2()
 Dim RelativePath As String
 RelativePath = "Library\SQLiteCforVBA\dll\x32"
 
 Dim DllMan As DllManager
 Set DllMan = DllManager(RelativePath)
 Dim DllNames As Variant
 
 DllNames = Array( _
 "icudt68.dll", _
 "icuuc68.dll", _
 "icuin68.dll", _
 "icuio68.dll", _
 "icutu68.dll", _
 "sqlite3.dll" _
 )
 
 DllMan.LoadMultiple DllNames
End Sub
Private Sub LoadLibs3()
 Dim RelativePath As String
 RelativePath = "Library\SQLiteCforVBA\dll\x32"
 
 Dim DllMan As DllManager
 Set DllMan = DllManager(RelativePath)
 
 DllMan.LoadMultiple _
 "icudt68.dll", _
 "icuuc68.dll", _
 "icuin68.dll", _
 "icuio68.dll", _
 "icutu68.dll", _
 "sqlite3.dll"
End Sub

I am playing with a custom-built SQLite library intending to access it from VBA via the C-language API. I keep the library within the project folder, so I load it via the LoadLibrary API. To make the load/unload process more robust, I created the DllManager class wrapping the LoadLibrary, FreeLibrary, and SetDllDirectory APIs. DllManager can be used for loading/unloading multiple DLLs. It wraps a Scripting.Dictionary object to hold <DLL name> → <DLL handle> mapping.

DllManager.Create factory takes one optional parameter, indicating the user's DLL location, and passes it to DllManager.Init constructor. Ultimately, the DefaultPath setter (Property Let) handles this parameter. The setter checks if the parameter holds a valid absolute or a relative (w.r.t. ThisWorkbook.Path) path. If this check succeeds, SetDllDirectory API sets the default DLL search path. DllManager.ResetDllSearchPath can be used to reset the DLL search path to its default value.

DllManager.Load loads individual libraries. It takes the target library name and, optionally, path. If the target library has not been loaded, it attempts to resolve the DLL location by checking the provided value and the DefaultPath attribute. If resolution succeeds, the LoadLibrary API is called. DllManager.Free, in turn, unloads the previously loaded library.

DllManager.LoadMultiple loads a list of libraries. It takes a variable list of arguments (ParamArray) and loads them in the order provided. Alternatively, it also accepts a 0-based array of names as the sole argument. This routine has a dependency UnfoldParamArray (see CommonRoutines module and the Guard subpackage), handling the "array argument" case; otherwise, this dependency can be removed. DllManager.FreeMultiple is the counterpart of .LoadMultiple with the same interface. If no arguments are provided, all loaded libraries are unloaded.

Finally, while .Free/.FreeMultiple can be called explicitly, Class_Terminate calls .FreeMultiple and .ResetDllSearchPath automatically before the object is destroyed.

DllManager.cls

The DllManagerDemo example below illustrates how this class can be used and compares the usage patterns between system and user libraries. In this case, WinSQLite3 system library is used as a reference (see GetWinSQLite3VersionNumber). A call to a custom compiled SQLite library placed in the project folder demos the additional code necessary to make such a call (see GetSQLite3VersionNumber). In both cases, sqlite3_libversion_number routine, returning the numeric library version, is declared and called.

'@Folder "DllManager"
Option Explicit
Option Private Module
#If VBA7 Then
'''' System library
Private Declare PtrSafe Function winsqlite3_libversion_number Lib "WinSQLite3" Alias "sqlite3_libversion_number" () As Long
'''' User library
Private Declare PtrSafe Function sqlite3_libversion_number Lib "SQLite3" () As Long
#Else
'''' System library
Private Declare Function winsqlite3_libversion_number Lib "WinSQLite3" Alias "sqlite3_libversion_number" () As Long
'''' User library
Private Declare Function sqlite3_libversion_number Lib "SQLite3" () As Long
#End If
Private Type TDllManagerDemo
 DllMan As DllManager
End Type
Private this As TDllManagerDemo
Private Sub GetWinSQLite3VersionNumber()
 Debug.Print winsqlite3_libversion_number()
End Sub
Private Sub GetSQLite3VersionNumber()
 '''' Absolute or relative to ThisWorkbook.Path
 Dim DllPath As String
 DllPath = "Library\SQLiteCforVBA\dll\x32"
 
 SQLiteLoadMultipleArray DllPath
 Debug.Print sqlite3_libversion_number()
 Set this.DllMan = Nothing
End Sub
Private Sub SQLiteLoadMultipleArray(ByVal DllPath As String)
 Dim DllMan As DllManager
 Set DllMan = DllManager(DllPath)
 Set this.DllMan = DllMan
 Dim DllNames As Variant
 DllNames = Array( _
 "icudt68.dll", _
 "icuuc68.dll", _
 "icuin68.dll", _
 "icuio68.dll", _
 "icutu68.dll", _
 "sqlite3.dll" _
 )
 DllMan.LoadMultiple DllNames
End Sub
' ========================= '
' Additional usage examples '
' ========================= '
Private Sub SQLiteLoadMultipleParamArray()
 Dim RelativePath As String
 RelativePath = "Library\SQLiteCforVBA\dll\x32"
 
 Dim DllMan As DllManager
 Set DllMan = DllManager(RelativePath)
 
 DllMan.LoadMultiple _
 "icudt68.dll", _
 "icuuc68.dll", _
 "icuin68.dll", _
 "icuio68.dll", _
 "icutu68.dll", _
 "sqlite3.dll"
End Sub
Private Sub SQLiteLoad()
 Dim RelativePath As String
 RelativePath = "Library\SQLiteCforVBA\dll\x32"
 
 Dim DllMan As DllManager
 Set DllMan = DllManager(RelativePath)
 Dim DllNames As Variant
 
 DllNames = Array( _
 "icudt68.dll", _
 "icuuc68.dll", _
 "icuin68.dll", _
 "icuio68.dll", _
 "icutu68.dll", _
 "sqlite3.dll" _
 )
 
 Dim DllNameIndex As Long
 For DllNameIndex = LBound(DllNames) To UBound(DllNames)
 Dim DllName As String
 DllName = DllNames(DllNameIndex)
 DllMan.Load DllName, RelativePath
 Next DllNameIndex
End Sub