Yesterday I got this error when loading my add-in MZ-Tools 8.0 for VBA:
I hate that error. The VBA and VB6 IDEs hide valuable information about why an add-in couldn’t be loaded. In Visual Studio you get at least a COM error number (the error message is almost always <Unknown error> for COM errors):
And you have this article of mine explaining most causes given an error number: HOWTO: Troubleshooting Visual Studio and Office add-ins.
But for VB6/VBA, you are clueless. I have got that error many times because my MZ-Tools add-in is supported in several IDEs using different approaches for registration for the end user (using a setup) and for myself during development/debugging (using PowerShell scripts for registration/unregistration).
For the end user (using a setup):
- MZ-Tools 8.0 for VBA uses per-user COM-registration, not requiring admin rights to install, which is important for many Office users in corporate environments. Per-user COM registration works because Office doesn’t run with admin rights by default (an executable running elevated can not see per-user COM-registration, for security reasons).
- MZ-Tools 8.0 for VB6/VB5 uses machine-wide COM-registration, requiring admin rights to install, because per-user COM-registration wouldn’t work since VB6 runs elevated by default (to be able to register ActiveX Dlls when compiling).
For development/debugging (using PowerShell scripts):
- MZ-Tools 8.0 for VB6/VB5 uses machine-wide COM-registration, because of the reason explained above. That means that I must run Visual Studio always elevated, so it can launch vb6.exe for debugging without annoying prompts for elevation of Visual Studio with a restart.
- MZ-Tools 8.0 for VBA uses also machine-wide COM-registration, because since I run Visual Studio always elevated, Office runs always elevated during debugging, and therefore per-user COM-registration wouldn’t work (remember, a process running elevated cannot see per-user COM-registration).
All this means that any error using the wrong registration or running Visual Studio, VB6 or Office with the wrong elevation (or lack of), and I get the dreaded “MZ-Tools 8.0 could not be loaded”.
In those cases, I need to use Process Monitor (see HOWTO: Using the Process Monitor (ProcMon) tool to diagnose Visual Studio add-ins problems) to try to guess which registry entry or file is causing a problem. In this case, I got this:
So, the dll of the add-in was not found. But, wait a minute: it is looking for it in the path “C:\Users\<user>\AppData\Local\MZTools Software\MZTools8VBA\”, which is used for the end user (setup), not in the path used during development. I went to the Control Panel, Program and Features, and MZ-Tools 8.0 was not installed!. So, somehow the last uninstallation of the setup failed silently, removing the file but leaving the COM-registration in the Windows registry. How could that be? I test the setups and PowerShell scripts exhaustively when created/modified and they were working correctly for weeks since the last changes.
Then, suddenly, I remembered something. I am using Windows 10, and I always use the “Control Panel”, “Program and Features” item to uninstall the software, because I like colors and I am not a fan of the new bland “Settings” > “System” > “Apps and features” user interface of Windows 10. But the day before, I used the “Apps and features” to uninstall MZ-Tools 8.0 for VBA, maybe because I guess that the Control Panel will be removed at some point since it seems a remaining duplicated thing. So, after some manual cleaning of registry entries, I tried again to install and uninstall using the setup (using “Settings” > “System” > “Apps and features”) and then to register for development using the PowerShell script and the problem was reproduced. One try again and I realized the cause:
- If you have a setup that doesn’t require admin rights to install, the “Control Panel”, “Program and Features” doesn’t require admin rights to uninstall either, which is correct.
- However, the “Settings” > “System” > “Apps and features” prompts for admin rights to uninstall even if the setup was installed without admin rights, which seems incorrect to me.
It happens that some users of MZ-Tools 8.0 for VBA run Office with admin rights (I don’t know really why they want that), and therefore if MZ-Tools 8.0 for VBA runs with per-user COM-registration, it doesn’t work. So I changed the setup of MZ-Tools 8.0 for VBA some months ago to behave as follows:
- By default, the setup runs without admin rights. In that case, the setup registers the add-in using per-user COM-registration.
- If you run Office with admin rights, you can force the setup to use machine-wide COM-registration (so it works) running it with admin rights (using the “Run as administrator” context menu). The setup detects that it is running with admin rights and uses machine-wide COM-registration instead of the default per-user COM-registration. Conversely, the uninstaller detects if it is running with admin rights and in that case removes machine-wide COM-registration; otherwise, it removes per-user COM-registration.
And then it happens that on Windows 10, if you use the “Settings” > “System” > “Apps and features” to uninstall, the uninstaller is elevated to use admin rights (even if it was installed without admin rights) and it removes the non-existent machine-wide COM-registration, leaving intact the existing per-user COM-registration that was created when installed without admin rights. When the PowerShell script registers the add-in using machine-wide COM-registration, two COM-registrations are in place. If I run Office with admin rights, the machine-wide COM-registration is used and it works. But if I run Office without admin rights, the per-user COM-registration that shouldn’t be there takes precedence, and points to a file that was uninstalled, so the error.
Now I have to modify the uninstaller to remove also the per-user COM-registration even if running with admin rights.