Loading VBE Addins - A Windows Registry Tour
Let's pull back the curtain and see how VBA loads VBE COM add-ins via a series of registry calls.
MZ-Tools. Rubberduck VBA. vbWatchdog.
What do these three things have in common? Each one is an Office VBE (Visual Basic Editor) Addin. If you're a naturally curious person like me, you've probably wondered at least once how VBA knows which addins to load and where to find the DLL to run the addin. Or, perhaps you find yourself in the seventh level of DLL Hell, and you are trying to troubleshoot your way out of it.
Let's follow along with VBA as it uses the Windows Registry to find, load, and execute VBE addins.
What are VBE Addins?
Let's make sure we're all on the same page here.
VBE addins are tools that you can install that provide extra functionality within the VBA editor. In other words, these are developer tools, not end-user tools. To manage the VBE addins on your device, go to Add-Ins > Add-In Manager... on the VBA menubar.
VBA populates the above list of "Available Add-Ins" by reading the subkeys in the following Windows Registry key:
HKEY_CURRENT_USER\SOFTWARE\Microsoft\VBA\VBE\6.0\Addins\Each subkey (aka, registry subfolder) of the \Addins\ key appears in the list. VBA specifically pulls the "FriendlyName" value from the subkey to populate the entries under the Available Add-Ins heading. The screenshot below is a Windows Registry Editor export of the \Addins\ key from the same machine as the Add-In Manager screenshot above.
Notice that the VBE addins are installed by user profile (HKCU) and not machine-wide (HKLM). The nice advantage to that approach is that they can be installed without requiring adminstrator privileges. Also, addins for 64-bit VBA appear in a registry key named "Addins64" rather than "Addins": ...\VBE\6.0\Addins64\.
Determining Which Addins to Load
VBA reads the contents of the LoadBehavior subkey to determine whether a particular addin should be loaded or not.
The Load Behavior values are listed here, but the most common values you will see in the registry are 0 (unloaded; don't load at startup) and 3 (loaded; load at startup). Of the four add-ins on my user profile, three are loaded and one is not. You can see this behavior reflected in both the Add-Ins dialog and the Registry export:
- EverythingAccess.com vbWatchdog Addin | Startup / Loaded | 00000003
- myToolWindowProject | Startup / Loaded | 00000003
- MZ-Tools 8.0 - VBA | Startup / Loaded | 00000003
- Rubberduck | | 00000000
Finding the Associated DLL
The functionality of Office Add-Ins is contained in compiled DLL files.
In order for the add-ins to do anything useful, VBA needs a way to find and load those DLL files. VBA does this by first grabbing the ProgID (programmatic identifier) from the \Addins\ key as shown here:
VBA then goes to the HKCU\SOFTWARE\Classes registry key and finds a subkey that matches the ProgID from above. It then checks that ProgID's CLSID subkey to find the GUID associated with the addin:
Armed with this GUID (e.g., {9979B091-BCB8-4E1F-B3BB-9699B276F3C0}), VBA then goes to the HKCU\SOFTWARE\Classes\WOW6432Node\CLSID\ key to find a subkey with matching GUID. Note that the \WOW6432Node\ subkey is only used if you are running 32-bit Office on a 64-bit operating system.
Once VBA finds the correct CLSID registry key, it reads the contents of the InProcServer32 (in process server) subkey to find the location of the DLL on the system:
VBA loads the contents of the DLL into the running Office application process (e.g., Excel.exe, MSAccess.exe, etc.). Hence the term, "in-process" (COM) server.
Recap
To review, here are the abbreviated steps to load an Office VBE addin:
- Enumerate the subkeys in
HKCU\SOFTWARE\Microsoft\VBA\VBE\6.0\Addins\ - Read ProgID from end of subkey
- Read GUID from
HKCU\SOFTWARE\Classes\[ProgID]\CLSID - Get DLL path from
HKCU\SOFTWARE\Classes\[WOW6432Node]\CLSID\{[GUID]}\InProcServer32\
Here's an example using the twinBASIC sample VBE ToolWindow project from the above screenshots:
HKCU\SOFTWARE\Microsoft\VBA\VBE\6.0\Addins\myToolWindowProject.myAddIn- ProgID:
myToolWindowProject.myAddin - GUID:
{9979B091-BCB8-4E1F-B3BB-9699B276F3C0} - DLL path:
C:\Users\Mike\Documents\Sample VBIDE Tool Window\Build\myToolWindowProject_win32.dll
Referenced articles
Mike Wolfe
