Invoking .NET Assemblies from VBA – License Protection for Office Application
.NET Assemblies are typically invoked from VBA applications such as Excel or MS-Access by adding a reference to the .NET assembly (the tlb of the assembly) and using early binding to access the .NET objects.
This approach has the drawback that the .NET assemblies need to be registered on the end user system with regasm.
The solution proposed below uses late binding and does not require registration of the .NET assemblies. This solution supports .NET 2.0 and .NET 4.0.
Add the following declarations to your project:
#If VBA7 Then
Private Declare PtrSafe Function GetShortPathName Lib “Kernel32.dll” Alias “GetShortPathNameW” (ByVal LongPath As LongPtr, ByVal ShortPath As LongPtr, ByVal Size As Long) As Long
Private Declare PtrSafe Function SetDllDirectory Lib “Kernel32.dll” Alias “SetDllDirectoryW” (ByVal Path As LongPtr) As Long
Private Declare PtrSafe Sub LoadClr_x64 Lib “QlmCLRHost_x64.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)
Private Declare PtrSafe Sub LoadClr_x86 Lib “QlmCLRHost_x86.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)
Private Declare Function GetShortPathName Lib “Kernel32.dll” Alias “GetShortPathNameW” (ByVal LongPath As Long, ByVal ShortPath As Long, ByVal Size As Long) As Long
Private Declare Function SetDllDirectory Lib “Kernel32.dll” Alias “SetDllDirectoryW” (ByVal Path As Long) As Long
Private Declare Sub LoadClr_x64 Lib “QlmCLRHost_x64.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)
Private Declare Sub LoadClr_x86 Lib “QlmCLRHost_x86.dll” (ByVal clrVersion As String, ByVal verbose As Boolean, ByRef CorRuntimeHost As IUnknown)
#End If ‘ WinAPI Declarations
‘ Class variables
Dim m_myobject As Object
Dim m_homeDir As String
You must initialize the m_homeDir variable to the path where the .NET assemblies are located.
For example, if you install the .NET assemblies in the same folder as the Excel or MS-Access files, you should initialize m_homeDir to:
Excel: m_homeDir = ThisWorkbook.Path
Access: m_homeDir = CurrentProject.Path
.NET Object Creation
Add the following code to your project.
Private Function GetMyObject(dllPath As String, dllClass As String) As Object
Dim LongPath As String
Dim ShortPath As String
LongPath = “\\?\” & m_homeDir
ShortPath = String$(260, vbNull)
PathLength = GetShortPathName(StrPtr(LongPath), StrPtr(ShortPath), 260)
ShortPath = Mid$(ShortPath, 5, CLng(PathLength – 4))
Dim clr As mscoree.CorRuntimeHost
If Is64BitApp() Then
Call LoadClr_x64(“v4.0”, False, clr)
Call LoadClr_x86(“v4.0”, False, clr)
Dim domain As mscorlib.AppDomain
Dim myInstanceOfDotNetClass As Object
Dim handle As mscorlib.ObjectHandle
Set handle = domain.CreateInstanceFrom(dllPath, dllClass)
Dim clrObject As Object
Set GetMyObject = handle.Unwrap
Private Function Is64BitApp() As Boolean
#If Win64 Then
Is64BitApp = True
Instantiate the .NET object
Now you are ready to instantiate your .NET object and start using it. Add the following code to your application:
m_homeDir = ThisWorkbook.Path
m_myobject = GetMyObject(m_homeDir & “\yourdotnet.dll”, “namespace.class”)
The first argument is the full path to the .NET DLL.
The second argument is the fully qualified name of the requested type, including the namespace but not the assembly, as returned by the Type.FullName property.
The solution requires deployment of 2 DLLs that are responsible for hosting the .NET CLR. The DLLs are expected to be deployed in the same folder as your Excel or MS-Access file.
The DLLs can be downloaded from Soraco’s web site: https://soraco.co/products/qlm/QLMCLRHost.zip
We hereby grant you the right to use our DLLs as long as your application does not compete directly or indirectly with Quick License Manager. You can use these DLLs in your commercial or non-commercial applications.