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.

Declarations

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)

#Else
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

Initialization

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))

Call SetDllDirectory(StrPtr(ShortPath))
Dim clr As mscoree.CorRuntimeHost

If Is64BitApp() Then
Call LoadClr_x64(“v4.0”, False, clr)
Else
Call LoadClr_x86(“v4.0”, False, clr)
End If

Call clr.Start

Dim domain As mscorlib.AppDomain
Call clr.GetDefaultDomain(domain)

Dim myInstanceOfDotNetClass As Object
Dim handle As mscorlib.ObjectHandle

Set handle = domain.CreateInstanceFrom(dllPath, dllClass)

Dim clrObject As Object
Set GetMyObject = handle.Unwrap

Call clr.Stop
End Function

Private Function Is64BitApp() As Boolean

#If Win64 Then
Is64BitApp = True
#End If

End Function

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.

Required DLLs

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 here.

Licensing Terms

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.

To learn more about our software protection and licensing solution, Quick License Manager, visit the QLM product page.