How are VBA References handled by COM?

Question

Does anyone know how VBA actually goes from a Reference to finding a COM object to load?

For example, the reference "Microsoft Forms 2.0 Object Library" is associated with GUID {0D452EE1-E08F-101A-852E-02608C4D0BB4} which seems to be a type library. So how does it find the file? I doubt if the full path "C:\Windows\SysWOW64\FM20.DLL" has been hard coded in the addin.

There is a huge tangle of registry keys involved, anyone know how they actually work? (I suspect the answer is no, with the last COM programmers dying off, but there wondrous code lives on!)

There must be a bit of extra magic for the inbuilt objects which automagically change depending on the Office version.

The reason I ask is I have users reporting compilation error bugs when trying to run an Excel VBA addin that I wrote, and I suspect they are missing files in their system. So it would be nice to have some code in my simple installer that had a look.

Answer 1

If you open up regedit and search for a 0D452EE1-E08F-101A-852E-02608C4D0BB4 key, you'll find one under the HKEY_CLASSES_ROOT hive:

The 2.0 key contains a PrimaryInteropAssemblyName string value:

Microsoft.Vbe.Interop.Forms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C

That's for .NET to use though. Look under the win32 key:

The exact location of FM20.DLL!

That's pretty much all there is to it... no magic.

---

A whole VBA project doesn't actually need to compile to run: you can have a completely broken project, and still have one module that works, and you can call and run members of that module. I don't know what your "simpler installer" does, but if you mean to have a way to tell if any of your references are broken, you could use the VBIDE API (needs explicit authorization to run) to iterate the VBPRoject.References and see if any of them IsBroken, in a special dedicated "troubleshooting" module.