How VBA IDE Addins Get Loaded From the Windows Registry

twinBASIC makes it easy to create custom addins for the VBA IDE:

If you follow the step-by-step instructions in the above article, you will have a proof-of-concept VBA IDE addin up and running in under fifteen minutes on your computer.  In fact, the article actually adds a few unnecessary steps so that you can see how to customize the built-in sample.  If you simply want to run the sample as is, it's a three- or four-step process:

1. Open twinBASIC
2. Create a new project using Sample 4: MyVBEAddin
3. If necessary, change the bitness from win32 to win64 to match VBA
4. Go to File > Build

And that's it!

The sample project includes all the boilerplate code needed to create a VBIDE addin, namely:

• A basic implementation of the IDTExtensibility2 interface
• A basic implementation of the DllRegisterServer function
• A basic implementation of the DllUnregisterServer function

NOTE: Throughout this article the acronyms VBA IDE, VBIDE, and VBE are used interchangeably as shorthand for the VBA development environment.

How the VBIDE Decides Which Addins to Load

If we take a peek at the DllRegisterServer function in the twinBASIC sample code, we gain valuable insight into how addins get loaded:

#If Win64 Then
    Const AddinsFolder As String = "Addins64"
#Else
    Const AddinsFolder As String = "Addins"
#End If

'NOTE: VBA.Compilation.CurrentProjectName = "myToolWindowProject"
Const AddinProjectName As String = VBA.Compilation.CurrentProjectName  
Const AddinClassName As String = "myAddIn"
Const AddinQualifiedClassName As String = AddinProjectName & "." & AddinClassName
Const RootRegistryFolder As String = "HKCU\SOFTWARE\Microsoft\VBA\VBE\6.0\" & AddinsFolder & "\" & AddinQualifiedClassName & "\"

Public Function DllRegisterServer() As Boolean
 
    On Error GoTo RegError
    
    Dim wscript As Object = CreateObject("wscript.shell")
    wscript.RegWrite RootRegistryFolder & "FriendlyName", AddinProjectName, "REG_SZ"
    wscript.RegWrite RootRegistryFolder & "Description", AddinProjectName, "REG_SZ"
    wscript.RegWrite RootRegistryFolder & "LoadBehavior", 3, "REG_DWORD"
    
    Return True
    
RegError:
    MsgBox "DllRegisterServer -- An error occured trying to write to the system registry:" & vbCrLf & _
            Err.Description & " (" & Hex(Err.Number) & ")"
    Return False
    
End Function

Let's break down what's happening in the above code.

The key piece of information is the RootRegistryFolder variable.  Its path varies slightly depending on whether you compile the tB project in win32 or win64 mode.

Root Registry Folder: win32

HKCU\SOFTWARE\Microsoft\VBA\VBE\6.0\Addins\myToolWindowProject.myAddIn\

Root Registry Folder: win64

HKCU\SOFTWARE\Microsoft\VBA\VBE\6.0\Addins64\myToolWindowProject.myAddIn\

Within the \myToolWindowProject.myAddIn\ registry subkey (a.k.a., folder), the following values are created:

• Description (REG_SZ): myToolWindowProject
• FriendlyName (REG_SZ): myToolWindowProject
• LoadBehavior (REG_DWORD): 0x00000003 (3)

Note that LoadBehavior is a bit-masked integer.  The value of "3" means that the add-in is loaded ("1") and it loads at startup ("2").  

Here are the bit flags for the LoadBehavior value:

• 0001: Add-in is currently Loaded
• 0010: Add-in should Load at Startup
• 0100: Not used
• 1000: Add-in should Load on Demand
• 0001 0000: Add-in should Load the first time, then Load on Demand

Registering the Add-in

When you build an "ActiveX DLL" (Project: Build Type) in twinBASIC, the IDE calls regsvr32.exe and passes it the full path to the newly-compiled .dll file.

regsvr32.exe reads through the .dll and creates new names and values in the registry based on the contents of the .dll.  Here's a real-world examle:

Here are a few excerpts of the code from the Sample 4 project.  Each excerpt includes a class name along with its associated attributes:

myToolWindow

[FormDesignerId("01E16668-75A8-4A3D-B248-B5EE575761D5")]
[ClassId("D7FAA2BD-5632-44F7-BC9B-11BAD174B745")]
[InterfaceId("61D43A83-0E1A-452F-9290-000B40C00F1D")]
[EventInterfaceId("020AE18D-DA4E-47B5-B92F-88A8407D2D5C")]
Public Class myToolWindow

myAddIn

[ClassId("CD909686-9BB6-4901-B111-71AFCD9848DC")]
[InterfaceId("E2D75A09-4078-4F8A-BF2B-B3C019C3BB99")]
[EventInterfaceId("6880BB87-CF2F-480C-8537-BC35980FCFCF")]
Class myAddIn

Project: GUID

{1781A688-D7A1-40F7-91D6-4237E58C3C9E}

Under the Hood

If you look closely and compare the ProcMon screenshot with the sample code excerpts, you can follow which GUIDs get added to which parts of the Windows Registry:

• Project GUID: HKCU\Software\Classes\TypeLib\{GUID}\1.0\
• myAddIn ClassId: HKCU\Software\Classes\WOW6432Node\CLSID\{GUID}\
• myAddIn ClassId: HKCU\Software\Classes\myToolWindowProject.myAddIn\CLSID\(Default)
• myAddIn InterfaceId: HKCU\Software\Classes\WOW6432Node\Interface\{GUID}\
• myToolWindow ClassId: HKCU\Software\Classes\WOW6432Node\CLSID\{GUID}\
• myToolWindow ClassId: HKCU\Software\Classes\myToolWindowProject.myToolWindow\CLSID\(Default)
• myToolWindow InterfaceId: HKCU\Software\Classes\WOW6432Node\Interface\{GUID}\

Unregistering the Addin

The regsvr32.exe command includes the /u switch, which is used to "unregister" a .dll.

The twinBASIC compiler calls this command behind the scenes when you go to File > Clean in the tB IDE.  

To test the actual effect this command has on the registry, I used File > Clean in the tB IDE using our sample project above.  Before doing that, though, I started a ProcMon session to record any changes made to the registry.

Here are the results of the ProcMon recording:

As you can see, all the values that were created and/or set when registering the .dll got backed out as part of unregistering it.

How the VBIDE Loads Addins

The first time you open the VBIDE, it searches the Windows Registry for addins to load.

Identify Which Addins to Load

It starts by looking in the Root Registry folder as defined above:

HKCU\SOFTWARE\Microsoft\VBA\VBE\6.0\Addins[64]\

For each add-in whose "Load at Startup" bit mask flag is set to 1, the VBIDE goes looking for the accompanying .dll file.  

Find the Addin's ProgID

Before the VBE can find the .dll file, it must first track down the CLSID associated with the addin.  We do this using the ProgID (programmatic identifier) of the addin.  

Here's the subkey for the VBIDE sample addin we created earlier:

HKCU\SOFTWARE\Microsoft\VBA\VBE\6.0\Addins\myToolWindowProject.myAddIn

The LoadBehavior value for the above subkey is 3, so the VBE knows it must load this library at startup.  Right now, though, all it knows from reading the registry values for the above subkey are the Description, FriendlyName, and LoadBehavior of the .dll.  

As it turns out, the name of the final subkey–myToolWindowProject.myAddIn–is the ProgID.  That's the value we need to use to find the addin's associated CLSID.

Find the CLSID

Armed with our ProgID, we can now go find the CLSID.

We look that up in the HKCU\SOFTWARE\Classes\{ProgID}\CLSID\ key:

Find the .dll

Now that we have the CLSID, we can use that to look up the full path to the .dll, which should be in the HKCU\SOFTWARE\Classes\[WOW6432Node]\CLSID\{CLSID}\InProcServer32\:

Why Does Any of This Matter?

Can't I just use regsvr32?  Won't that handle all of this for me?

Yes, but...

Relying solely onregsvr32 is the equivalent of using a strip map to navigate in the military.  It's easy ... as long as everything goes the way it's supposed to.  If there's a problem, a strip map won't help you get back on course.  You better have a full map, or you will find yourself wandering in the wilderness.

When things don't go right with .dll registration, you can quickly find yourself in the proverbial "DLL Hell."  

Hopefully, this article can act as the map to help guide you out of that hell and back to safety.

Related Reading