In other words, calling the members of a dynamic-link library (DLL, in a .dll file), from VBA code, without having to first (separately) register that DLL using RegAsm or using a .reg file etc. The VBA code registers the DLL "on-the-fly" as soon as it is needed.
Why do this? Because it makes the life of the users of your VBA code (who are also the users of your DLL) simpler: the DLL does not have to be separately registered on their device (which can be a clunky process and requires administrator access) rather it is all done "under the hood".
If at this stage you're feeling a bit lost, I suggest you read my Calling C# code from VBA (COM interop) post but keep in mind that for dynamically loading a DLL:
You (and the users of your VBA code and DLL) do not need administrator access on the device (but do need access to read and write to the HKCU hive of the Registry)
You do not need to "Register for COM interop" ... the point of this post is to show how to dynamically load a DLL "on-the-fly"
Only late-binding with the DLL is available (no Intellisense while developing your VBA code ... but then you created the DLL so you understand all of its members, right ... !)
What you will need
As well as having basic knowledge of VBA and access to an app that hosts VBA (Excel, Word, PowerPoint, Outlook or Access), this post assumes:
You have a reasonable knowledge of developing with C# (or VB.NET, though you'll have to do the 'translating' to VB.NET-speak from C#-speak yourself)
You have permissions to update Registry keys in HKEY_CURRENT_USER
You are using a Windows device with the .NET Framework installed, preferably version 4.6.2 or greater
You have Visual Studio installed (though you can use other IDEs, this post refers to using Visual Studio … at the time of writing, Visual Studio 2022 version 17.11.4 … if the various paths to menu options don't exist when you're reading this then I apologise, but Microsoft do have a habit of moving everything around every few years)
Creating the DLL
First, you need to select the version of the .NET Framework that you will use (always use the .NET Framework for COM interop not any other variant of .NET). Your decision as to which version to use depends on the devices you are targeting … you need to choose the oldest version that might be installed on any device you want to deploy to. For information, Windows 10 originally came with .NET Framework 4.6 installed (but over time progressed to newer versions) whereas Windows 11 comes with .NET Framework 4.8 or 4.8.1 installed. However, 4.6 (and 4.6.1) are now no longer supported ... the oldest supported 4.X.X version is 4.6.2 and so you SHOULD always be safe using that version.
For up-to-date information on .NET Framework versions and to download the 'developer pack' for a specific version (i.e. so that you can use it in Visual Studio) see https://dotnet.microsoft.com/en-us/download/dotnet-framework.
Fire up Visual Studio and create a new 'Class Library (.NET Framework)' project. Choose a project name (I'm using 'LoadLibTest' for this post … Visual Studio will also use this name as your namespace and so it will become the name of the library) and choose the version of the .NET Framework that you selected, and click 'Create'.
Rename the "Class1" class that Visual Studio will have added to "Utils" and then add /amend the code as follows:
... this code isn't really useful but allows the VBA code that we will create, later in this post, to demonstrate that it successfully calls the C# code in the DLL.
Next, CHANGE THE GUIDs (both of them)! After all, GUID stands for Globally Unique IDentifier ...if you use the same GUIDs as I do then they aren't globally unique! To generate a new GUID, in Visual Studio, Tools > New GUID (or similar) then select the appropriate GUID format (the one with square brackets) then Copy > Exit and replace the GUIDs above the interface and above the class. If your Tools menu does not have a New GUID option then see Appendix 1: Getting the New GUID tool.
Then double-check that:
You have added all of the attributes (in square brackets) for the interface and for the class
The class implements the interface (otherwise your interface is doing nothing)
The interface, class and class members are all public
You have not added a non-public default (i.e. no-parameters) constructor
You changed the GUIDs!
Note that, unlike in the Calling C# code from VBA (COM interop) post, we are not adding 'Description' attributes ... when dynamically loading a DLL then we are necessarily using late binding and so there will be no Intellisense and so the descriptions will not be visible.
Make COM-visible
You have two options for this.
The simple one: in Visual Studio, select Project > LoadLibTest Properties (or whatever you called your project) > Application > Assembly Information … tick ‘Make assembly COM-visible’.
Or the slightly more complex one: add another attribute to the class (and also to the interface if you want to be able to use early binding): [ComVisible(true)].
I recommend the former ... there's nothing, really, to be gained from the latter.
Set the 'Platform target'
Check the 'Platform target' … in Visual Studio, select Project > LoadLibTest Properties (or whatever you called your project) > Build > Platform target.
'Any CPU' should, as it sounds, work for any CPU ie x86 (32-bit Office) and x64 (64-bit Office). If you have problems building or deploying to a device with a specific bitness of Office then select 'x86' or 'x64' as appropriate … but then the .dll will only work on that bitness of Office.
Don't ...
If you are reading along with my Calling C# code from VBA (COM interop) post, in that post we:
Add a 'strong name' ... don't do this ... the registry keys we're using here do not expect a 'strong name'
Register for COM Interop ... don't do this ... it defeats the purpose of this post by registering the DLL when you build the project in Visual Studio ... while doing that is handy on the development device, your end-users cannot do that! ... the point here is to register the DLL "on-the-fly"
Build
You can set the config to Release if you wish (I'm not doing so for this demo) but you may want to do so before deploying your code. But note: the settings for 'COM-visible' and 'Platform target' are per-configuration … not global … so if you subsequently change config you will need to come back and update those settings again.
… then build your project/solution, ensuring that Visual Studio reports a successful build.
Location
Next, you need to decide where to save the DLL and the file with your VBA code (i.e. the macro-enabled Workbook, Document, Database etc) that will use the DLL. I suggest, initially at least, saving them both into the same folder somewhere on your local drive (though the DLL does not have to be in the same folder as the file with your VBA code ... see the comments above the msTHE_FILE constant in the following LibLoader Module for further details).
Wherever you decide, copy the DLL (i.e. the .dll file) from the relevant folder within your Visual Studio project (normally either bin\Release or bin\Debug depending on the Configuration setting in Visual Studio) to the location you decide to use.
Using the DLL from VBA code ... the boilerplate
Create a new VBA Project in your preferred host application ... if following my recommendation, you will save it to the same folder that you just copied the DLL file into.
To the VBA Project, add:
A "UtilsFiles" (standard) Module from my Working with files and folders in VBA post (scroll to the end of the first long block of code to download the entire "UtilsFiles" Module ... ensure you don't download the 'old' version ... the following code requires the 'new' version), and
A "UtilsRegistry" (standard) Module from my Working with the Registry using VBA (the 'wider' Registry: the HKCU, HKCR and HKLM hives) post (again, scroll to the end to download the entire "UtilsRegistry" Module)
... the code you will add next is dependent on both of these.
Note that UtilsFiles includes a reference of a gsAPP_NAME constant. As noted in that post, you can either add a constant with that name (I always have one in every VBA project), replace gsAPP_NAME with a string literal, or you can delete the GetAppFolder() procedure entirely.
Next add a "LibLoader" (standard) Module and add this code to it:
If you try to compile at this point, you may get compilation errors depending on which host application you are using:
If you are using Excel, all should be fine
If you are using Access, you will have 1 or 2 compile errors ... possibly the msoFileDialogFilePicker error and definitely the HOST_APP_NAME error
If you are using any other host application, likely you will have 1 compile error relating to the HOST_APP_NAME
... either way, these are fixed as follows ...
If your VBA Project doesn't have a reference to the 'Microsoft Office NN.N Object Library' (where NN.N is the version number such as 16.0) then, when you compile, it will complain about the
line. To fix, add a reference to that library either using the VBE's own clunky Project References dialog (Tools > References) or using VBE_Extras improved version (see my Adding / updating "Project References" in the VBE post for details).
And before using LibLoader, as per the TODOs in the code, you need to update:
HOST_APP_NAME ... to the name of the host application you are using (if you are not using Excel which is what it is set to initially... hence why you will not get compilation errors if you are using Excel) e.g. "Word", "Access" etc
msTHE_ASSEMBLY_NAME ... to the name of the assembly as defined in your DLL, Project Properties (i.e. in Visual Studio) ... if you were following along with the example C# code then this is already set to the right value for you
msTHE_PROG_ID ... to match the ProgId attribute of the class as defined in your DLL (i.e. in Visual Studio) ... if you were following along with the example C# code then this is already set to the right value for you
msTHE_GUID ... to match the Guid attribute of the class (not the interface!) as defined in your DLL (i.e. in Visual Studio) ... as you will have changed the GUID (as per my notes above) then you will need to change the value to match that in the C# code ... note that, in your VBA code, the curly braces must be included
msTHE_VERSION ... to the version of the assembly as defined in your DLL (i.e. in Visual Studio, see Project Properties then Application > Assembly Information) ... if you were following along with the example C# code and didn't specifically change this then this is already set to the right value for you
msTHE_FILE ... as per the comments in the VBA code, this can be either an absolute path on a local/shared drive, or a filename only if you will put the DLL in the same folder as the VBA Project and if you are using either Excel, Word or Access ... so if you were following along with the example C# code and created a DLL with the same name as I did and if you are using Excel, Word or Access and if you save the DLL in the same folder as your VBA Project then this is already set to the right value for you ... otherwise, you need to use an absolute path and / or the actual name of the DLL
... you can then delete the TODOs if you want (and if you have to use the VBE's Find dialog to locate them: why aren't you using VBE_Extras?!).
LibLoader, obviously, does the hard work here. The GetLib() Function 'gets the library'. Initially, it assumes that the DLL is registered and tries to create an instance of it. If that fails, then the DLL must not already be registered and so (following a few checks) it registers it by way of adding the relevant values to the Registry. It then tries again to create an instance of it which (the theory goes ...) will now work.
LibLoader also 'returns' by way of its 'outeResult' parameter an indication of the result of trying to load the DLL (which calling code should always check ... as in the following example).
If LibLoader fails to return a successful outcome, your code can query the ErrNum(), ErrDesc() and DriveTypeOfLibFile() Properties to gain insight into the problem (see the documentation comments in the code for each of these to understand when they are relevant to be used).
Whether your code ever calls UnregisterLib() is really up to you. As it sounds, it unregisters the DLL. All it does is remove the Registry entries that were made when GetLib() ran the first time. It is entirely optional.
Actually using the DLL from VBA code
Add a new (standard) Module with a Sub and add the following VBA code to that Sub. This code deliberately includes a few Debug.Print statements which you can delete once you have tested it.
This code is fairly straight forward. It attempts to load the DLL. If successful, it calls the two methods we added to the DLL and shows you the results. If unsuccessful, it provides some information as to why.
So, run the code. If everything went to plan, you just dynamically loaded a DLL "on-the-fly"!
If you have problems
First:
Save and close the VBA project and close and re-start the host app (Excel, Word, Access … whichever) and try again
Then try:
Check again that you have correctly followed the instructions in Make COM visible, Set the 'Platform target' and Dont ... (or that you haven't inadvertently changed a setting that you originally had set correctly … easily done) … particularly if you have just changed configuration (e.g. from Debug to Release or vice-versa) as various settings are per-configuration, not global
Check again all of the required attributes are present on the interface and class
Check the interface, class and class members are all public
Check the class implements the interface
Check that you have not added a non-public default constructor
Do not set a 'Description' in the Assembly Information: in Visual Studio, select Project > LoadLibTest Properties (or whatever you called your project) > Application > Assembly Information ... check that 'Description' is blank
Then 'Clean' and 'Rebuild' your project/solution and ensure Visual Studio reports that the build was successful – any errors need to be fixed; if the build 'times out' then ensure all VBA-hosting applications are closed on your device (including checking in the Task Manager for any 'ghost' instances)
Having re-built your project/solution then replace the DLL you were using with the newly built DLL
Next steps
At this stage, you should be itching to write your own C# library class members. Just remember:
Add your new members to the interface including a DispId attribute (which must be a unique number)
Then add the members to the class … make them public!
Static members cannot be seen from your VBA code (COM does not support static members)
You can, of course, add multiple classes (and so multiple interfaces, one for each class) to your C# library … remember to add a new GUID for the interface and for the class and remember to update the ProgId attribute to match the class name
Re-build the C# library in Visual Studio
Copy the DLL to the location identified by the msTHE_FILE constant
Useful info
The 'Appendix 4: Useful info' section of my Calling C# code from VBA (COM interop) post includes sections on:
Types in VBA and C#
Arrays
For Each
Optional parameters
Enums
Exceptions
Appendix 1: Getting the New GUID tool
In Visual Studio, Tools > External Tools > Add
Title: New &GUID
Command: browse to the Tools directory of your current Visual Studio install and look for 'guidgen.exe' … on my device, the Tools directory is at "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\Tools\"
Initial directory: same path as above (ie the Tools directory)
Click OK and now you will have the New GUID tool in your Tools menu
Comments